mysql - 每个客户的数据库表或带有链接表的数据库设计？

Question

我只是 (mysql) 数据库设计的新手，非常感谢您对以下问题的反馈。我正在开发一个链接到应该包含客户信息和产品信息的 mysql 数据库的网站。对于一般客户信息(姓名、地址、出生日期等)，我创建了一个表“crm”，对于一般产品信息(可用产品的描述)，我创建了一个表“产品”。

我的问题是如何存储每个客户选择的产品列表？此列表当然会因客户而异，并且还会随时间而变化。此外，“产品”表可能会随时间发生变化(添加新产品或删除现有产品)。首先，我考虑了一个带有“客户产品”表的设计，其中为每个客户选择了产品，但在阅读了本网站相关页面上的一些评论后，我质疑这是否是一个好的设计(因为这可能会导致超过 100K 个表)。另一种解决方案可能是使用如下链接的单表“客户产品”设计:

table 'crm'                    table 'customer products'          table 'products'
--------------------------     -------------------------------    ---------------- 
name | addr | first_prodref    prodref | prodid | next_prodref    prodid | name | cost


- first_prodref in table 'crm' points to (index) prodref in table 'customer products'
- prodid in table 'customer products points to (index) prodid in table 'products'
- next_prodref in table 'customer products' points to the next prodref in table 'customer products'

这个替代解决方案是否可行，或者有更好的建议吗？

感谢您的反馈。

Answer 1

这里有一些东西可以让你开始......

• (P) 表示主键
• (F table.column) 表示一个外键和它应该指向的table.column

我们需要一张表来存储地址:客户账单地址、客户送货地址等。

addresses
    id              unsigned int(P)
    street1         varchar(75) // 123 South Main Street, etc.
    street2         varchar(75) // Apt A, etc.
    city_id         unsigned int(F cities.id)
    zip             varchar(6) // 12345, A1A 1A1, etc. (CA, MX and US)

最好让用户从值列表中进行选择，而不是让他们自己输入值(即城市名称)...

cities
    id              unsigned int(P)
    state_id        unsigned int(F states.id)
    name            varchar(50) // Omaha, Detroit, Tampa, etc.

同样，最好提供可供选择的有效值，而不是让用户输入他们自己的值...参见 ISO 3166-1 .

countries // 
    id              char(2)(P) // CA, MX, US, etc.
    iso3            char(3)(U) // CAN, MEX, USA, etc.
    iso_num         char(3)(U)
    name            varchar(50)(U) // Canada, Mexico, United States, etc.

您肯定会想要向此表中添加更多信息，但这里有一些列可以帮助您入门...请参阅 PHP's crypt() function了解如何散列密码。

customers
    id              unsigned int(P)
    first_name      varchar(50) // John, Mary, etc.
    middle_name     varchar(50) // Quincy, Louise, etc.
    last_name       varchar(50) // Doe, Public, etc.
    email           varchar(255) // me@privacy.com, etc.
    username        varchar(32) // blahblah, etc.
    password        varbinary(255) // hashed
    ...

此表将客户连接到无限数量的地址。

customers_addresses
    id              unsigned int(P)
    customer_id     unsigned int(F customers.id)
    address_id      unsigned int(F addresses.id)

您可能希望向该表中添加更多信息，但这里有一些列可以帮助您入门...

orders
    id                  unsigned int(P)
    created             datetime // 2013-08-28 13:24:53, etc.
    shipped             datetime // 2013-08-28 15:12:10, etc.
    customer_id         unsigned int(F customer.id)
    ship_address_id     unsigned int(F addresses.id)
    bill_address_id     unsigned int(F addresses.id)

我们需要一个表格，将订单号与每个订单中的所有产品相关联。

orders_products
    id              unsigned int(P)
    order_id        unsigned int(F orders.id)
    product_id      unsigned int(F products.id)

您可能希望向该表中添加更多信息，但这里有一些列可以帮助您入门...

products
    id              unsigned int(P)
    name            varchar(50) // Widget A, Widget B, etc.
    height          unsigned int // height in inches, centimeters, whatever.
    width           unsigned int // width in inches, centimeters, whatever.
    depth           unsigned int // depth in inches, centimeters, whatever.
    weight          double // weight in ounces, pounds, grams, kilograms, whatever.

与城市和国家一样，让用户从选择列表中进行选择，而不是输入可能有问题的数据。参见 ISO 3166-2 .

states
    id              unsigned int(P)
    country_id      char(2)(F countries.id)
    code            char(2) // AL, NF, NL, etc.
    name            varchar(50) // Alabama, Newfoundland, Nuevo León, etc.