August 17th, 2012, 09:53 AM
How best to store addresses for many other types of data
In my database at the moment I have
an addresses table which has fields for a physical postal address, phone numbers and email addresses
I then have things can can have an address (or part of)
contacts (individual people)
each of those types can have an address. In fact, they can have more than one address; lets take a company with several locations and staff members who may work from elsewhere or an employee who works in different places or has different phone lines or email addresses
the company record relates to the company as a whole and the primary address will the head office; but may also store the addresses of each branch
people within the company may also have an address record (even if it's just a mobile number for a travelling salesman or an email address)
users may also have some address credentials
So I've been going round in circles trying to make sure that at each view level a relevant address is returned.
In an early stage of development I had 3 addresses being created for each new contact (company address if not already in existence or different, personal level details - eg phone, fax, email and one other I can't remember); and that just screwed things up when looking at the user from different levels
I need to get this part of the database designed sorted. All other parts of the db are perfectly fine with either child-parent or direct relationships obvious but because addresses can be applied all over the place I'm struggling to find a way forward
has anyone come across this problem before? Is there any best-practice guidance /documentation for this problem? does this problem have its own name?
I'd rather not go down an EAV route, but will consider it if others think its a good idea
August 17th, 2012, 10:50 AM
to me, an address is ~not~ an entity worthy of recognition by itself
i like to compare it to having a first names table
just because a number of people might have John as their first name is not sufficient reason to create a first names table and link your personnel records to it
first name is an attribute of a person, not an entity in and of itself (unless you run one of these choose-your-baby's-name web sites)
similarly, an address is just an attribute of another entity, like person, company, etc.
a lot of programmers get all ~antsy~ when they see the same columns (address, city, state/prov, postal code) in multiple tables, and immediately start looking around for ways to "improve" the design, typically by creating an address table
but in my opinion, there are no benefits to this so-called improvement, and a huge downside -- complexity -- as you seem to have discovered
Comments on this post
August 17th, 2012, 11:51 AM
Even in systems that are famously exploded (and I'm pointing to PeopleSoft here) there are tables for address but they are still the same type of address. A work address, a home residential address, etc. In the PS world these also have effective dates so you have a history. Why? Because we really do often need to know who worked in the state of Minnesota but lived in the state of Wisconsin in the year 2009 for some tax or legal reason or another. But if you don't have to go there I wouldn't, and certainly you would not want to mix your business location addresses up with residential employee addresses and customer addresses, that's just asking for trouble.
“Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss
August 20th, 2012, 11:45 AM
Thanks guys, will look at some database re-factoring. All good to get this done while in development rather than when complete