Re: Single Code Table or Separate Code tables dilemma

> Some good points... different strokes for different folks ya know. :)

exactly.

> Partitioning a code table? Really??? Show me the money, er, cache. If your
> lookup/validation data is not "mixed together" then why would you need to
> partition it?

Why not? If you can imagine a code table with 10M rows, somebody might want to partion it with local indices. ( For those who prefer to search for data among 10 rows :-)).

> Regarding "lookup" or validation tables and more complex "rules" tables...
> do they ALL go into your master code table?

It depends. "Rules" tables do not go into master lookup. Validation - maybe.. if the number of values is small and unvariant, they can go into check constraint.

 > For instance, what about a "code
> table" for all valid U.S. zip codes? Do they go into your master code table?
> State abbreviations too? What about valid city/state/zip code combinations
> which you can get from the U.S.P.O.? At what point are
> validation/lookup/rules data not in the master code table and how do you
> decide?

If you have relationship between lookup tables, probably they will not go to the master lookup table ( although is is still possible ). Depends on a common sense. If you have 100 tables all of them are of ( t_id, name, description ) - they will sure go.

> The code table technique may be fine for smaller apps but couldn't
> it become unwieldy for larger apps with 5000+ tables? Of which 1-3000 would
> be "lookup/validation tables?"

I think it still work for large models. Large app with 5000 tables of which 3000 tables are lookups is a medium app with 2001 tables :-).
Do you prefer to administer 3000 tables instead of one, even partitioned?

> Do Oracle ERP or SAP ERP apps do this?
> Curious.

They do not. But I do not consider them a good example of desing and programming.

Regards,
Michael

> -----Original Message-----
> Ghosalkar
> Sent: Thursday, March 22, 2001 3:01 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> > -----Original Message-----
> > From: Michael Netrusov [mailto:mn_at_g-fax.com]
> > Sent: Thursday, March 22, 2001 4:23 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: Single Code Table or Separate Code tables dilemma
> >
> >
> >
> >
> > > > Referential integrity is still present if you create
> > Master lookup table
> > > > with type attribute
> > > This requires a "composite referential integrity
> > constraint" such as:
> > > alter table tname
> > > add constraint fk_whatever
> > > foreign key (extra_column_for_code_table_key,
> > > column_i_really_care_about)
> > > references master_codes(lookup_id, lookup_type);
> > >
> > > Still don't like it. Maybe it's just a preference thing...
> >
> > What's wrong with a composite foreign key constaint? Works
> > for me all the time..
>
> As a duhveloper wont u like to pass one argument to a procedure rather than
> two?
>
> >
> > > > granular control over the individual code table
> > > You DON'T have granular control for caching specific tables because
> > > everything is mixed together. (Hawaiian pidgin translation:
> > "All kalikaka
> > > li' dat. Da' kine chop suey. Easy Brah." :) No control for different
> > > indexing requirements or not even having an index for small
> > tables where it
> > > would be better to do a FTS.
> >
> > I DO. Nothing is mixed - you can even partition this table if
> > you prefer to keep unlike data in different places.
> >
>
> so u will create one single table and then partition it. do u think
> partitioning is cheap from maintenance point?
>
> > > > prefer to have multiple numerous small tables or one large?
> > > Numerous small tables are not a problem, actually a benefit
> > because you have
> > > more granular control for tuning. I once had the misfortune
> > of having to do
> > > reports where the "mother of all code tables" had around
> > 100,000 rows. (It
> > > was a big, dumb 3rd party app with roots in COBOL and it
> > had a lot of
> > > unmaintained junk in it). To pick up a description in a
> > large multi-table
> > > join query I had to join against the mother of all code
> > tables where a small
> > > 10 row table would have sufficed.
> >
> > I don't think a join to 100K rows table versus a join to 10
> > rows table would make a big difference.. Most likely the join was slow
> > was it? ) because of some other factors.
>
> u dont mind searching 10 starbuck's shops to find one cup of coffee?
> also u dont mind releasing 10 different versions of ur programs to ur client
> and then let the client search thru them for the best one.
>
> >
> > > I'm not exactly a relational purist and for some apps code
> > tables may work
> > > just fine. But usually it's just for duhveloper convenience
> > (laziness) and
> > > why make life easy for them ;-) at the expense of "normal"
> > relational
> > > design. Especially if they haven't bothered to answer the
> > challenge I posed
> > > below? When I was a developer I had a set of objected oriented class
> > > libraries and could bang out a new window for a lookup
> > table in 60 seconds.
> > > Of course I probably spent 600 hours developing and maintaining my
> > > libraries... sigh. Oh yeah, developer designed tables
> > containing metadata
> > > come in handy also.
> >
> > Do developers design ER models in your shop? It should be you
> > or a DA. :-)
> >
> > > Duhveloper combat is so much fun! :-) Unfortunately they
> > usually outnumber
> > > us DBA's so we have to be particularly nimble. ;-)
> >
> > In my current shop I am a pl/sql developer :-) ... as well as
> > a DA and a DBA. This saves me a LOT time and nerves.
>
> so whose is the PM and client?
>
> I taste blood :)
>
> Har Har Mahadev ... Thats a Battle Cry :-)
>
> Regards,
> Mandar
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Steve Orr
> INET: sorr_at_arzoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Michael Netrusov
  INET: mn_at_g-fax.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).