Is the following acceptable foreign key usage

Question

I have the following database, the first table users is a table containing my users, userid is a primary key.

user table

The next is my results table, now for each user, there can be a result with an id and it can be against an exam. Is it ok in this scenario to use "id" as a primary key and "userid" as a foreign key? Is there a better way I could model this scenario?

results table

These then link to the corresponding exams...

enter image description here

score 2 · Accepted Answer · edited May 23 '17 at 11:48

2

I would probably not have userid as a varchar. I would have that as an int as well.

So the user table is like this:

userId int
userName varchar
firstName varchar
lastName varchar

And then the forenkey in the results table table would be an int. Like this:

userId int
result varchar
id int
examid INT

Becuase if you are plaing on JOIN ing the tables together then JOIN ing on a varchar is not as fast as JOIN ing on a INT

EDIT

That depend on how much data you are planing to store. Beause you know that there is a minimum chans that GUIDs are not unique. Simple proof that GUID is not unique. I think if I would design this database I would go with an int. Becuase it feels a little bit overkill to use a GUID as a userid

edited May 23 '17 at 11:48

Community

1
1

answered Apr 18 '12 at 10:55

Arion

31,011
10
70
88

I was actually considering using a GUID for the primary key of the user, rather than an int. Is this a bad idea? – David Apr 18 '12 at 11:29
2

@david99world : Remember to up vote the answers you think are good. That gives us all a warm fuzzy feeling :P – Arion Apr 19 '12 at 08:44

score 1 · Answer 2 · answered Apr 18 '12 at 10:55

1

Provided that each user/exam will only ever produce one result, then you could create a composite key using the userid and exam columns in the results table.

Personally though, i'd go with the arbitrary id field approach as I don't like having to pass in several values to reference records. But that's just me :).

Also, the exam field in the results table should also be a foreign key.

answered Apr 18 '12 at 10:55

weenoid

1,156
2
11
24

I'd also recommend renaming the exam foreign key field in the results table to 'examid' to keep the naming consistent. – weenoid Apr 18 '12 at 10:57

score 1 · Answer 3 · answered Apr 18 '12 at 11:12

Another way of doing this could be to abstract the Grade Levels from the Exam, and make the Exam a unique entity (and primary key) on its own table. So this would make a Grade Levels table (pkey1 = A, pkey2 = B, etc) where the grade acts as the foreign key in your second table, thus removing an entire field.

You could also normal out another level and make a table for Subjects, which would be the foreign key for a dedicated Exam Code table. You can have ENG101, ENG102, etc for exams, and the same for the other exam codes for the subject. The benefit of this is to maintain your exams, subjects, students and grade levels as unique entities. The primary and foreign keys of each are evident, and you keep a simple maintenance future with room to scale up.

You could consider using composite keys, but this is a nice and simple way to start, and you can merge tables for indexing and compacting as required.

score 1 · Answer 4 · answered Apr 18 '12 at 11:45

1

Please make sure you first understand Normal Forms before actually normalizing your schema.

answered Apr 18 '12 at 11:45

npclaudiu

2,401
1
18
19

Ah ok, so I should split out the values in my result table into seperate tables to have first normal form. – David Apr 18 '12 at 13:46

Is the following acceptable foreign key usage

4 Answers4