Re: surrogate keys or composite?

Mark do you reckon its quite common to have most Primary Keys composed of over 5 columns? (Ranges from 5 to 11)

Thx

Alex

On 6/20/06, Powell, Mark D <mark.powell_at_eds.com> wrote:
>
> >> You are right I wonder how to convert composite PK to Surrogate Keys.
> <<
>
> Why? What will you gain? None of the key columns appear subject to
> change so that is not an issue. Are any of these tables queried using only
> some of the columns in the PK. The use of a surrogate key will require
> these queries be rewritten as joins to the parent. These child tables have
> inherited their parent keys and it seems likely that some of the parents may
> have inherited PK's so you may end up converting two table joins of child
> tables into five and six table joins to produce the same results depending
> on how many multi-column keys you convert into surrogate keys. You should
> also check to see if any of these tables are stored as IOT's.
>
> HTH -- Mark D Powell --
>
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *amonte
> *Sent:* Tuesday, June 20, 2006 10:30 AM
> *To:* Mercadante, Thomas F (LABOR)
> *Cc:* oracle-l_at_freelists.org
> *Subject:* Re: surrogate keys or composite?
>
> Hi Thomas
>
> Thanks for the reply.
>
> You are right I wonder how to convert composite PK to Surrogate Keys.
>
> The new rows will be inserted using SQL*Loader so as long as I get the
> control file right it should be ok. To publish the new PK to child table I
> guess I would have to run MERGE between parent and child comparing the
> natural PK.
>
> Thanks
>
>
> Alex
>
>
>
>
> On 6/20/06, Mercadante, Thomas F (LABOR) <Thomas.Mercadante_at_labor.state.ny.us
> > wrote:
> >
> > Amonte,
> >
> >
> >
> > Are you asking how do you convert the tables to use a surrogate key? A
> > bit of coding will accomplish this. But your application also needs to be
> > changed so that when it inserts new records, it now creates the records
> > using the surrogate key rather than the natural keys.
> >
> >
> > The code to convert to surrogate keys is the easy part. Changing your
> > application may be the difficult part.
> >
> >
> >
> > Did I answer your question?
> >
> >
> >
> > Tom
> >
> >
> > ------------------------------
> >
> > *From:* oracle-l-bounce_at_freelists.org [mailto:
> > oracle-l-bounce_at_freelists.org] *On Behalf Of *amonte
> > *Sent:* Tuesday, June 20, 2006 6:32 AM
> > *To:* oracle-l_at_freelists.org
> > *Subject:* surrogate keys or composite?
> >
> >
> >
> > Hi all
> >
> > Reviewing an application over here and came across to an issue, probably
> > not a very big issue but have kept me thinking several days.
> >
> > The database is designed to report the "workflow" about payments to
> > suppliers in a Big Supermarket. It collects data from at least 6
> > Transactional systems, i.e 6 data sources.
> >
> >
> > Considering only the order management reporting module of the database
> > ----------------------------------------------------------------------------------------------------------------------------------------------------------
> >
> > The supermarket is right now a Organization with a company so we have a
> > company_code
> > The supermarket has right now 1500 stores so we have a store_code
> > The supermarket operates in 4 countries so we have a country_code
> > We have an order_code
> > We have a supplier_code
> > We have an ordering_date
> >
> > All 6 codes make up the Primary Key for the Order Table
> > The child table of this Order Table (Order details, line by line) is
> > made up of 6 codes plus the line_code
> >
> > In this database there are quite a few modules which works the same way,
> > composite natural keys to make up the PK. There are a couple of tables whose
> > PK are over 10 columns.
> >
> >
> > My question is if I wanna use Surrogate Keys how can I make to the rest
> > of world know which key is their parent? How do I publish the Surrogate to
> > the rest of the tables?
> >
> >
>

--
http://www.freelists.org/webpage/oracle-l