Project 1

Table ecommerce.merchants {
id int
country_code int
merchant_name varchar

"created at" varchar
admin_id int [ref: > U.id]
Indexes {
(id, country_code) [pk]
}
}

Table users as U {
id int [pk, increment] 
full_name varchar
created_at timestamp
country_code int
}

Table countries {
code int [pk]
name varchar
continent_name varchar
}

Ref: U.country_code > countries.code
Ref: ecommerce.merchants.country_code > countries.code

Table ecommerce.order_items {
order_id int [ref: > ecommerce.orders.id] 
product_id int
quantity int [default: 1] 
}

Ref: ecommerce.order_items.product_id > ecommerce.products.id

Table ecommerce.orders {
id int [pk]
user_id int [not null, unique]
status varchar
created_at varchar [note: 'When order created'] 
}

Enum ecommerce.products_status {
out_of_stock
in_stock
running_low [note: 'less than 20'] 
}

Table ecommerce.products {
id int [pk]
name varchar
merchant_id int [not null]
price int
status ecommerce.products_status
created_at datetime [default: `now()`]

Indexes {
(merchant_id, status) [name:'product_status']
id [unique]
}
}

Table ecommerce.product_tags {
id int [pk]
name varchar
}

Table ecommerce.merchant_periods {
id int [pk]
merchant_id int
country_code int
start_date datetime
end_date datetime
}

Ref: ecommerce.products.merchant_id > ecommerce.merchants.id 
Ref: ecommerce.product_tags.id <> ecommerce.products.id 
Ref: ecommerce.merchant_periods.(merchant_id, country_code) > ecommerce.merchants.(id, country_code)