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

    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 http://imgur.com/tkpQ2nZ

    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
    Posts
    2
    Hey steve

    sorry for the rushed response here.

    I would basically have something along the following lines:

    DVD_Titles
    ID
    Description

    DVD_Inventory
    ID
    DVD_ID
    Serial_Number

    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:

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

    DVD_Inventory
    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
  •