File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes two forign keys Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "two forign keys" Watch "two forign keys" New topic

two forign keys

Zein Nunna
Ranch Hand

Joined: Mar 31, 2005
Posts: 245
Hi guys,

I have a small problem.

I have a database table, whose primary key is two foreign keys. I dont know how to specify this in SQL in order to create the table.

It sounds a little weird, but look at this example,

Table User (userID*, firstname, lastname.....)
Table Reports (reportID*,.... )
Table ReportEvaluations (userID**, reportID**, grade, date... )

One user is allowed to evaluate one report only once. Each report is evaluated by upto 15 users.

So my questions are, is what I'm trying to do 'sound' in SQL. If so how would I create a table in MySQL for 'ReportEvaluations'.

Thanks in advance for your thoughts,
David O'Meara

Joined: Mar 06, 2001
Posts: 13459

Personally: reportEval should have its own primary key, it should have two foreign keys to the other tables, and there should be a separate uniqueness constrain on the foreign keys if it is required.

Table ReportEvaluations (evalID*, userID**, reportID**, grade, date... )
Zein Nunna
Ranch Hand

Joined: Mar 31, 2005
Posts: 245
Thanks Dave,

Ive managed to implement it the way I stated above.

I dont really see the point of evalID*, as you have stated, because of the business rules, you'll never use the evalID*, it will always be inconjuction with one (or both) of the foreign keys.

Just out of intrest, how will what I have done impact on scaling? i.e. when I'm recieving large amounts of data etc.? will it slow me down?

And is my methodology sound from an SQL point of view?

Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
Zein, what you're suggesting is called a natural key, composed of meaningful business data. What Dave suggested is a surrogate key, made up out of thin air, often a GUID or an incrementing integer. There are pros and cons to both. Google for natural surrogate key and find some discussions about which ones work well in what conditions.

My corporate environment dictates surrogate keys in almost all situations. Right now I'm considering playing with natural keys in a small database just to see how it works out.

A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
Zein Nunna
Ranch Hand

Joined: Mar 31, 2005
Posts: 245
Hi Stan,
Thank you for your response.
I have read about what you mentioned in your post. I get the feeling that surrogate keys are favoured over natural keys.

In my personal case, I think a natural key is the best, but i get the feeling surrogate keys are favoured mainly because overtime business logic changes, or migration may occur in which case natural keys can become particularly messy.

I do however lack corporate experience, so may there's something seasoned developers have experienced that I'm yet t learn?

I agree. Here's the link:
subject: two forign keys
It's not a secret anymore!