LookUp tables for dynamic set of attributes: Set a pointer or use a generic LookUp?

[Banana]

I'm wrestling with the issues; in other threads, it became apparent that because I could not know ahead of time what I will need to know about a given entity, I will use a table to enumerate attributes that is applicable for a given entity.

However, the stumper is that what if an attribute should conform to a set list of values? Since they are dynamic, I would have problem predicting what I will need to be able to lookup, and am even don't know whether I will need a one-many lookup or many-many lookup.

I thought that generic lookup table with a table listing "classes" of lookup would allow me to have one big generic lookup table while using "classes" to act like virtual tables so I can then set the query to appropriate "class" to return just right set of values.

But as I thought about it, I ran into some issues which is pulling me toward the crazy idea that I should have freestanding tables, and use a field in tblAttribute to give me the table's name so I'd know which free-standing table it points to, and have the necessary key to lookup the values within that table.

Even though my gut instincts tell me that I shouldn't be going against the conventions of database design (who the frick goes around creating free-standing lookups?!?), I'm simply not sure how I can use a generic lookup table to hold all information.

For example, suppose I was given a list of values that has its own categories. Since the former design allows only for two level (lookup and lookupclass), where am I to insert that extra level?

Furthermore, I found myself needing a set of virtual keys to reference a certain "class" of lookups for report purposes. That means I need an extra field in my lookup table than I originally anticipates. What if I find myself needing one more field that just won't fit the generic lookup table?

So does anyone have suggestions on how we would create a placeholder for a lookup table that will be made just in time?

 

[Banana]

After thinking about it:

I realized that it doesn't necessarily violate normalization to relate tblAttributes to multiple LookUp tables. Besides, I can just add an field in tblAttribute to store the lookup tables' name so I know which table this particular attributes used to lookup for the values.

Furthermore, by creating a lookup table as I add new attribute, I can define how deep I need the lookup table to be or how many fields this particular lookup table need at the moment I create it, I can ensure that new lookup tables can be properly normalized as needed and never worry about getting stuck with a generic lookup that just won't support the specifications that I didn't predict.

Lastly, I remember that someone showed a way to get a listing of all tables programmically using MSys object. I figure that I could then create a query that returns me all lookup tables (provided I have given all of my lookup table a suffix or something to help me keep track) that is related with attribute table so I can validate the tables.

The only thing about that idea that strikes me as 'wrong' is that I am adding tables to the database as I go. I reason that as long I'm adding, and not modifying or deleting any existing tables, the structure will be okay.

Do you agree or is there a better way?

 

[Banana]

Maybe anyone has a suggestion?

 

[Banana]

I'm given to understand that adding tables to a mature database is not in itself a bad thing, but wanted to make sure one more point is clarified:

Is it acceptable to have one entity related to several lookup tables? I know that a table can have several relationship (at same level) with other tables, but wanted to make sure there weren't any exception to that rules.

 

[Rabbie]

Is it acceptable to have one entity related to several lookup tables? I know that a table can have several relationship (at same level) with other tables, but wanted to make sure there weren't any exception to that rules.

IMHO Yes - i think it is acceptable provided this reflects the relationships already established.

I feel however that there is a danger of being trapped in theoretical costraints. Remember in the real world we build databases to solve problems/ perform tasks etc. The design is only important in as much as it allows us to complete the task as easily as possible.

Anyone who doubts the value of a normalised database only needs to read the number of posts on these forums where the problems are caused by unnormalised databases/other poor design.

Get the design right (ie one that reflects the real life model) then the rest will fall into place