RE: Single Code Table or Separate Code tables dilemma

> -----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: Mandar Ghosalkar
  INET: MandarG_at_gsr-inc.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).