RE: surrogate keys or composite?

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Tue, 20 Jun 2006 12:48:38 -0400
Message-ID: <5A14AF34CFF8AD44A44891F7C9FF410507A85AA2@usahm236.amer.corp.eds.com>

It would not be the first such system design. It is probably safe to say that most systems have PK that are composed on only one to three columns, but I have seen systems that have longer keys. You need to look at the design and what the keys represent. You need to look at how the data is organized, consolidated, and related. How is it being accessed? Or if the application does not yet exist what are the intended accesses? What information is known when the query is submitted?

I do not remember the first post stating at exactly what stage the application is at, but obviously if no code has been written changing the design is a must simpler task than changing the database once programs that access it already exist.

If some of the tables are summary rows does the application provide a means to explode the summary back to the detail? You may find that having the keys columns serve a purpose other than just being the PK.

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 12:08 PM
	To: Powell, Mark D
	Cc: oracle-l_at_freelists.org
	Subject: 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

Received on Tue Jun 20 2006 - 11:48:38 CDT