I use a similar approach to yours. For tables that have a PF/FK relationship I use individual tables, but for some others I use a master table. I have a bridge features file with 168 columns and over 70 columns requiring lookups, each with anywhere between 3 and 10 lookups. Rather than have 70+ individual lookup tables, I have a master bridge code table with over 500 rows. When choosing between 70+ tables, each with a few rows, or 1 master table, the answer, IMHO, is a no brainer. Perhaps, the best way to answer the question is that it depends on the particular circumstances; in my case, a combination of master lookup tables and individual lookup tables was the preferred solution. Javier, Javier Valencia, PE 913-829-0888 Office 913-915-3137 Cell 913-649-2904 Fax <mailto:[email protected]> [email protected] _____
From: [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Friday, September 03, 2010 3:54 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Schema Design Question: Look-ups 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
