We will be using retail data model for this section. It contains 6 tables.
orders and order_items are transactional tables.
products, categories and departments are non transactional tables which have data related to product catalog.
customers is a non transactional table which have customer details.
There is 1 to many relationship between orders and order_items.
There is 1 to many relationship between products and order_items. Each order item will have one product and product can be part of many order_items.
There is 1 to many relationship between customers and orders. A customer can place many orders over a period of time but there cannot be more than one customer for a given order.
There is 1 to many relationship between departments and categories. Also there is 1 to many relationship between categories and products.
There is hierarchical relationship from departments to products - departments -> categories -> products