Lookup tables and table relationships

  • Thread starter Thread starter GeorgeMar
  • Start date Start date
G

GeorgeMar

I am wondering if it is more efficient to have a number of
lookup tables or one lookup table for the whole database.

For example, I may have separate lookup tables:
tblAssetType, tblAssetModel and tblAssetManufacturer, and
the relate each of these tables to the one tblAssets table.

On the other hand I could have the tblAssets table and one
tblAssetlookup table with Type, Model and Manufacturer
identified in a third field in the lookup table. When
relating them, I have tblAssets and tblAssetLookup,
tblAssetLookup1 and tblAssetLookup2

The latter scenario reduces the number of lookup tables I
will have.

How will that impact the database and the network traffic?

many thanks for your help
george
 
Hi George

Either design is acceptable. Both can be useful in different circumstances.

My personal preference is to use different tables: it keeps the relational
integrity tight (i.e. you cannot choose a lookup value that does not apply).
Some other developers prefer to keep the table count down as it simplies the
design. I doubt there is any significant difference to network traffic
either way.
 
Thank you Allen

george
-----Original Message-----
Hi George

Either design is acceptable. Both can be useful in different circumstances.

My personal preference is to use different tables: it keeps the relational
integrity tight (i.e. you cannot choose a lookup value that does not apply).
Some other developers prefer to keep the table count down as it simplies the
design. I doubt there is any significant difference to network traffic
either way.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.




.
Click to expand...
 
Back
Top