Manuel Manuel - 1 year ago 148
MySQL Question

Mysql database design for customer multiple addresses and default address

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?

Answer Source

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:

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

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download