Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
North American or International? –  Neil McGuigan Mar 7 '13 at 3:46
    
Check out Enterprise Model Patterns by David Hay. It has all the answers for ya. –  Neil McGuigan Mar 7 '13 at 3:54
    
What makes the difference international and N.America. Where can I get a your Model pattern –  gowri Mar 7 '13 at 9:49
    
North American addresses are somewhat easier and more structured than others. That book is available on O'Reilly Safari, which has pretty low rates. –  Neil McGuigan Mar 7 '13 at 18:08
    
If you really need truly international coverage (and if you are sure you need to compartmentalize address fields) then you might consider one of the open standards, like OASIS Customer Information Quality v2.0 (oasis-open.org/committees/ciq/download.html). –  Joel Brown Mar 8 '13 at 12:49

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
    
it was hard to understand your answer, but jnk helped me to understand the answer in chat room. –  gowri Mar 12 '13 at 19:17

Build up a generic address table. Using a one to many translation table relate users/business/etc -> translation table -> address table.

This way you can expand your user 'types' and be flexible yet still be very strict and sanitary about the addresses used in relation to those flexible user types.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.