RE: surrogate keys or composite?

>> 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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of amonte
        Sent: Tuesday, June 20, 2006 10:30 AM
        To: Mercadante, Thomas F (LABOR)
        Cc: oracle-l@xxxxxxxxxxxxx
        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@xxxxxxxxxxxxxxxxx > 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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of amonte
                Sent: Tuesday, June 20, 2006 6:32 AM
                To: oracle-l@xxxxxxxxxxxxx
                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? 
                
                


Other related posts: