one reference table vs. many

[jjohnson]

I am developing a database that could potentially have more than 50 reference tables with approximately 10 – 20 entries in each reference table.

I was considering combining the reference tables into one reference table, however I have never used this approach and have not been able to find any discussion on the pro’s and cons of using a single reference table or any info on “How to” implement such a design.

I would appreciate learning of others experiences in using a single reference table approach and suggestions on how to implement.

Thanks in advance for your help.

 

[Jack Cowley]

Generally speaking you are better of with more tables with few fields than fewer tables with lots of fields. I would go with more tables....

hth,
Jack

 

[FoFa]

We have both types here. In a main ref. table we use a control number to handle the reference returned (like all states have a control number of 12345, and all countries have a control number of 23456). Then you have to specify your control number along with the key to access the proper value. On the other hand having a lot of ref. tables if named correctly is easy to deal with also, but with either method the amount of joins remain the same, just seperate tables vs. self joins. I find not much differance from either method, except it is usually harder to "lookup" the control number of a ref. rather than a named table (bar a bad naming convention). But it is not that big of a draw back.

 

[RichMorrison]

It depends on how you use reference tables;
do you want to return one value or many ?
do you want to join other tables to a reference table for report grouping ?

If you are using a reference table to look up one value, sort of like an old fashioned "INI" file, then a single table with keywords and values is best.

If you have some kind of grouping table - say state codes to state names - then you want a separate table.

HTH,
RichM

 

[Pat Hartman]

I have a standard table with forms and reports that I add to ALL applications. It can be used for all lookup tables that conform to their specs. I've posted a picture of the data entry form. It shows the set of tables contained within the single code table. By doubleclicking on a table name, you can bring up the individual codes for that set.

I then create separate tables if I have any codes that do not fit the standard. I have been using this model since I was creating mainframe applications 20 years ago and I find that it works very well.

Most applications that use separate code tables don't provide user interfaces and nice reports because of their shear volume. With a single code table, you can provide a nice interface which allows the users to maintain the tables if appropriate so you can get out of the "it's time to change the code tables business".