Hello I am new to SQL Server 2005 and am designing my first database.
In the AdventureWorks sample database the city field is included with the street address where it repeats for each row.
Why would you not put it in a separate table with state/province or a separate table all togeather?
Thanks for your help.
Blair:
Basically, because city is a component of an "address entity". Now, you COULD extract the city so that the city was in a separate table from the address table and store the key to the city table as part of the address information. I would try to avoid this.
In many of the applications that I have used one way of locating information associated with a specific person is as part of an "address search." When you perform an "address search" it is much more efficient if all of the data elements for an address are located in the same table. If you have "City" in a table by itself with a key to the "City" table included as part of the "Address" table you would need to join the two tables to included "City" information as part of a filtering criteria as part of a "search by address."
Now, we DO from time to time receive "zipcode files" from the post office. These we DO store on a separate table; however, this particular collection of data serves a completely different function.
You might have a valid reason to have a separate "City" table.
Frequently, we do have a separate "State Code" table.
|||I can't see any reason to put it in a seperate "table," but a seperate field would be sufficient. It really all depends on how you use the table.
It would be my venture to guess that your company is only concerned with querying records on a state level.
Just my twist on it,
Adamus
|||In trying to 'normalize' a database, you should continully 'deconstruct' the various parts until the effort to re-construct is more costly than the space savings of deconstruction.
For example, if an address database was 'really' normalized, then there would be a table of street names, a table of street types (Ave, St, Rd, etc.), perhaps a table of directions (N, NNE, NE, SNE, E, etc.) , a purist could even make an extreme case for a table of address numbers. Then a table of Cities, Counties, States, Countries. Imagine a table of address types (Apt, Suite, Mail Stop, etc.)
The original address table could easily be deconstructed until it was nothing more than a table full of FK-PK links. Extreme storage space savings, yet excessive computation resources required everytime you need an address.
A good rule of thumb: Normalize until it 'hurts' and then back up a level until it 'works'.
Third Normal Form is NOT always your friend in this day of very inexpensive storage.
|||Thank You for all your help!
This is exactly what I wanted to know.
