Results 1 to 10 of 10

Thread: Address Table

  1. #1
    Join Date
    Feb 2006
    Posts
    1

    Address Table

    I would like to get a couple of opinions with regards to building tables that contain address information. Do you guys/girls feel it is better to have the address [Address, city, state, zip) information in one table with a linking field to the table\data that it goes with? Or should you keep the address information in the table that it goes with? For examle You have a Swimming Pools table and a Resturants table. Both could have Address information. Would you put that data in one overall Address table or in their respective tables? Also, wonder if you had say 5 - 10 tables that needed Address Information? Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    There are pros and cons (of course). An argument for keeping a separate Address table is that you only need to write your "address management" code and data constraints once, and you can be sure they will be consistent between restaurants and swimming pools etc.

    If your DBMS supports user-defined types you could get smart and create an Address type and then give each table a single address column of that type; but in my experience (which is Oracle only) UDTs are a pain in the rear and add fiendish complication to your SQL for little benefit.

  3. #3
    Join Date
    Dec 2002
    Location
    Vancouver, BC
    Posts
    35
    Heh, I just visited the forums right now because I was going to ask the same question!

    I've just recently implemented a schema that has a seperate Address table. I only have 3 different Entities that have Address information. I'm still not sure whether it was the right decision or not. The way I have it work is that if a new Entity gets created that should have an Address, a TRIGGER fires to create a new Address and assign that AddressId to that new tuple I just created. If an Enitity gets deleted I've also created a TRIGGER for the delete operation. So just for my 3 Entities, I've had to write 6 TRIGGERS (albeit they are rather simple) to maintain this relationship.

    The App this was done for is written in C#/ASP.Net so the same abstraction applies. I have an Address class which is a property of a Person, Building, etc...

    I guess what I'm not quite certain about is at what level do we make sure these relationships exist? Should I code Address INSERTs in my BLL? Since I've written those TRIGGERs that part of my BLL doesn't need to be written.

    I'm really interested in hearing any other arguments regarding this structure. Obviously it's not really saving a whole lot of space, since I've implemented a pseudo 1 to 1 relationship. From the performance angle, I can see this as an improvement because now you can fit more Entity tuples on a page which should reduce disk reads. Or it may result in more reads because 2 pages would have to be read for each single Entity.
    INSERT INTO Jokes (statement) VALUES ('Here is my witty SQL signature');

  4. #4
    Join Date
    Feb 2006
    Posts
    7
    Personally, I like to keep my addresses on the table that they are derived from, mainly because I think the single table structure adds a layer of complexity for little, if any, benefit.

    Keep in mind that space is only saved if many of the addresses are duplicated on these tables... if addresses are unique or even close to unique, it will take more even more space to have an address table, due to the size of the index & the length of the arbitrary key fields. I would say that if you can't demonstrate a reason for putting them in one table (as in, entities can have multiple addresses, cross-table redundancy is problematic, etc), then you probably shouldn't.

    Of course, this is just a generalization. Like most data design questions, it really depends on how you plan to use your data. Remember that your DB should be serving your application, not the other way around.

    Hope this helps,
    Leon

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by leoncat
    Remember that your DB should be serving your application, not the other way around.
    Hmm... Perhaps we should say that both the application and the DB should serve the users' requirements. Saying the DB should serve the application is true to a point, but if followed too far leads to monstrosities such as denormalisation and "object-oriented" schemas!

  6. #6
    Join Date
    Jul 2003
    Posts
    74

    An Address should have its own table

    Databases designed for professional organisations should always have a separate table for Addresses, because an Address is basic Entity Type.
    However, it does require that an Address exists in the Address table before it can be referenced in a table such as Customers or Customer_Addresses.
    This can be seen as a tedious hindrance by the individual or small business user.

    Here's a couple of examples of Data Models with separate Address tables :-
    http://www.databaseanswers.org/data_...sses/index.htm

    http://www.databaseanswers.org/data_...fees/index.htm

    And here's one where the Address is stored in the Customers Table :-
    http://www.databaseanswers.org/data_...ents/index.htm

    HTH

    B.Dimple
    Senior DBA

  7. #7
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Separate. There are too many scenarios where that 1-1 can become a 1-many.

    Inevitably, someone will ask for a "mailing address" and a "home address". Or worse, an address change with an effective date! Diabolical, I know. Far easier to make it separate from the start.
    --
    Jonathan Petruk
    DB2 Database Consultant

  8. #8
    Join Date
    Feb 2006
    Posts
    7
    Quote Originally Posted by andrewst
    Hmm... Perhaps we should say that both the application and the DB should serve the users' requirements. Saying the DB should serve the application is true to a point, but if followed too far leads to monstrosities such as denormalisation and "object-oriented" schemas!
    Agreed. Ultimately we are talking about serving the user requirements. But I see it as more of a multi-tiered approach. The user requirements are served by the application... and the application requirements are served by the database.

    I don't think the database should dictate to the application that a customer MUST supply an address, for example. That should be determined by business requirements... and the database needs to model those business requirements, rather than a strict code of 100% normalization.

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Well, perhaps I'm being pedantic here but...
    Quote Originally Posted by leoncat
    I don't think the database should dictate to the application that a customer MUST supply an address, for example. That should be determined by business requirements...
    But if the business requirements are that the customer must supply an address, then the database should dictate that to the application(s)! Why ever not? (Of course the database shouldn't dictate something that goes against the business requirements, that would be a bug!)
    Quote Originally Posted by leoncat
    ... and the database needs to model those business requirements, rather than a strict code of 100% normalization.
    It can and should do both: I have never heard of user requirements being stated as "the business requires that the address table MUST be denormalised." Denormalisation is a design decision, not a business one - and 99.9% of the time a bad design decision, IMHO.

  10. #10
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by andrewst
    It can and should do both: I have never heard of user requirements being stated as "the business requires that the address table MUST be denormalised." Denormalisation is a design decision, not a business one - and 99.9% of the time a bad design decision, IMHO.
    Bingo! (except that I would probably say 90% of the time...)
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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