SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot akohl's Avatar
    Join Date
    May 2001
    Location
    Israel
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    help with basic db concepts and example

    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.

    Primary key
    ----------
    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.


    Enforcing Uniqueness
    ----------
    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
    table.

    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.

    www.matifone.com



    users

    -----
    (pk) userID autonumber
    nameFirst*
    nameLast*
    nameFather*
    town
    userName
    password
    userImage
    userImageCaption
    userTextSubHeading
    userTextBody
    pageEdit [all, own, none]


    groups
    ------
    (pk) groupID autonumber
    grade*
    subject*
    group*
    groupImage
    groupImageCaption

    users_groups
    ------------

    userID
    groupID
    role [teacher,assistant,student]

    yearJune [(the year in June that this group assignment applies)


    pages
    -----

    (pk) pageNAME
    pageImage
    pageImageCaption
    pageTextHead
    pageTextSubHead

    pageTextByLine
    pageTextBody

  2. #2
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Reckon you've got all that right.

    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)

  3. #3
    SitePoint Zealot akohl's Avatar
    Join Date
    May 2001
    Location
    Israel
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Primary Keys can use more than one field. It's often nessecary to use dual primary keys in lookup tables that are used to manage "many to many" relationships

  5. #5
    SitePoint Zealot akohl's Avatar
    Join Date
    May 2001
    Location
    Israel
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You mean "junction table", right?
    Can you describe a case where you would use multiple fields for the primary key?

    How about in my case. Would you suggest doing that here as well?

  6. #6
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    user_groups requires a dual primary key(userID and groupID) inorder to insure uniqueness. Without it you can have the same user in the same group multiple times.

    I've never heard the term "junction table", but I suspect its just another term for "lookup table" or vice-versa.

  7. #7
    SitePoint Zealot akohl's Avatar
    Join Date
    May 2001
    Location
    Israel
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    Last edited by akohl; Jun 10, 2002 at 22:49.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •