SQL表结构反馈

Question

我对下面的 sql 表结构不太满意，原因有两个。

1. 我不喜欢在 1020 字节 varchar 变量中列出订单 (orders.itemOrderIds) 中包含的所有商品。我觉得有一种更优雅的方式来保存一堆 ID 号。

2. 我依赖于从项目表中的 itemDescription 变量中提取有关我的 java 代码中的项目（基本上是字段）的信息。

有人对处理这些情况的最佳方法有一些建议吗？我在这个网站上读到，让java代码生成表是可以的。例如，我的 Java 代码将创建一个名为 order_(orderId)_items 的表，该表将保存带有 orderId 的订单中包含的所有项目。这是解决这个问题的最佳方法吗？

CREATE TABLE IF NOT EXISTS customers
(
id INT AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
company VARCHAR(255) NOT NULL,
address1 VARCHAR(255) NOT NULL,
address2 VARCHAR(255),
city VARCHAR(255) NOT NULL,
province_state VARCHAR(255) NOT NULL,
country VARCHAR(255) NOT NULL,
zip VARCHAR(255),
telephone VARCHAR(255),
PRIMARY KEY(id)
) ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS orders
(
id INT AUTO_INCREMENT,
customerId INT NOT NULL,
orderNum VARCHAR(255) NOT NULL,
itemOrderIds VARCHAR(1020) NOT NULL,
regName VARCHAR(255) NOT NULL,
regCompany VARCHAR(255) NOT NULL,
regEmail VARCHAR(255) NOT NULL,
orderTime DATETIME NOT NULL,
FOREIGN KEY(customerId) REFERENCES customers(id),
PRIMARY KEY(id)
) ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS items
(
itemNum VARCHAR(255) UNIQUE,
itemName VARCHAR(255),
itemTypeId SMALLINT NOT NULL,
--itemDescription must hold all information needed to generate license keys in format
-- Field_Name1: Field_Value1, Field_Name2: Field_Value2, .....
-- Field names are platform, version, (choose one: port, voiceName)
itemDescription VARCHAR(1020),
FOREIGN KEY(itemTypeId) REFERENCES item_types(id) ON DELETE CASCADE,
PRIMARY KEY(id)
) ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS item_types
(
id SMALLINT AUTO_INCREMENT,
itemType VARCHAR(255) UNIQUE NOT NULL,
PRIMARY KEY(id)
) ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS item_orders
(
id INT AUTO_INCREMENT,
itemNum VARCHAR(255) NOT NULL,
licenseKey VARCHAR(255) NOT NULL,
FOREIGN KEY(itemNum) REFERENCES items(itemNum),
PRIMARY KEY(id)
) ENGINE = INNODB;

编辑：

CREATE TABLE IF NOT EXISTS Order_Items
(
orderId INT NOT NULL,
itemOrderId INT NOT NULL,
PRIMARY KEY(orderId, itemOrderId)
) ENGINE = INNODB;

Answer 1

是的，您需要一个 OrderItem 交集表（我通常将父对象放在名称的前面），而不是将所有订单 ID 反规范化到 varchar 字段中。没有办法正确索引这样的字段，并且这使得更新非常困难。

阅读数据库规范化，这些是您将找到的第一类示例。

Answer 2

最好的方法就是将所有数据结构至少标准化为 3NF。

数据库规范化

目前 - 更好地将订单项目列表存储在单独的表中，该表还包含对订单表。

Answer 3

您需要一个订单详细信息表（而不是连接表）。为什么？因为订单信息是特定于特定日期的。因此，您必须按该日期的原样存储信息。因此，订单详细信息通常包括商品编号、商品名称、价格（包含价格至关重要，您需要的是订单出售时的价格，而不是 2 年后的价格。）以及选项的任何详细信息，例如如颜色、订购的商品数量等。在订单表中，您还需要包含有关发货地址的详细信息，以及客户的姓名，因为这些事情也会随着时间的推移而变化。

我还会打破客户表。客户通常可以有多个地址、电话号码和电子邮件，每个地址、电话号码和电子邮件都应该是一个单独的相关表。