Mysql – Relational database for address model

addressdatabase-designMySQL

I want to design an "Address" model for all types of entities like users, businesses, etc.

I have two types of main models: one is User and the other is Business. Each one has different address types like below.

User

1.Contact Address
2.Billing Address

Business

1.Contact Address
2.something

So I created an address model with an addresstype column like this

Address

id
addresstype
user
addressline1
addressline2

Relationships:

  • User – One to many –> Business
  • User – One to many –> Address (User Column)

Now using the above relations, addresstype and user columns will be in a relation, but Business address is not relatted with address.

How can I design this one in an efficient way?

Best Answer

Here's what we did in LedgerSMB:

  1. Entity table storing basic container info on individuals and businesses

  2. location table storing address information.

  3. Right now we have a join table between address and entity, but in future versions that will probably be merged with the address table.

  4. Tables 'person' and 'company' which extend entity for that purpose.

That basic structure would work on MySQL too. On PostgreSQL we do some magic with partial unique indexes to make sure that billing addresses are unique per entity, but contact addresses are not. This would not be possible to enforce in MySQL as easily though it could be done with creative design.

Related Question