SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2001
    Location
    Atlanta
    Posts
    48
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to create i to many relationship database

    Hello,

    I need to ccreate database, which has 1 to many realtionship, please tell me htat how I can do that.
    Aneal

  2. #2
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, here is an example of a one-to-many relationship. A car can have four wheels, but each wheel can only be attached to one car. So this is a one-to-many relationship. Now lets say for some reason we need to keep an inventory of all the wheels on a fleet of cars. Each wheel will have its own ID number and each car has its own ID number.

    Here are the tables

    Car
    ---
    ID (primary key)

    Wheel
    ----
    ID (primary key)
    carID (foreign key references Car.ID)

    Lets say we need to know which wheels belong to Car.ID=3

    SELECT *
    FROM Wheel
    WHERE carID = 3

    Lets say we want to know which Car has been fitted with Wheel.ID = 15

    SELECT carID
    FROM Wheel
    WHERE ID = 15

    Now, if you have a many-to-many relationship you need to create an an extra "lookup" table to hold the details of the relationships between the two entities. For example, lets say a student takes many classes, but each class has many students (to use a recent example from another thread). So we represent the relationship like so:

    Student
    ------
    StudentNumber (primary key)
    StudentName
    etc...

    Class
    -----
    ClassCode (primany key)
    ClassName
    etc...

    ClassStudent
    ---------
    StudentNumber (foriegn key references Student.StudentNumber)
    ClassCode (foreign key references Class.ClassCode)

    Now for table ClassStudent you could either create an artificial key such as an ID number to be the primary key or define (StudentNumber, ClassCode) to be the composite primary key.

    Now if we want to know which classes student 123 is taking we first need to find the keys from ClassStudent

    SELECT ClassCode
    FROM ClassStudent
    WHERE StudentNumber = 123

    and to find the keys of all the students who are taking class MATH100

    SELECT StudentNumber
    FROM ClassStudent
    WHERE ClassCode LIKE 'MATH100'

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2001
    Location
    Atlanta
    Posts
    48
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, Thank you that's been a great help. Also the forums are great too.

    Thanks again
    Aneal


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
  •