Depends on your perspective. To me, DEPT is a master table in a parent child relationship. I go from DNAME=SALES to the list of EMPs. It all depends.

A master table is the same as a lookup table when you query from child to parent.
A lookup table is a master table.

No, it is not safe to say anything -- lookup tables can have more then 2 columns (zip code -> state -> region). Masters can have 2 -- no reason they could not.

It is all "semantics", simply use the term that has more meaning.

is this like the three laws of robotics? Not sure with the "principle of rule engine" is.

so, if you end up with a ton of views -- why not tables?

with tables, fkeys work properly.
with views, they do not.

with tables, hot spots are spread out.
with views, they are not.

with tables, things are "clear" -- a fkey relationship clearly documents which set of domain values are allowed.

with views, they are not.



generic is fine, just understand the downsides. It is always a tradeoff.

ABSOLUTELY - why else have them?
what is the point of the lookup table otherwise?

You need to maintain many lookup VIEWS -- views require just as much "care and feeding" as a small table does.

A single module can still handle multiple tables -- if you can pass in the name of a lookup so you can query "select ... from generic_table where lookup_name = :x", yu can query "select .... from " lookup-table

well -- if they do the novalidate/disable/rely thing AND we rely on them AND they are wrong -- you will get the WRONG answer via query rewrites.....


so, don't do that unless we can really RELY on the underlying data.

I've always prefered to let the database do my work, therefore I will still go back to "i'd like a lookup per domain "

short_desc would be perfectly acceptable -- assuming the primary key is a true primary key and not subject to change.

a) benchmark it. probably fairly equivalent. set up a simulation and see.


b) i'm against "caching" as a rule cause it implies you are writing lots of procedural code.

I prefer to do as much in a single sql statement as humanly possible. after that, sure, caching a small set of reference data in a plsql table type is fine if the data is allowed to become stale.

primary keys should be immutable.

if the "name" column can change - your version #1 is right

if the "name" column cannot change ever - version #2 is right

how silly.

because of course, theirs is the last application and last application language to ever want to touch this data.

Very "smart" of them...

(not)

ad hoc users should use views then - shouldn't they.


The problems - one table, no correct datatypes - you'd use a string, of a fixed size - regardless - for all lookups.

You would not have referential integrity - because the primary key of your lookup table would be (code, fieldname,tablename) and I doubt you would put code,fieldname,tablename in your fact tables would you. so data integrity = NOT THERE (please don't tell me the application will do it, the application cannot do it without locking tables and tell me the last time you saw a program with the lock table command in it....)

You would negatively affect the optimizer, especially if some table/fields had LOTS of entries and others did not.



And you know, you'd end up having to join to this single table over and over. Let us go back to the original "argument"


...
One person argues that since one table have so many codes and requires 10-20
lookup tables, ad hoc users find it very difficult to work with.
.......


Let's see, they would have to

select a.description, b.description, c.description, t.*
  from T, lookup A, lookup B, lookup C
 where t.field1 = a.code and a.fieldname = 'FIELD1' and a.tablename = 'T'
   and t.field2 = b.code and b.fieldname = 'FIELD2' and b.tablename = 'T'
   and t.field3 = c.code and c.fieldname = 'FIELD2' and c.tablename = 'T'

and for some reason they believe that is EASIER, more understandable, better for these poor ad-hoc users than:

select a.description, b.description, c.description, t.*
  from t, field1_lookup a, field2_lookup b, field3_lookup c
 where t.field1 = a.code 
   and t.field2 = b.code 
   and t.field3 = c.code;

I'm not so sure I agree it would be easier - the end user would still have to know

a) fieldname (duh)
b) tablename (duh duh - they are quering the table!)

and heck, they'd even have METADATA (eg: the data dictionary) to tell them WHAT TO DO - so their little ad-hoc query tools would probably actually form the JOIN (if you use separate tables) whereas if you use a single table, these tools would form BAD JOINS if ANY


Meaning: I see no advatanges, I see only glaring disadvantages - and the perceived advantage stated by this person is actually a huge disadvantage.

look at the data dictionary - they use VIEWS that present the end user (us) with meaningful stuff.

we query all_objects

we do not query the myriad of tables, that are joined in arcane manners with many tables.

we query all_objects - which uses JOINS and many times decode's to resolve lookups.


You present to ad-hoc users, which are typically not the most SQL proficient people, end user layers which provide them a good, documented start.

you don't have to use views.

But then your ad-hoc users will have to join themselves

*NO MATTER WHAT*

one lookup or not.

I say with many lookups, their life is better
with a single lookup - their life is miserable.


You present to the end user something that would make their life better. You tell me if you are going to create a view per table or several tables - what will satisfy their needs best.


I don't get your last paragraph. What do you mean "show ad hoc users the meaning of each code using a view"

Let me turn that around on you - how would you do this (whatever this is) without a view?


Look at the data dictionary - it does this, it gives somewhat descriptive values in columns - when you query all_tab_columns - you see datatype names, NOT data type codes (we store codes, you see names). You see table_names, not OBJ# (we use a number, you don't). You see column_names - not .... you get the picture.

what do your end users want, if you don't know, you need to talk to them and find that out.

you do whatever you want to present the data to whomever needs it in the format that works best for them?

I don't know what "defining codes in the data dictionary" means.


But, if you have a code in order_status_code like 'A', 'B', 'C' and that really means "Open", "closed", "back ordered", you would either

a) join to pick that up from the lookup table
b) use decode/case to present that information in a VIEW (we do that in our dictionary views - we have big decodes on lookups that are not volatile - so we don't have to join, the view just decodes the status)

that is sort of obvious - but works in the case where you use a check constraint nicely - and can work just as nicely in any controlled environment where you maintain your code table and your views using source code control/some configuration management tool. It is just an option, and the side effect is rather obvious.

Do what you want that best accomplishes your goal - a lookup table, a decode, a case statement, a check constraint - you make the call.