Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2015

    Foreign Key 1 to Many relationship

    I am trying to design a database for an online store.
    The store sells DVDs each copy of a particular dvd has a unique serial Number. I am trying to create a database based around this. I was hoping to have a product table which would list all the DVD's and then have an inventory table which would have all the various unique IDs for each copy of a DVD.

    Here is a picture of these two tables in my model

    I can't figure out how to link the tables in a way which would work as you can see the relationship is with the serial number as a foreign key for dvd table.
    This won't work though as I need each copy of a particular dvd to have a serial number.

    All help Appreciated

  2. #2
    Join Date
    Dec 2015
    Hey steve

    sorry for the rushed response here.

    I would basically have something along the following lines:



    The "ID" column in each table is a unique auto-increment int type.
    In the inventory table, the DVD_ID would be a foreign key to the "ID" column of the titles table. You can add other columns that you need like price etc, but these should be your core joins. As an example see the data below:

    ID... Description
    1... Independance Day
    2... Judge Dread
    3... smurfs
    4... Avengers

    ID... DVD_ID ... Serial_Number
    1... 1... 12350823-435-35
    2... 1... 34324543-434-23
    3... 1... 48424723-423-23
    4... 1... 23480723-434-43
    5... 2... 48734723-434-25
    6... 3... 48734687-423-23
    7... 3... 46793873-423-23
    8... 4... 57648732-467-23

    Hope that helps if you were still stuck
    Last edited by kdogg123; 12-22-15 at 09:59. Reason: added dots to improve formatting

Tags for this Thread

Posting Permissions

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