关系数据库设计-规范化和关系

Question

我刚开始设计数据库，我不确定在为我的应用程序设计数据库时是否正确地实现了规范化和关系。

正在设计的应用程序适用于购物列表和产品，并将按以下方式使用数据库：

• 用户通过facebook登录到该网站。
• 有一张大桌子上有数百万的“产品代码”。每个产品代码代表不同的产品(例如。(苹果、电视、手机、牙刷等)。
• 用户可以创建不同的购物列表，每个购物列表都包含一个或多个“产品代码”。

我让“尝试”为数据库创建两种不同的设计，以推断哪一种可能更适合该场景。一种使用一对多关系，另一种使用多对多关系，这两种关系都将shopping_list链接到product。

​

​

问题：

1. 关于一对多:考虑到将有数百万不同的产品标识，assigned_products表中的一对多的设计是否会产生大量的冗余信息？products表的字段类似于： ________________________________________________ |assigned_p_id，shopping_list_id，product_code，product_code，|--------------+------------------+--------------|，1|--------------+------------------+--------------|，aa11aab，aa11aab，2，+

正如您所看到的，aa11aab被重复了两次，因为其他人用相同的项目创建了购物列表，因此我想知道这种冗余是否会产生数据库问题，每个重复的product_code所占用的不必要空间会有多少？

1. 关于多对多:图似乎是通过拥有表shopping_list_products存储id值而不是重复product_code来消除冗余，但是，这会不会在检索信息时造成性能损失？我假设一些加入必须在表之间进行，这样每个单独的用户就可以获得他们所有的购物列表和购物列表中的产品代码。
2. 一般地说：在我的关系/规范化中可能有什么错误吗？(我是这方面的新手)

Answer 1

这里有一些关于从哪里开始的建议。

你需要一张桌子来存储有关购买的信息。在数据中，你可以看到Wile E郊狼购买了一辆迪诺强力士和一架钢琴.

CREATE TABLE IF NOT EXISTS `purchase` (
    `id`           int unsigned      NOT NULL AUTO_INCREMENT,
    `user_id`      int unsigned      NOT NULL,
    `item_id`      int unsigned      NOT NULL,
    `quantity`     double            DEFAULT 1,
    `price`        double            NOT NULL,
    `qwhen`        datetime          NOT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`user_id`) REFERENCES user(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (`product_id`) REFERENCES product(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB Comment='Purchases';

purchase data
+----+---------+------------+----------+--------+---------------------+
| id | user_id | product_id | quantity |  price | created             |
+----+---------+------------+----------+--------+---------------------+
|  1 |       1 |          1 |        1 | 100.00 | 2016-07-02 20:42:13 |
|  1 |       1 |          3 |        1 | 734.23 | 2016-07-02 21:23:40 |
| .. | ....... | .......... | ........ | ...... | ................... |
+----+---------+------------+----------+--------+---------------------+

您还需要一个表来存储有关产品的信息。我假设您正在销售物理产品，所以我添加了ean、upca和其他一些可能有帮助的专栏。

CREATE TABLE IF NOT EXISTS `product` (
    `id`                 int unsigned      NOT NULL AUTO_INCREMENT,
    `manufacturer_id`    int unsigned      NOT NULL,
    `ean`                int unsigned      NOT NULL,
    `upca`               int unsigned      NOT NULL,
    `upce`               int unsigned      NOT NULL,
    `name`               varchar(100)      NOT NULL,
    `height`             double            DEFAULT NULL,
    `width`              double            DEFAULT NULL,
    `depth`              double            DEFAULT NULL,
    `weight`             double            DEFAULT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`manufacturer_id`) REFERENCES manufacturer(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB Comment='Item master file';

product data
+----+-----------------+---------------+--------------+--------+------------+--------+-------+-------+--------+
| id | manufacturer_id | ean           | upca         | upce   | name       | height | width | depth | weight |
+----+-----------------+---------------+--------------+--------+------------+--------+-------+-------+--------+
|  1 |               1 | 1234567890123 | 123456789012 | 123456 | Dyno-might |   12.0 |   1.0 |   1.0 |    0.5 |
|  2 |               1 | 2345678901234 | 234567890123 | 234567 | UltraLaser |   96.0 |  96.0 |  96.0 |  450.0 |
|  3 |               2 | 3456789012345 | 345678901234 | 345678 | Piano      |   36.0 |  60.0 |  72.0 |  375.0 |
| .. | ............... | ............. | ............ | ...... | .......... | ...... | ..... | ..... | ...... |
+----+-----------------+---------------+--------------+--------+------------+--------+-------+-------+--------+

当我们把东西卖给别人的时候，正式的称呼是很好的，所以我们应该有一张有效的敬礼表。user表将具有此表的外键。

CREATE TABLE IF NOT EXISTS `salutation` (
    `id`           varchar(5)     NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB Comment='Salutations';

salutation data
+------+
| id   |
+------+
| Mr.  |
| Mrs. |
| Miss |
| Ms.  |
| Dr.  |
| Rev. |
| .... |
+------+

名称后缀是人名的一部分，因此我们将把它作为user表的一部分，并为该表提供一个外键。

CREATE TABLE IF NOT EXISTS `suffix` (
   `id`           varchar(5)     NOT NULL,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB Comment='Suffixes';

suffix data
+-----+
| id  |
+-----+
| Sr. |
| Jr. |
| II  |
| III |
| IV  |
| ... |
+-----+

当然，我们也有用户--尽管称呼他们为客户可能更合适。

CREATE TABLE IF NOT EXISTS `user` (
    `id`              int unsigned   NOT NULL AUTO_INCREMENT,
    `facebook_id`     int unsigned   NOT NULL,
    `email`           varchar(255)   NOT NULL,
    `salutation_id`   varchar(5)     NOT NULL,
    `first_name`      varchar(50)    NOT NULL,
    `middle_name`     varchar(50)    DEFAULT NULL,
    `last_name`       varchar(50)    NOT NULL,
    `suffix_id`       varchar(5)     DEFAULT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`salutation_id`) REFERENCES salutation(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (`suffix_id`) REFERENCES suffix(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB Comment='Users';

user data
+----+-------------+------------------------+---------------+------------+-------------+-----------+-----------+
| id | facebook_id | email                  | salutation_id | first_name | middle_name | last_name | suffix_id |
+----+-------------+------------------------+---------------+------------+-------------+-----------+-----------+
|  1 |   123456789 | coyote_guy32@gmail.com | Mr.           | Wile       | E           | Coyote    | NULL      |
| .. | ........... | ...................... | ............. | .......... | ........... | ......... | ......... |
+----+-------------+------------------------+---------------+------------+-------------+-----------+-----------+

Answer 2

在这两种情况下，你基本上都在做同样的事情。在第一种情况下，您将shopping_list_id映射到product_code，在第二种情况下，您将它映射到product_id。无论您使用product_code还是product_id，它们似乎都是表示product实体的外键。在这两种情况下，要到达产品行，必须使用该字段对products表进行连接。

通常，您应该映射两个id列(您的第二个选项)，以便在这两种情况下都使用整数主键完成联接。这不仅提高了存储和索引的效率，还使这些连接更快。

另外，正如其中一个注释所指出的，您应该向shopping_list_products表中添加自动增量主键。

另外，对于命名约定，您可能需要考虑始终使用id命名主键。例如，不是有products.product_id，而是对每个表都有products.id等等。