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

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.

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.

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

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!

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

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.

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.

Well, perhaps I'm being pedantic here but...
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!)
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...)