design question

[scristaldi]

When or how does one make the decision to create a table (lookup) instead of just entering the data. Example zip code table or say area code and phone in this day of home/office/cell1/cell2. Should create a separate table for phone?

[Ron Weller]

It can depend on many things; how restrictive you are going to be with your application, how much data will there be, how critical the data is, etc..

For example, if you want each contact to be able to store any and all phone numbers then splitting them into a seperate table is the way to go.

But on the other hand one of the reasons for splitting data on a record to another table is to keep from repeating that data. In this instance a phone number is not likely to be repeated from one contact record to another. Contacts don't usually have the same phone numbers.

Try to look at things from a practical point of view and balance need with importance. Are phone numbers a critical piece of data, or just informational? Does this application require that kind of flexability?, that is, to allow each contact the ability to store any list of phone numbers.

Also look at volume. If you are going to have a huge list of contacts so that most of those phone numbers would be empty fields, and thus taking up a lot of space for empty data, then you would take up less storage as a seperate table where you only have records for actual phone numbers. If there are not very many contact records then having empty phone number fields is not that big of a deal.

[Hack]

Welcome to DevX

When or how does one make the decision to create a table (lookup)

Any time an application requires you to look something up. If you have to enter information each time, then you aren't doing any look ups.

[mdb002]

A zip code table could be useful if you included city and state, then the user could just enter the zip. This would be a lookup table. The phone table would be an example of a data hierarchy (e.g. company->person->phone). Ron Weller made some very good points regarding this. I would add one thing table/record size consideration. If you had 4 phone numbers per person and assuming a text field that would be a minimum of 52 characters (13 per phone number (including country code or with formating)). This would mainly be an issue with an MS Access (or similar DB) on a network with multiple users and a large person table.

[Slope]

The biggest thing for me is whether I will be grouping on that column during reporting or as Ron mentioned, is it just information to be displayed at the detail level. If you need consistency (ie- for grouping), then it is almost never a good idea to give users a free-type column.

Using the zipcode example again, lets say for all zipcodes in Orange County, you might want the city name to always be, "Orange County". If you let users type in their own city name, people who live in South Orange County might enter, "So. Orange County" or "South Orange County' or "S. Orange County" or "S Orange County" or any other number of variations. Also, there will inevitably be typos mucking things up even more. If you try to group by that column you will get a different grouping for every variation, which can make it very difficult to create a meaningful report.

In addition to storage and maintenance considerations, using lookup tables is a way to keep your data clean.

[scristaldi]

Thanks to everyone for the good thoughts.