[RBASE-L] - Re: Schema Design Question: Look-ups

[Doug Hamilton]

Why not use a Master table (with some wasted columns/space) and then project individual temp lookup tables as needed? 1)  For each lookup table, write the PROJECT TEMP TABLE as Stored Proc, RUN SELECT or whatever to avoid code lying all over the place.      Or use one master Stored Proc/RUN SELECT and pass the table name in a parameter/variable. 2)  Run the Stored Proc or RUN SELECT in an On Before Start EEP of a form, beginning of an app, or even ON CONNECT. 3)  It eliminates WHERE clauses in the multi-choice controls if a master lookup table is used. 4)  You can add PK/FK, Rules & Triggers to temp tables for relational integrity, include it in the Stored Proc or RUN SELECT. 5)  You can index temp tables. 5)  You won't have all those little tables permanently cluttering up up the db. 6)  Temp lookup tables are supported with Static DB on. 7)  Updating a lookup table will require some extra programming - both the master and the temp table will either have to be updated or the master updated and the lookup deleted & recreated. The bests (and worst?) of both worlds? Doug p.s.  I haven't tried this, it just popped into my head now. Disclaimer: Your speed may vary. karentel...@cs.com wrote: I generally use one lookup table for all lookups except those that require a FK/PK relationship (which can be substituted with rules to the lookup table if you need them).   I usually run with staticdb ON, so yes it is difficult to add new tables, easy to add more rows to the lookup table.  And I feel better when I list a database, and there aren't 40 tiny little lookup tables, often with only 5 to 10 rows in each table.  That to me seems like a waste of space.  And I think it's no more difficult to remember the LookupCode value than remembering the lookup table name.  And I always know the names of the other columns in this table. Usually my lookup table has these columns:        LookupCode      Text 8        LookupText       Text 30        LookupInt          INT        LookupDate      DATE        LookupDouble   DOUBLE Karen Very good points, I prefer your way when I have a choice. On the other hand, if you have the db locked down with STATICDB, adding tables requires you to down the db to add the new tables. This make for a different kind of difficulty. depending on your situation. With a master table you can just add data and code it up. We actually use a mix, using a master table for fairly generic values and distinct tables when the data and relationships are elaborate.   Dennis McGrath