I think I might be confused about the some of the concepts in database table
design. I'm not sure. I just need someone to confirm or correct or otherwise
comment on my ideas.
Here's my thinking, followed by a basic question and an example.
A primary key is used to reference each listing in a table so that other
tables can be joined to it using criteria of identity with its referer
(foreign key) in that other table. A Primary key must be unique. It also should
consist of just one field so that the reference can be simple.
For example, groups.userID=users.userID. An automatically incremented integer
is a good way to implement this.
Lets say I want to ensure that the same user is not entered into the
users table twice. I want the database to reject entires into the users table
whenever a combination of nameFirst, nameLast and nameFather has alreadiy been
entered in that table. I don't want to use this to reference the listings in
the users table this way. That would complicate the reference and increase the
number of fields I need to put into tables for the purpose of referencing this
Does it make sense to use a set of fields to enforce uniqueness
and an additional field to serve as a primary key for purposes of
referencing the listing in another table?
For example, here is a db for a school website I'm planning that displays
pictures and textual content for specified classes, pupils and teachers.
By the way, here's the url for the opening page of that school webiste
that I'm working on if your interested. Its just an opening page that the
principal doesn't like and leads nowhere at this stage.
On the uniqueness issue, you could optionally move that enforcement to your application and out of the database. Say if there are two brothers who have been given the same name (unlikely I know), how will you add them both? By moving that to the application, you can provide a means for special cases. But I may get beaten by dB purists for saying that.
One other thing, from my own experience. Access, though good, clouds some issues of database design by making them too easy. The first database that I really learnt something from was MySQL, because it's so raw. Could be worth playing around with (perhaps using Kevins famous article)
Thanks for confirming that I'm on the right track.
Yes, I figure mySql may very well be the next stage in my education. We'll see.
Curious you ask about two brothers with the same first name. Really, that just can't be. And anyone who gives two of his kids the same first name has to take into acount that their data will end up getting all messed up wherever they go.
A more likely question might have been, Why not use groupID, since this db groups students into classes, instead of father's name as part of the unique index? Well, we happen to have two girls in the same class, who are from the same village, and have the same first and last names. But they aren't sisters and luckily for me, they're fathers have different names.
This is what I was asking about. Namely, enforcing uniqueness and creating a reference are two different things. I use the term Primary key as the unique field which is used for referencing the listings of this table by another table. But you can also set a multi-field index to enforce uniqueness and then choose whether or not to make it a primary key.
The terninology is confusing because the word "key" seems to imply reference. But so does the word "index".
You are right about the need to enforce uniqueness in the users_groups table. But I will need to include all of the fields in this uniqueness. A kid could be held back a grade. So his userID would be entered twice with the same groupID but a different year. He could also have two different roles in the group the same year.
So according to my logic, I suppose I should include all the current fields in the uniqueness index and create an additional auto-increment field to serve as the primary key for the users_groups table.