RDBMS：儲存數據的正確方法 - 逗號分隔的變數或不同的欄位或表？
我似乎無法找到答案的基本問題。我有一個數據庫，用於儲存收據中的行項目以及使用者名和收據編號。
現在，該
lineItem列只是一長串用逗號分隔的數據（原始文件似乎只是一個 Excel 文件）。此資訊在 PHP 腳本中進行解析，以便在前端查看。該表如下所示：
|----------|----------|----------| |lineItem |receiptID |customerID| |----------|----------|----------| |CD, DVD, |001 |User01 | |----------|----------|----------| |CD, CD, |002 |User02 | |DVD, usb, | | | |----------|----------|----------|最終，這是不好的做法嗎？是否應該將這些
lineItem值連結到另一個表中的相關值？
為什麼將數據儲存為字元串是一個問題：
將多個數據儲存為（在這種情況下以逗號分隔）字元串是不好的做法，因為：
- 第一個原因：
它違反
Codd's second rule（稱為"Guaranteed Access Rule"），其中指出Each and every datum (atomic value) in a relational data base is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name.因此，如果您想引用
user02的 USB ，除了簡單地知道表名、和列名lineItem之外，您還必須進行進一步的處理。PRIMARY KEY來自
here：並且從（RM）Among the conventional database models, the Relational Model of data has a simple, sound mathematical foundation based on the notions of set theory.上的wiki我們有：Relational Model關係模型是第一個用正式數學術語描述的數據庫模型。分層數據庫和網路數據庫在關係數據庫之前就已經存在，但它們的規範相對不規範。關係模型定義後，多次嘗試比較和對比不同的模型，這導致對早期模型的更嚴格描述的出現；儘管分層和網路數據庫的數據操作介面的程序性質限制了形式化的範圍。
因此，基本上，唯一具有良好數學基礎的數據模型是關係模型。
Most relational databases use the SQL data definition and query language; these systems implement what can be regarded as an engineering approximation to the relational model.[同上]。Codd 將他的規則推導出來作為他的關係演算的實際實現的指南——鑑於它是唯一具有良好數學基礎的模型，違反其中任何一個似乎都是一個壞主意。
警告：現在，例如，如果您永遠不想將行項目分解為單獨的組件，那麼將其儲存為一個“單元”是可以接受的，但我可以看到許多您想要拆分的實例到它的組成部分（見下面的第五個原因）。
您可能希望以 .csv 形式儲存數據的一個範例可能是儲存某人的姓名和學術期刊的標題 - 它可能會這樣儲存：
Citizen, Seán B., Prof.這是您列印/處理/傳輸/儲存此資訊的唯一方法
datum，然後它是一個數據，而不是逗號分隔的變數 -或者data是一個非常上下文的概念。
- 第二個原因：
如評論中所述，您的
lineItem表格甚至不是第一範式（請參見此處的圖表-Atomic columns (cells have single value)。這顯然與上述觀點有關。根據一系列所謂的範式來建構關係數據庫的過程，以減少數據冗餘並提高數據完整性。
這些“形式”源自 RM/關係演算和 Codd 的規則，作為確保數據保持一致的一種方式，這顯然在任何數據庫系統中都至關重要 - 簡單來說，這是我們確保給定的最終原型的方式數據儲存在一處且僅一處。
- 第三個原因：
您無法控制輸入該欄位的數據 - 即您無法實施
Declarative Referential Integrity(DRI)。這意味著，例如，沒有什麼可以阻止您引用不存在的產品（例如，DVDx）。DRI 是使用 RM最重要的好處之一 - 這意味著可以保持內部數據的一致性，如果您曾經不幸使用過這樣的系統，您將非常感激這些好處。已經壞了。
在第二點中，我們說範式是為了
ensure that the definitive archetype of a given datum is stored in one place and one place only- DRI 確保對該數據的所有其他引用都指向那個地方而不是其他地方。
- 第四個原因：
SQL 不是為解析字元串而設計的——它可以完成，但它很混亂、耗時且容易出錯。各種 RDBMS 提供商已經開發了許多專有擴展來嘗試克服這個缺陷，但是處理正確規範化的表仍然要容易得多（參見下面的 SQL）。
- 第五個原因：
除了不這樣做的“理論”（或多或少）原因之外，還有一個巨大的實際問題是無法為您的架構下的項目分配單獨的數量和價格 - 假設我正在做聖誕購物，我想要為我的 3 位 U2 狂熱者朋友準備了新的“U2 CD”？除了具有如下欄位值外，無法告訴系統有 3 個 U2 CD：
‘“U2 CD”、“U2 CD”、“U2 CD”、“UB40 CD”、“U2 DVD”、“Kingston USB 32GB”’——注意重複“U2 CD”。
假設您想知道已售出的 USB 數量？每個客戶端的 USB 數量？每個客戶區/地區/國家的數量 - 取決於您的運營規模（參見下面的 SQL）？假設我想知道上週在 USB 驅動器上花了多少錢 -絕對無法獲得任何資訊！名單還在繼續……
如何處理問題：
因此，在處理了您問題的第一部分之後，我們現在可以進入第二部分 -
Should the lineItem values be linked to relational values in another table instead maybe?。
- 第一個解決方案（額外欄位）：
這是與儲存字元串相關的問題的另一個範例。在這種情況下，將欄位添加到給定記錄是解決方案 - 即將字元串拆分為其組成部分，並使每個部分成為欄位！如果一個人有（在這種情況下）郵政編碼、街道名稱和 c 的參考表，則對執行 DRI 和控制數據正確性非常有幫助…
- 第二種解決方案（額外記錄 - 一對多關係）：
在您的問題的這種特殊情況下，我們這裡有一個經典
1-many relationship- 也稱為父子，其中receipt父母line_item是孩子。你的表結構是這樣的：
CREATE TABLE line_item ( lineItem VARCHAR(2000), -- could have a many items - need a very long string - parsing a nightmare! receiptID INTEGER, -- "001" could be a string - MySQL has a zero-fill function customeID VARCHAR(20) -- redundant - don't need to store it for every line_item - it corresponds to a receipt (1 customer/receipt), not a line_item! );您應該擁有的是這樣的（請參閱此處的小提琴-此答案的底部還提供了所有數據和表格）：
CREATE TABLE line_item ( receipt_id INTEGER NOT NULL, item_id INTEGER NOT NULL, item_qty INTEGER NOT NULL, CONSTRAINT line_item_pk PRIMARY KEY (receipt_id, item_id), CONSTRAINT li_item_fk FOREIGN KEY (item_id) REFERENCES item (item_id), CONSTRAINT li_receipt_fk FOREIGN KEY (receipt_id) REFERENCES receipt (receipt_id) );你的數據將（相當神秘）看起來像這樣：
INSERT INTO line_item VALUES (1, 1, 1), (1, 4, 1), (2, 2, 1), (2, 3, 1), (2, 5, 1);
receipt_id欄位和item_id欄位指向PRIMARY KEY它們各自表的 s - 並且表中沒有多餘的、無關的資訊 -customer_id例如沒有儲存多次！這種建模方式允許編寫以下形式的查詢：SELECT c.customer_id, c.customer_name, c.customer_address_1, i.item_desc, i.item_price, r.receipt_id, li.item_id, li.item_qty FROM customer c JOIN receipt r ON c.customer_id = r.customer_id JOIN line_item li ON r.receipt_id = li.receipt_id JOIN item i ON li.item_id = i.item_id;結果：
customer_id customer_name customer_address_1 item_desc item_price receipt_id item_id item_qty 1 Bill Gates Redmond Michael Jackson CD 1.50 1 1 1 1 Bill Gates Redmond U2 DVD 5.00 1 4 1 2 Larry Ellison Redwood Shores U2 CD 2.00 2 2 1 2 Larry Ellison Redwood Shores UB40 CD 4.00 2 3 1 2 Larry Ellison Redwood Shores Kingston USB 32GB 25.00 2 5 1請參閱所有 DDL 和 DML 的小提琴（或以下）！我挑戰你用一個包含你的產品的 .csv 字元串來簡單地做到這一點
line_item——尤其是在 MySQL 中！在 PostgreSQL 中使用類似於array_to_table將字元串輸入數組之後的方法可能是可行的，但我把它留給你作為練習！因此，對於 1-many 關係，您將項目添加到
line_item表中 - .csv 字元串中的每個元素一個項目 - 1 條receipt父記錄可以有1許多（可能非常多）line_item子記錄。現在，該
item表也是該表的父級，line_item並且在這種情況下，可以有0許多子級，例如，如果一個項目根本沒有售出，那麼line_item表中將沒有對它的引用。
- 第三種解決方案（額外的表 - 多對多關係）：
在適當的情況下，“值應該連結到另一個表中的關係值”（正如您在問題中暗示的那樣）並且這是存在
m-to-n關係的時候 - 否則稱為many-to-many關係。考慮一下以前最喜歡的 Databases-101 學生和課程範例以及許多學生參加的許多課程！看這裡的小提琴 - 這次我沒有填充表格。我已經將 PostgreSQL 用於小提琴（我最喜歡的伺服器），但稍作調整就可以讓它在任何合理的 RDBMS 上工作。
創建表課程和學生：
CREATE TABLE course ( course_id SERIAL, -- INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY in MySQL dialect course_year SMALLINT NOT NULL, course_name VARCHAR (100) NOT NULL, CONSTRAINT course_pk PRIMARY KEY (course_id) ); CREATE TABLE student ( student_id SERIAL, student_name VARCHAR (50), CONSTRAINT student_pk PRIMARY KEY (student_id) );這就是
JOINing 表（又linking名表(more formally known as an [關聯實體`]13 - 順便說一句，該頁面上此類表有 17 個不同的名稱）出現的地方。
- 一個給定的學生可以參加許多課程
- 一個給定的課程可以有很多學生
Associative Entity因此，您可以通過創建- 您的JOINing 表來處理這個問題：CREATE TABLE registration ( reg_course_id INTEGER NOT NULL, reg_student_id INTEGER NOT NULL, CONSTRAINT reg_course_fk FOREIGN KEY (reg_course_id) REFERENCES course (course_id), CONSTRAINT reg_student_fk FOREIGN KEY (reg_student_id) REFERENCES student (student_id) );然後我添加一個
PRIMARY KEY- 我將它保留在表定義之外以說明這一點，但它可能（並且通常會）是表創建 DDL 的一部分。ALTER TABLE registration ADD CONSTRAINT registration_pk PRIMARY KEY (reg_course_id, reg_student_id);所以現在，
- 給定的學生只能註冊一次給定的課程，並且
- 一個給定的課程只能讓同一個學生註冊一次
在許多其他情況下，此構造很有用 - 基本上，它是有意義地模擬許多現實生活情況的唯一方法。
以我自己的職業為例：
想想一個
flight包含一個flight_id欄位的表格，一個出發和到達機場列表以及相關時間，然後還有一個crew包含機組人員和一個crew_id欄位的表格（顯然還有其他細節）。事實證明，在ing 表中包含
flight_idandcrew_id欄位JOIN對系統非常有用 - 它確實有助於安排和排班，這與其他系統一團糟 - 兩者都經常發生衝突。辨識哪種模式設計適合哪種場景需要時間和經驗，但是 1-many（現有表中的額外記錄）和 many-many（額外JOIN表）是一個很好的經驗法則！ps 歡迎來到論壇！
_____________ 完整的 DDL 和 DML _______________
Customer table:CREATE TABLE customer -- storing the customer_id on every line item is redundant - check out 3rd normal form ( customer_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, customer_name VARCHAR (100) NOT NULL, customer_address_1 VARCHAR (100) NOT NULL -- can have address_1..n -- -- other fields of particular interest to you -- );
Customer data:INSERT INTO customer (customer_name, customer_address_1) VALUES ('Bill Gates', 'Redmond'), ('Larry Ellison', 'Redwood Shores');
item table:CREATE TABLE item ( item_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, item_code VARCHAR (25) NOT NULL UNIQUE, item_desc VARCHAR (200) NOT NULL, item_price DECIMAL(10, 2), item_supplier INTEGER NOT NULL -- refers to supplier table - not shown! -- -- other fields of interest to you -- );
item data:INSERT INTO item (item_code, item_desc, item_price, item_supplier) VALUES ('code_1', 'Michael Jackson CD', 1.5, 56), ('code_2', 'U2 CD', 2, 78), ('code_3', 'UB40 CD', 4, 67), ('code_4', 'U2 DVD', 5, 78), ('code_5', 'Kingston USB 32GB', 25, 23);
receipt table:CREATE TABLE receipt -- often called "orders" but receipt is OK ( receipt_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, customer_id INTEGER NOT NULL, -- refer to customer table - see below receipt_total DECIMAL(10, 2), -- kept updated by a trigger (not shown) -- can be calculated on the fly or -- possibly a generated field receipt_dt TIMESTAMP NOT NULL, -- date and time of sale receipt_asst INTEGER, -- refers to the sales assistant table - not shown CONSTRAINT rec_cust_fk FOREIGN KEY (customer_id) REFERENCES customer (customer_id) );
receipt data:INSERT INTO receipt (customer_id, receipt_total, receipt_dt, receipt_asst) VALUES (1, 6.5, '2020-06-03 15:23:45.123', 34), (2, 31 , '2020-06-05 10:54:23.123', 17);
line_item table:CREATE TABLE line_item ( receipt_id INTEGER NOT NULL, item_id INTEGER NOT NULL, item_qty INTEGER NOT NULL, CONSTRAINT line_item_pk PRIMARY KEY (receipt_id, item_id), CONSTRAINT li_item_fk FOREIGN KEY (item_id) REFERENCES item (item_id), CONSTRAINT li_receipt_fk FOREIGN KEY (receipt_id) REFERENCES receipt (receipt_id) );
line_item data:INSERT INTO line_item VALUES (1, 1, 1), (1, 4, 1), (2, 2, 1), (2, 3, 1), (2, 5, 1);詢問：
SELECT c.customer_id, c.customer_name, c.customer_address_1, i.item_desc, i.item_price, r.receipt_id, li.item_id, li.item_qty FROM customer c JOIN receipt r ON c.customer_id = r.customer_id JOIN line_item li ON r.receipt_id = li.receipt_id JOIN item i ON li.item_id = i.item_id;結果：
customer_id customer_name customer_address_1 item_desc item_price receipt_id item_id item_qty 1 Bill Gates Redmond Michael Jackson CD 1.50 1 1 1 1 Bill Gates Redmond U2 DVD 5.00 1 4 1 2 Larry Ellison Redwood Shores U2 CD 2.00 2 2 1 2 Larry Ellison Redwood Shores UB40 CD 4.00 2 3 1 2 Larry Ellison Redwood Shores Kingston USB 32GB 25.00 2 5 1