Larry, 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 ________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of Lawrence Lustig Sent: Friday, September 03, 2010 12:51 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Schema Design Question: Look-ups Use separate tables for separate entity types. If you mix them: 1. You lose the ability to use relational integrity constraints (primary / foreign keys) to enforce correctness. 2. You have to settle on the lowest common denominator for the value column data type, generally a long text field. This wastes space, complicates datatype checking, and makes it harder to compare values to the table with the real data, where the column is likely to be a different type. 3. You have to write a WHERE clause on every multi-choice control (drop-down, pop-up menu, etc). -- Larry ________________________________ From: Bruce Chitiea <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Fri, September 3, 2010 1:27:34 PM Subject: [RBASE-L] - Schema Design Question: Look-ups All: Look-up tables proliferate like computer cables. Remembering my kids' names is hard enough. Wondering if there's a simpler approach, balancing coding complexity with performance: Anybody have thoughts/experience regarding two approaches: 1) individual look-up tables - each dedicated to a specific 'idea' (category); 2) a master look-up table - all 'ideas' contained within a category field - look-ups being performed through where-clauses targeting the specific category. zMasterLookUpTable zCategory - (Indexed) Where-clause target zData - (Indexed) Data values zSortOrder - Category-data sort order Just wondering. Appreciating all you all do for all of us, bruce chitiea safesectors inc
