| Urgent – Fundamental – Primary key [message #21311] |
Thu, 25 July 2002 11:03  |
Swamy
Messages: 78
Registered: June 2002
|
Member |
|
|
I need to create two tables like employee and department with one to many relations. I need to load these tables everyday from flat files. These tables will grow with millions of records. My problem is with Primary key. I have two options.
First option: In the parent table, I can make a record unique by creating a primary key with 4 columns and I can create a foreign key in the child table by referencing these 4 columns.
Second option: I can create column with a system generated sequence number as a primary key in the parent table and I can create a foreign key in the child table by referencing this system generated column. I can create a unique constraint with the 4 columns that I mentioned earlier.
Please help me to find out which is the better way and appreciate for your advice.
|
|
|
|
| Re: Urgent – Fundamental – Primary key [message #21312 is a reply to message #21311] |
Thu, 25 July 2002 11:50  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
|
If those four columns involved in the composite primary key on the parent table will never be updated, then it would be safe to use those same columns in the child table as a foreign key. But, personally, I tend to use your second option (use a single sequence-populated primary key and a unique constraint on the other four columns) once the parent primary key candidate extends to more than 1-2 columns. It makes your joins to the child table so much simpler/easier.
|
|
|
|
| Re: Urgent – Fundamental – Primary key [message #21314 is a reply to message #21311] |
Thu, 25 July 2002 14:49 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
|
A one-column join _should_ be faster than a four-column join (the index is smaller, a row takes up less storage space, etc.). The only type of query that would be negatively impacted by this approach is one that would scan the child table only looking for values in one of the four columns. This type of query would have to be changed to also include a join on the parent table but the benefits of the one-column PK in the child should greatly outweigh this one possible slight disadvantage.
|
|
|
|
|
|
| Re: Urgent – Fundamental – Primary key [message #21343 is a reply to message #21311] |
Fri, 26 July 2002 12:53 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
|
Well - I agree with Todd that a single key is easier and maybe stightly more efficient than a composite key. In reality you are going to be hard pressed to show more than a few % difference in performance in most cases. The more important consideration to me is how the data is available in the flat files. If you have separate files for parent and child records, then your PK's are going to get allocated during load on the "parent record" flat file. When you load the "child record" file - you need to tag on those numbers as FK's. If the PK's and FK's are naturally occuring in your flat files - I'de be using those.
|
|
|
|
| Re: Urgent – Fundamental – Primary key [message #21345 is a reply to message #21343] |
Fri, 26 July 2002 14:13 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Andrew, yes, the load needs to:
select parent_pk
into child_fk
from parent p
where p.c1 = :a
and p.c2 = :b
and p.c3 = :c
and p.c4 = :d;
in order to get the FK values (instead of loading it directly from the child flat file), but even with this extra step (which performs well since there is a unique index on these columns in the parent table anyway), I still favor the single-column foreign key.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
