Database design question

Question

Hi all,

I have a table products and table sizes. Each product can have multiple sizes. How can I design my database in order to not have repeated entries of the same product?

Thanks in advance

Answer 1

The typical approach to a many-many relationship is to have a mapping table called Product_Size that contains the Primary Keys of each table.

create table Product (
    id uniqueidentifier not null,
    name varchar(255),
    primary key (id))

create table Size (
    id int,
    name varchar(255),
    primary key (id))

create table Product_Size (
    productId uniqueidentifier,
    sizeId int,
    primary key (productId, sizeId),
    foreign key (productId) references Product(id),
    foreign key (sizeId) references Size(id))

Answer 2

product Table

 1. product id 
 2. product name
 ......

Product Size

 1. Id
 2. ProductId( Foreign key form product table)
 3. Size 

Answer 3

It depends, each product can have multiple sizes, but can different products have the same size?

If they can't then you have a one-many relationship and you need a ProductSize table which holds the primary key of the product.

ProductSize (SizeID, ProductID, Size)

If they can then you have a many to many relationship and you can break this up by having three tables, Product, Size and ProductSize, where Product contains products, Size contains sizes and ProductSize maps each product to the sizes available, by holding the primary key of Product and Size.

Product (ProductID, ProductName)
Size (SizeID, SizeName)
ProductSize (ProductID, SizeID)

Answer 4

try something like this:

Products
----------
ProductID    PK, auto increment int
ProductName
....

Sizes
------- 
SizeID      PK, auto increment int
SizeInfo
....

ProductSizes
--------------
ProductID   PK, FK to Products.ProductID
SizeID      PK, FK to Sizes.SizeID