Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

two forign keys

 
Zein Nunna
Ranch Hand
Posts: 245
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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,
Zein
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 245
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?

Thanks
Zein
 
Stan James
(instanceof Sidekick)
Ranch Hand
Posts: 8791
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Zein Nunna
Ranch Hand
Posts: 245
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?

Regards
Zein
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic