Overview of Data Model

We will be using retail data model for this section. It contains 6 tables.

  • Table list

    • orders

    • order_items

    • products

    • categories

    • departments

    • customers

  • orders and order_items are transactional tables.

  • productscategories 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