Mysql database design for customer multiple addresses and default address

Question

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 1

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.

Answer 2

I'll start out my answer with the ole cliche: "There's more than one way to skin a cat." That said, I have a few suggestions:

Point 1 - Assuming a customer can have multiple addresses (i.e. billing and mailing), then yes, you have the right idea in terms of the separate mapping table. As for adding a field to customer_addresses called default or preferred, or something like that, it's not a bad idea, but another option is to add a new field called address_type that would reference a separate table with two records, "Billing" and "Mailing" and/or whatever else you would want. Then, in whatever application you are coding that is going to use the address data, depending on what context i.e. if you're on the billing info page, then code the address type that you use on the page itself something like SELECT * FROM customer_addresses WHERE address_type = 2 /* Billing */.

Point 2 - Same as for customers.

Point 3 - Do you want to be able to display shortened country names? For example, abbreviate "United States" to "US," "Canada" to "CAN," or "United Kingdom" to "UK?" I'd consider adding a field for abbreviated country names for that purpose.