Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2010
    Posts
    9

    Help designing a simple database

    First of all I'm a complete newb and I need help. I don't expect you to solve everything and am willing to make an effort.

    I want to create a web page where I would track if a person was on a lecture or not. The HTML/CSS part is done, the PHP part will be, but the database part is what I don't know how to do.

    This is what I need to do:

    Person P1 attended lecture L1
    Person P2 attended lecture L1
    Person P3 attended lecture L1

    Person P1 attended lecture L2
    Person P2 attended lecture L2
    ...

    There are about 20 persons, and exactly 15 lectures (L1-L15)

    I created a table Lectures which has 2 fields LectureID (primary key) and LectureName. I also created a table Persons which has 5 fields, Person ID (primary key) and 4 others which describe that person.

    I don't know how to "remember" the fact that a person was on a lecture. I definitely need to remember it so I can use it later.

    Please help me

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    CREATE TABLE person_lectures 
    ( personID INTEGER NOT NULL
    , lectureID INTEGER NOT NULL 
    , PRIMARY KEY ( personID, lectureID )
    , INDEX lecture_persons ( lectureID, personID )
    , FOREIGN KEY ( personID ) REFERENCES persons ( personID )
    , FOREIGN KEY ( lectureID ) REFERENCES lectures ( lectureID )
    );
    
    INSERT INTO person_lectures VALUES
     ( 1, 1 ) -- Person P1 attended lecture L1
    ,( 2, 1 ) -- Person P2 attended lecture L1
    ,( 3, 1 ) -- Person P3 attended lecture L1
    ,( 1, 2 ) -- Person P1 attended lecture L2
    ,( 2, 2 ) -- Person P2 attended lecture L2
    ;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2010
    Posts
    9
    Great! Thanks a bunch!

Posting Permissions

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