Lookup tables and table relationships

[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

 

[Allen Browne]

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.

 

[GeorgeMar]

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.




.