SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    Shiver me timbers!! anthony_irl's Avatar
    Join Date
    Aug 1999
    Location
    Dublin, Ireland
    Posts
    495
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Arrow mySQL DB for a dummy

    Hi all,

    Im building a PHP project with a mySQL database at the moment and was having a slight problem with a couple of my tables. The database is used to store details on college subjects, courses and students. The problem is that I have this table called YEARS which will hold information about a particular YEAR of a course which relates to COURSE (ie. my college course would have 4 entries as it is 4 years long, other courses may be different). Here's the layout:

    Table YEARS
    ===============
    YID - auto_increment field
    CID - foreign key to unique identifier for each course
    YearNo - will hold the year number (normally 1-3)

    I need the CID and YearNo values as a joint primary key to stop the same year being registered to the same course twice. The problem arises in that the YID field also wants to be a primary key because it is auto_increment. If I do this then the same combination of CID and YearNo can be entered and I do not want this.

    I was thinking that I could just remove the auto_increment from YID and increment it manually everytime I enter a record. Would this cause update anomolies as the value may not be mutually exclusive? I know this is fairly complex but any help at all would be really appreciated. If you need any further explanations then please post.

    Thanks a lot,
    Anthony - How's tings?

    24 hours in a day, 24 beers in a case. Coincidence? I think not.
    Contact me by: PM Email NEW! Carrier Pigeon

  2. #2
    SitePoint Addict DevilBear's Avatar
    Join Date
    Oct 2001
    Location
    Hades
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Perhaps I'm missing it but, what's wrong with all three being the primary key?

  3. #3
    Shiver me timbers!! anthony_irl's Avatar
    Join Date
    Aug 1999
    Location
    Dublin, Ireland
    Posts
    495
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If I make all three the primary key then its possible for the same course/year combination to be inputted which cannot happen. The same year must not be registered more than once to one course (ie. Course 1 has Year 1, Year 2, Year 3). Hope that makes sense.
    Anthony - How's tings?

    24 hours in a day, 24 beers in a case. Coincidence? I think not.
    Contact me by: PM Email NEW! Carrier Pigeon

  4. #4
    SitePoint Enthusiast xev's Avatar
    Join Date
    Jan 2002
    Location
    USA
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think the best way to go would be to make YID your primary key. Then make CID and YearNo a combined unique key.

    Code:
    CREATE TABLE YEARS (
      YID    INT AUTO_INCREMENT NOT NULL,
      CID    INT NOT NULL,
      YearNo TINYINT NOT NULL,
      UNIQUE (CID,YearNo), PRIMARY KEY (YID),
    );

  5. #5
    Shiver me timbers!! anthony_irl's Avatar
    Join Date
    Aug 1999
    Location
    Dublin, Ireland
    Posts
    495
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi xev,

    Thanks for that. It seems to work alright but am not sure now whether I will need a seperate student table for each class as the students table I have at the moment may hold up to 2000 entries. What do you guys think performance wise? Thanks very much for the help.
    Anthony - How's tings?

    24 hours in a day, 24 beers in a case. Coincidence? I think not.
    Contact me by: PM Email NEW! Carrier Pigeon

  6. #6
    SitePoint Enthusiast xev's Avatar
    Join Date
    Jan 2002
    Location
    USA
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    2000 really isn't very many records. MySQL will perform well even with millions of records in a table as long as you index correctly.

    Just make sure that that you join on indexed columns when you do the join on the years and student table.


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
  •