3

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?

5
  • North American or International? Mar 7, 2013 at 3:46
  • Check out Enterprise Model Patterns by David Hay. It has all the answers for ya. Mar 7, 2013 at 3:54
  • What makes the difference international and N.America. Where can I get a your Model pattern
    – Gowri
    Mar 7, 2013 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. Mar 7, 2013 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, 2013 at 12:49

2 Answers 2

3

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.

1
  • it was hard to understand your answer, but jnk helped me to understand the answer in chat room.
    – Gowri
    Mar 12, 2013 at 19:17
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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