Question: Let's say I have this two tables: Store and Product. I want my store to have a list of products. How can I do that?

```
create table store(
id int unsigned not null auto_increment,
store_name varchar(30) not null,
product_list_FK int unsigned not null,
primary key(id)
);

create table product(
id int unsigned not null auto_increment,
product_name varchar(30) not null,
price float not null,
primary key(id)
);

```

I started something like that, but I don't know how to finish, can you guys help me?

Answer: 
Many-to-one (products can only have one store)

```
create table store(
    id int unsigned not null auto_increment,
    store_name varchar(30) not null,
    primary key(id)
);

Query OK, 0 rows affected (0.02 sec)

create table product(
    id int unsigned not null auto_increment,
    store_id int unsigned not null,
    product_name varchar(30) not null,
    price float not null,
    primary key(id),
    constraint product_store foreign key (store_id) references store(id)
);

Query OK, 0 rows affected (0.02 sec)

```

Many-to-many (products can be in many stores)

```
create table store(
    id int unsigned not null auto_increment,
    store_name varchar(30) not null,
    primary key(id)
);

Query OK, 0 rows affected (0.04 sec)

create table product(
    id int unsigned not null auto_increment,
    store_id int unsigned not null,
    product_name varchar(30) not null,
    price float not null,
    primary key(id)
);

Query OK, 0 rows affected (0.01 sec)

create table product_store (
    product_id int unsigned not null,
    store_id int unsigned not null,
    CONSTRAINT product_store_store foreign key (store_id) references store(id),
    CONSTRAINT product_store_product foreign key (product_id) references product(id),
    CONSTRAINT product_store_unique UNIQUE (product_id, store_id)
)

Query OK, 0 rows affected (0.02 sec)

```
This is an n-m relationship. One store has multiple products. One product is in multiple stores (presumably).

You do this with a separate table:

```
create table StoreProducts (
    StoreProductId int auto_increment primary key,
    StoreId int,
    ProductId int,
    constraint fk_storeproducts_store foreign key (StoreId) references Stores(StoreId),
    constraint fk_storeproducts_product foreign key (ProductId) references Products(ProductId)
);

```

This is called a junction table. You can maintain additional information in the table, such as a "no longer stocked" flag or "date of first stock".