To Reference Table or Not to Reference Table?

Question

I'd like to invite comments on the best approaches to the following:

Let's say a table has an attribute with a static domain that holds only string values. For example, an attibute called MethodOfContact, to make things simple. MethodOfContact will always be one of the following: Email, Phone, In Person. This will never change so there is no need for the user to maintain a dynamic domain through an interface, for example. The question is this: Should a reference table be used or not? The advantage I see to using a reference table is that I can avoid having to create a lengthy attribute in the original table that holds a descriptive value; in this case, "Email", "Phone", and "In Person". The advantage of not using a reference table that I can think of is that it's one less table in the database. The disadvantage of not using a reference table is that you'll either have too long of a field in the original table to keep the value descriptive, or you'll have a one-character field that saves space but is not very descriptive. I guess my preference would be for the reference table, but I'm not sure if there are other considerations. Please let me know what you think.

Thank you. 

Answer 1

I would go with a reference table.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 2

Reference Table is better design here which is easy to extend to the new values and add more information to the different contact points. Lets say If you want to keep one contact point as default or assign priority over another contact point then Reference table would be very useful. Also one more extra table is not much of a concern to SQL Server if that table is there for a purpose.

 

---

Ranjith | My Blog

Answer 3

I'd use a reference/lookup TABLE. My reasoning: when i come up with question such as this myself, is this list limited by defintion?

For example:

Birthdate: As long as it is a date, we don't care what it is. No lookup. The datatype is perfectly good.

(Employee) Type: Lookup. When used, we care that it is a specific list that is defined by our system.

Active_Flag*: Attribute. It is by definition either on or off. Hence, no lookup is required. Not even a CHECK CONSTRAINT.

(Arithemetic) Sign: It is by definition either +, -, X, /. Use a CHECK CONSTRAINT to limit it.

Applying this reasoning to MethodOfContact: It is not by definition limited to Email, Phone, and In Person. Therefore, it should be a lookup.

>The advantage I see to using a reference table is that I can avoid having to create a lengthy attribute in the original table that holds a descriptive value;

That isn't always an advantage, unless space is a major concern and the (child) TABLE is expected to have hundreds of million of records. It's just 5 bytes or so.

>The advantage of not using a reference table that I can think of is that it's one less table in the database.

A good model has as many TABLEs as required. That is a foundational ascpect in Normalization.

---

*Flags are nearly always bad design. Use a status instead. Unless, by definition--not by business rule, but logical definition--it has only two values. An example would be arithmetic sign, if for some reason it must be kept separate from the number. Bad exmaples are: Active_Flag, Group_Blank_Participant, and IsManager. The first should be status, the second should be another TABLE, the third should be a Manager COLUMN.