#1
  1. Mad Scientist
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2007
    Location
    North Yorkshire, UK
    Posts
    3,661
    Rep Power
    4123

    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)
    companies
    users

    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
    I said I didn't like ORM!!! <?php $this->model->update($this->request->resources[0])->set($this->request->getData())->getData('count'); ?>

    PDO vs mysql_* functions: Find a Migration Guide Here

    [ Xeneco - T'interweb Development ] - [ Are you a Help Vampire? ] - [ Read The manual! ] - [ W3 methods - GET, POST, etc ] - [ Web Design Hell ]
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,207
    Rep Power
    4279
    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

    • medialint agrees
    • debasisdas agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. Type Cast Exception
    Devshed Supreme Being (6500+ posts)

    Join Date
    Apr 2004
    Location
    OAKLAND CA | Adam's Point (Fairyland)
    Posts
    14,954
    Rep Power
    8617
    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.
    medialint.com

    “Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss
  6. #4
  7. Mad Scientist
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2007
    Location
    North Yorkshire, UK
    Posts
    3,661
    Rep Power
    4123
    Thanks guys, will look at some database re-factoring. All good to get this done while in development rather than when complete
    I said I didn't like ORM!!! <?php $this->model->update($this->request->resources[0])->set($this->request->getData())->getData('count'); ?>

    PDO vs mysql_* functions: Find a Migration Guide Here

    [ Xeneco - T'interweb Development ] - [ Are you a Help Vampire? ] - [ Read The manual! ] - [ W3 methods - GET, POST, etc ] - [ Web Design Hell ]

IMN logo majestic logo threadwatch logo seochat tools logo