Your question is actually a pretty common one and one that I and many others have pondered. After years of doing this here is what I have found to be best. Use look up tables only when they will never need to be changed or if the application that you are designing the db for will want/need the ability to modify it (add/delete/update). Here is my theory on it.

1) If you work in IT you already have a workload that requires 110% of your time, why make it harder.
2) If you are 100% sure that the values will never change then it makes sense. (Nothing more frustrating then when you are constantly being asked to go in to a db to change the wording, spelling, you name it and you will inevitably encounter it.)
3) Many times when I am defining a requirement and I know that a look up table we be beneficial I sell the client on the idea that it should be updateable by them and their application. Generally only "Update" and "Insert" functionality, another lessoned learned from clients who have taken the short cut in coding and don't remove the FK's from the joined tables.
4) You will also hear people talk about added space to the table, normalization, ect... Blah on all that! Do you know how many DB's I have looked out where every field is NULLABLE except the primary key or how about a table that is linked to another where they both have many of the same fields... come on I know you have seen them. What kind of design is that?
5) If you take the time to design the db properly and really think it out then you should have an efficient well modeled DB and that as a designer/developer is our ultimate goal.

There is no absolute, 100% for sure way to design a db, it is part brains, part art, and a big part of common sense. Hope this helped...




Originally posted by lvthuan
Hi freinds

In my app, I have some kinds of user called top user, normal user, free user. I design my ERD like this:

Table users(username, password, fullname, kindID,....)
Table kinds(kindID, kindName)

Can I drop table kinds?

Thanks