Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2010
    Posts
    6

    "Address" as own table?

    Hey,

    I have a database that contains users, companies, orders with billing addresses etc. I would like to have a table that just contains addresses and where all these tables can refer to.

    The problem is that sometimes the "recipient" part of the address is the first name + the last name of a table (e.g. user), sometimes it's the company name or it might be something totally different so there has to be a column in the address table which is called "recipient".

    This all creates a lot of overhead. I would be glad for any suggestions (a nice design pattern maybe ) to solve the problem!! Thanks a lot in advance!

    Best,
    Henning

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mynameishenning View Post
    I would be glad for any suggestions (a nice design pattern maybe ) to solve the problem!!
    you can solve this problem easily by ~not~ having an address table

    just leave the address columns in the tables which currently have them

    if you still want a separate address table, then i would put the onus on you to explain why

    and saving space, in this day and age of terabytes for fifty bucks, is no longer a valid reason

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Quote Originally Posted by r937 View Post
    you can solve this problem easily by ~not~ having an address table
    So how would you store multiple addresses per person then?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by shammat View Post
    So how would you store multiple addresses per person then?
    that's an entirely different problem, innit
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2010
    Posts
    6
    i just thought it's better db design - otherwise i would have "street, postal code, city etc." in several tables. Multiple addresses could easily be created by many-to-many relationships or by giving the user table multiple reference id's to the address table. But still - idk if i'm adding unnecessary complexity here...

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mynameishenning View Post
    But still - idk if i'm adding unnecessary complexity here...
    you are, and for what benefit?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2010
    Posts
    6
    I guess you're right...I will just put address fields in multiple tables - thanks a lot for the input!

  8. #8
    Join Date
    Apr 2010
    Location
    London, England
    Posts
    24

    Best Practice says have an Address Table

    Hi

    IMHO it's better to have an Address Table.
    This is a 'normalised design' and also offer other benefits.
    For example, you can use a commercial package, such as QAS or Trillium to validate Addresses if they are in one Table.
    The Data Model on this page of my Database Answers Web Site shows an example oif what this looks in practice :-
    Customer Addresses Data Model

    HTH

    Barry Williams
    Principal Consultant
    Database Answers

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by BarryWilliams View Post
    IMHO it's better to have an Address Table.
    This is a 'normalised design' ...
    but so is having address fields in each entity

    my classic rejoinder for this argument is that you hardly ~ever~ see anyone designing a first names table, even though many entities are called "john" -- and yet developers are so quick to have an address table, when the amount of sharing of the same address is substantially less than people called john

    you'd need a separate address table only if either (1) an entity needs more than one address (see earlier post re addresses per person), and then it's still not enough reason to have a many-to-many relationship instead of just a one-to-many relationship, or (2) there is a strong need to share a single address amongst many entities, or (3) you were interested in the addresses themselves, regardless if there was anyone or any entity at that address

    Quote Originally Posted by BarryWilliams View Post
    ... and also offer other benefits.
    and drawbacks as well -- in your model, you need a three-table join to find customers in a given city

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Having an "Address" table is a huge benefit from an application perspective when you have to do "Address Normalization" to meet the UPU requirements to get reduced international rates. If you have infinite money, then this isn't a constraint but nearly every company that I work with has a finite budget and those budgets tend to be jealously guarded lately.

    Abstracting out addresses from a small database (under 500 Gb) and especially one that only deals with a few countries (many only deal with North America) is probably counter productive. When you start dealing with big problems (dozens or hundreds of countries) and lots of data (Terrabytes), the change in scale forces you to think differently.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •