Mysql数据库设计为客户多个地址和默认地址 [英] Mysql database design for customer multiple addresses and default address

问题描述

我正在使用Mysql和INNODB引擎创建电子商务的数据库结构。

Point 1 ：为客户创建多个地址我有这个表

我是用正确的方法做的吗？我应该如何存储默认地址（在哪个表格中）？

点2 ：我有另一个表叫做供应商只需使用supplier_address表将其连接到地址，或者是否有更好的方法？

点3 ：城市和国家？我应该添加东西还是可以吗？也许在另一个表中的一个字段区在两个之间？

解决方案

在我看来，你使这太复杂了。不需要使您的地址模式过度归一化。我看到的处理多个客户地址的系统有一个像你的客户表，然后有一个地址表，如下所示：

  customer_id 
 address_ordinal（每个客户的小数字：0,1,2,3等）。 
 primary（boolean）
 address_1 
 address_2 
位置（城市，村庄等）
省（州等）
邮政编码（zip，邮政编码等） ）
国家
  

customer_id 客户表的外键。主键是（ customer_id ， address_ordinal ）的组合。 primary 列是 true ，如果地址是主要的。

关于您对供应商的问题，您可能需要创建一个名为联系人的公用表，并向客户和供应商提供contact_ids。

如果您的系统包含一个包含（邮政编码，地区，省）行的参考表（或许是从数据供应商购买的东西），您可以使用它来帮助填充您的地址表。但是您应该避免强迫您的地址只包含硬编码的邮政编码：这些参考表格过得非常快。

I am creating the database structure of an ecommerce with Mysql and INNODB engine.

Point 1: To create multiple addresses for the customers i have this tables

Am I doing it in the correct way? And how should I store the default address (in which table)?

Point 2: I have another table called "Suppliers", should i just connect it to addresses with a "supplier_address" table or is there a better way?

Point 3: What about the tables cities and countries? Should i add something or is that ok? Maybe a field "district" in another table beetween the two?

解决方案

In my view you're making this far too complex. There's no need to make your address schema so over-normalized. Most systems I've seen that handle multiple customer addresses have a customer table like yours, and then have an address table, as follows:

 customer_id
 address_ordinal  (small number for each customer: 0,1,2,3 etc).
 primary    (boolean)
 address_1
 address_2
 locality   (city, village, etc)
 province   (state, etc)
 postcode   (zip, postcode etc)
 country

customer_id is a foreign key to the customer table. The primary key is a composite of (customer_id, address_ordinal). The primary column is true if the address is the primary one.

Regarding your question about suppliers, you might want to create a common table called "contacts", and give both your customers and suppliers contact_ids.

If your system contains a reference table (perhaps something you purchase from a data supplier) containing (postcode, locality, province) rows, you can use that to help populate your address table. But you should avoid forcing your addresses to only contain hard-coded postcodes: those reference tables get out of date very fast.

这篇关于Mysql数据库设计为客户多个地址和默认地址的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！