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.
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.
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: