normalized?

[enriquemellado]

i understand normalization is something that you get trained with after working for a whilie with databases

i have a member db
and i made a table for the country,state,city,zip

this tale is used by other tables like the member info.
how far does the normalization go?

should i make a table for country, state , city, and zip by themselves? or is that "normalized" enough???
thanks

 

[David Eagar]

I guess a lot depends on how many records your db is likely to end up with. At the end of the day, your table only ends up with two or three records, then it's overkill

 

[boblarson]

i understand normalization is something that you get trained with after working for a whilie with databases

Sadly that statement is terribly quite frequently true, although it would save people a LOT of pain if they learned that well BEFORE doing anything with databases.

how far does the normalization go?

Your questions are not really easily answered without you first getting some information. Read this http://www.utexas.edu/its/windows/database/datamodeling/rm/rm7.html
and then come back with questions about it. I think it will help you get the idea.

 

[doco]

While the theories established and expounded upon by Boyce, Codd, Date and others are important even crucial, it has been my experience a fully normalized database of several hundred tables ormore, is a rare bird indeed; if not non-existent. Typically what happens is several teams over years are involved in the identification, implementation and maintenance of 'core components' and their periphery. How they get established depends very much on time and money only and hopefully, in some form of normalization.
Team A is responsible for some part A of the overall schema while Team B has responsibility for part B, etc. And then of course, team members and team leaders come and go over time, well you get the idea - that is what winds up being 'NORMAL'. Full employment for DBA's

 

[The_Doc_Man]

You normalize things by taking apart stuff that belongs together because of being of the same purpose, and keeping other stuff of different purposes separately. Since I used the word "purpose" above, I guess the best way to answer "what belongs together," the first question is "how will it be used?"

If you ask for address information AS A UNIT then that stuff belongs in close proximity. Same table is probably good for all address data. (With an exception or two.) If you keep records only of current addresses of people and it never happens that two people in your database have the same address (or it happens so rarely as to be hardly noticeable), then maybe you keep address data with the person in the same table. See what I mean about "How will it be used?"

ZIP codes are frequently used separately for geographic location purpose. E.g. at our military reserve site, we compute approximate distance for our personnel to reach a muster point by computing the distance between centroids of their zip-code areas. (Such data sets are available from the US Postal Service on tape or CD for a media and shipping charge.) Therefore, at our shop, we have a special usage for zip-code data. In our personnel tables the ZIP code becomes a foreign key to the zip-supplemental table.

In any case, the degree of normalization required depends on usage, not actual content. Stated another way, if you were going to store something and never use it, why go to the trouble of designing a database at all? For raw storage, just use Excel, close the file, and open it so rarely that you need to dust off the cobwebs when you do.

 

[Moniker]

A few rules of thumb about normalization:

1) You normalize to avoid redundancy in your data. If you've ever filled out an application for a loan, insurance, etc., then you've seen the forms that you fill out "in triplicate". That's what you avoid by using normalization. For example, if you have a structure where your manager's name is associated in three different places, then manager's name needs to become normalized. You don't want "John Smith" as your manager, "John Smith" in the manager table, and "Bob Jones is John Smith's supervisor" in yet another table. With normalization, you would change John Smith once in a "manager's table" and that change would cascade everywhere it's used.

2) You normalize on one to many relationships. For example, in an phone book, you may have someone's home, cell, work, and vacation house numbers. You don't want to write that person's name out four times to match each record. You normalize the name (give it an ID), and associate each phone number with that ID.

3) You don't normalize on things that don't need it. For example, you wouldn't want a "normalized" table of state abbreviations. (AL = 1, AK = 2, AZ = 3, etc.) You're not really saving anything there.

4) If you want specific input, it's a good idea to normalize. For example, when looking for salutations (Mr., Mrs., Miss, etc.), you may want to put the "acceptable" salutations into a table and reference them that way. The reasoning is that people write all of those differently (Ms., Miss, Missus, Mrs., etc.). You can help normalize your data by limiting choices like that. The same thing with addresses. Think of St. Str. Strret, Ave., Avenue, Blvd., Boulevard, etc. You can get tons of variation on that. Normalization can help keep the data clean.

Finally, if you're asking "is that normalized enough?", then you're not really getting normalization in the first place. Make sure you understand at least the first three levels of normalization (usually good enough for Access). The idea isn't to put each data element into its own table and give it an ID. The idea is to make your data maintainable, clean, and as small as possible.

You can see this all over the place. Anytime you search on Amazon, for example, in the URL you'll see there are seemingly ridiculous codes pointing you toward products. Those are normalized lookup tables. (DVDs = 1, Books = 2, Music = 3, etc.) That is the point of it all -- fastest speed, minimal footprint.