Lookup Table or Lookup Tables? (1 Viewer)

[Uncle Gizmo]

I realised that all my separate lookup tables would go into one table, and I've been doing it like that for years. However I happened upon these comments on stack overflow and I'm not really understanding what the poster is getting at? Am I missing something important? Should I use a separate table for each lookup?


Poster's Comments HERE:-
http://stackoverflow.com/questions/...use-constraints-within-a-table-or-a-new-table

Sounds like what you have are lookup tables. Let me tell you what happens when you decide to put all lookups in one table with an additional column to specify which type it is. First instead of joining to 4 different tables in one query, you have to join to the same table 4 times. There ends up being more contention for the resources in the "one table to rule them all". Further, you lose FK constraints. That means you eventually lose data integrity. So if one lookup is state, nothing will prevent you from putting the id values for a different lookup for customer type in the state id column in the customeraddress table. When the lookups are separate you con enforce that relationship.
Suppose instead of one big table you decide to have a constraint on the column for customer type. Constraints are now enforced but you have a problem when they need to change. Now you have to alter the database in order to add a new type. Again usually this is a very bad idea especially when the table gets large.

 

[The_Doc_Man]

I'm not sure that I fully agree with the poster's contention.

you have to join to the same table 4 times Absolutely true.

There ends up being more contention for the resources in the "one table to rule them all". The risk here is if the query doing this JOIN is a UPDATE, you run the risk of having to open the lookup table for UPDATE through more than one JOIN and I think that is where you would be self-limiting. It would be a case of "Another user has this table open for update" and that other user would be you! However, if the query is purely a SELECT query and is opened appropriate for no updates, this isn't necessarily such an issue.

 

[Simon_MT]

Uncle Gizmo,

You could also try VBA SQL Injection (INSERT, UPDATE) into the table using a SQL Statement for that table alone. I have conbimed Descriptors into one table but essentially this are describing the data and these Foreign Tables don't get updated in Joined SQL Statements, the update to these tables are separated.

Simon

 

[Uncle Gizmo]

For clarity I'm using MS Access 2013. I note one of the posters in that thread mentions"PostgreSQL" --- Somewhere at the back of my mind I am under the impression that with a front end back-end situation you are better off reducing the number of tables. In the case of a lookup-table your data is already available to the front end through the one link. Therefore you're better off having the lookups in One table. I now realise these people are probably talking about SQL servers maybe that's why I'm confused...

 

[The_Doc_Man]

If the individual lookup tables would be small and relatively static, you might be BEST off with them in the front end.

Whatever the situation, reducing the number of tables is the answer only if the question is, "I don't need this table any more so what do I do with it?"