Hi all,

i had an hard time to come up with a good title for this post (my first here) so i shall explain my problem more clear right here:

Lets say i have to tables.
One is called 'Inzet' and the other is called 'Oogst'
In the first table the PK is defined by two fields: 'cel_code' and 'i_date'
(there are three more fields in this table, but that does not matter now)

In the second table, i have to refere to the first table (inzet) so the fields 'cel_code' and 'i_date' should reapear in the second table to act as a foreignkey..

Now my question is whether or not this is the best way to do this...

One other posibility i came up with is to add an 'inzet_id' to the first table and use that ID in the second table to refere to, so i could use an integer (auto_inc) as FK.

I also came up with some pro's and cons of these posibilities:
1) If i use and ID to identify an 'Inzet' that would be easier to work with in our application, i mean: it would be easier to make the code for the application.. so using an ID would be a good idea
but...
2) if i use an ID to identify the 'inzet' i loose the PK to prevent double and thus wrong values in the table 'inzet'.. so using a ID would not be good..

So here i have this design dilema..
I know both posibilities are posible, but i was wondering what was the preferd way if i want a good and sollid database design.

I realy hope someone can tell me something about these kind of problems.
Thanks in advance

If anything is not clear, please ask, i'm very much willing to explain some more. Realy hope it is clear the way i wrote this down..

Cheers
Eelko

> 1) If i use an ID to identify an 'Inzet' that would be easier to work with in our application
i do not understand why you think this -- it is no easier to use, in fact it's slightly harder

> 2) if i use an ID to identify the 'inzet' i lose the PK to prevent double
no, you don't, all you have to do is declare a unique constraint on the two columns combined (in addition to the PK for the auto_increment)


the design choice you are facing is between a natural and a surrogate key

search the web for those terms, you will find dozens of articles, forums, discussions, etc.

there is no one true answer

i personally favour using natural keys unless the key is comprised of several columns and has several levels of children (i.e. children, grandchildren, great-grandchildren, etc.)

read this article: Should sequence numbers be used as primary keys?
and make sure you also read A Truly ID-iotic Design which is linked to in that article

hi rudy, merry solstice.

I recommend surrogate keys with unique natural keys.

Hi r937,

Thaks for the quick reply.
I will read the articles you pointed me to after the christmas holidays i guesss.

If any one else has thoughs about this, please share them here.

I wish everybody who reads this a very happy christmass and an wonderfull 2006!

Cheers
Mc

I use natural keys as much as reasonably possible.

Often the surrogates are annoying when you're doing application development. For instance:

DEPARTMENT (ID, DEPTNUM)
EMPLOYEE (ID, FNAME, LNAME, DEPARTMENT_ID)

Now you know an employee is supposed to be in department ABCD. You need to do a separate query to get the ID from DEPARTMENT before you can insert or update the employee.

SELECT id from department where deptnum = 'ABCD'
=> 228

INSERT INTO EMPLOYEE (FNAME, LNAME, DEPARTMENT_ID)
VALUES ('John', 'Doe', 228);

Or, you can build it in to the query:

INSERT INTO EMPLOYEE (FNAME, LNAME, DEPARTMENT_ID)
VALUES ('John', 'Doe', (select id from department where deptnum = 'ABCD'));

But it's it far more clean and clear if you say:

DEPARTMENT (DEPTNUM)
EMPLOYEE (ID, FNAME, LNAME, DEPTNUM)

and

INSERT INTO EMPLOYEE (FNAME, LNAME, DEPTNUM)
VALUES ('John', 'Doe', 'ABCD')

?

I certainly think so.