查看完整版本 : MySQL database 設計

linuxfedora 2020-7-27 09:28 AM

MySQL database 設計

如我想用MySQL儲Data

我應該把同類Data用同一table儲 但可能會有幾十億行
定分1000個table儲, 每個儲小D好?


[b]For example: Each person will have a million of rows.[/b]
[list][*]Design 1: (Only 1 table to store all person)[list][*]PersonTable:[*]personId data[/list]
[/list]


[list][*]Design 2: (Each person have its own table)[list][*]personTable1[*]personTable2....[/list]
[/list]


有乜要留意?
Thanks.

[[i] 本帖最後由 linuxfedora 於 2020-7-27 09:41 AM 編輯 [/i]]

Ob1Kenobi 2020-7-27 10:22 AM

[quote]原帖由 [i]linuxfedora[/i] 於 2020-7-27 09:28 AM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=522329386&ptid=29256233][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]
如我想用MySQL儲Data
我應該把同類Data用同一table儲 但可能會有幾十億行
定分1000個table儲, 每個儲小D好?[/quote]
點樣design,要睇翻你點用啲資料。即係你既bussiness model
[quote]有乜要留意?[/quote]
normalization (not to mention):loveliness:

linuxfedora 2020-7-27 10:34 AM

[quote]原帖由 [i]Ob1Kenobi[/i] 於 2020-7-27 10:22 AM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=522332179&ptid=29256233][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]

點樣design,要睇翻你點用啲資料。即係你既bussiness model

normalization (not to mention):loveliness: [/quote]

I am not sure what you mean the Bussiness model. But the requirements are:
I need to query more than 1 person at the same time.
The number of person will increase not fixed.
The person id will be used in other table.

Sorry, i have no experience on designing database table. Thanks

form5 2020-7-27 11:24 PM

樓主未識分 normalisation 同 partition table

YjgfkHJj 2020-7-27 11:44 PM

chop it so that each table only hold the most basic type of data and no repetetion, say

User
-id int pk
-name string(255)
-address text
-dept_id int
-bank_id int

Department
-id int pk
-dept_name

Bank
-id int pk
-name string
-address text
-interesT_rate float

instead of putting everything in User table. This is called normalization

sswroom 2020-7-28 12:53 AM

MySQL最好不要多過1000 tables, 否則會很慢。每個table最好不要多過100k rows, 否則會很慢。

abcd5678 2020-7-28 03:59 AM

You put a trap on yourself.

The DB won't contain billions of records on day 1.

Your data would affect your design, e.g. one id has 100M record while another only has 1K, or all id is evenly distributed. The design and tuning method is very different.

For your suggested data volume, unless you really know the data, otherwise, all design at this moment is worthless.

kormer 2020-7-28 05:54 PM

畫一畫實體關係圖啦或寫下有乜attributes及關係,及想想之後要查詢什麼東東。有這些後才做db設計。

xianrenb 2020-7-28 08:48 PM

[quote]原帖由 [i]linuxfedora[/i] 於 2020-7-27 09:28 AM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=522329386&ptid=29256233][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]
如我想用MySQL儲Data

我應該把同類Data用同一table儲 但可能會有幾十億行
定分1000個table儲, 每個儲小D好?


For example: Each person will have a million of rows.
Design 1: (Only 1 table to store all person)PersonTable:per ... [/quote]

呢個應該是原則問題。
以我的理解,寫程式來說,同類型的相關 data ,用同一個 class ;
搞 database 來說,同類型的相關 data ,用同一個 table 。
係唔會因為 data 多而分開用多個 class/table 的。

如果達到要考慮效能或 database 的設置限額時,我估自行寫一個 file-based system 來解決會是較佳選擇。

kormer 2020-7-29 08:24 PM

[quote]原帖由 [i]xianrenb[/i] 於 2020-7-28 08:48 PM 發表 [url=https://www.discuss.com.hk/redirect.php?goto=findpost&pid=522415858&ptid=29256233][img]https://www.discuss.com.hk/images/common/back.gif[/img][/url]


呢個應該是原則問題。
以我的理解,寫程式來說,同類型的相關 data ,用同一個 class ;
搞 database 來說,同類型的相關 data ,用同一個 table 。
係唔會因為 data 多而分開用多個 class/table 的。

如果達到要考慮效能或 database 的設置限額時,我估自行寫一個 file-based system 來解決會是較佳選擇。 ... [/quote]

是否自行寫都要考慮重新發明輪子的問題,可能有其他現有工具能滿足要求呢,或可優化都說不定,無論怎樣都是看要求。

我的無比膏 2020-7-30 03:44 PM

換db2 或 Oracle, 用partition係最簡單

煙民母親生賤種 2020-7-31 03:28 AM

[quote]原帖由 [i]linuxfedora[/i] 於 2020-7-27 09:28 AM 發表 [url=https://www.discuss.com.hk/redirect.php?goto=findpost&pid=522329386&ptid=29256233][img]https://www.discuss.com.hk/images/common/back.gif[/img][/url]
如我想用MySQL儲Data

我應該把同類Data用同一table儲 但可能會有幾十億行
定分1000個table儲, 每個儲小D好?


For example: Each person will have a million of rows.
[*]Design 1: (Only 1 table to store all person)[*]PersonTable:[*]per ... [/quote]

row  太多,當然係一個 person  一個 table  啦!  搵都快 no of person x 1 百萬倍 (最佳下) 。

歌都有得你唱, 一首歌一個故事。

[url]https://m.youtube.com/watch?v=723WgZgWAXM[/url]

Qoo記 2020-7-31 11:24 AM

MySQL都有Partitioning?
頁: [1]
查看完整版本: MySQL database 設計