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