TSQL Tutorial: Designing Data Model

Data Model for Designing Database:

 

This blog shows how to design a relational database. For understanding database design we need to understand how to create data model.

Problem: We want a system for a retail store for sales details. The system will have
  • Customer Data and purchase made
  • Multiple product in Purchase order
  • Information about product purchased

As per initial observation of above requirement tables comes in mind are customer with details of product.


The table will have following column.

 
Customer Id
Customer Name
Order Number
Product Name
Product  Quantity
Units
Price\unit
Total Price
1
David Miller
Or01
sugar
1
kg
50
50
1
Badree
Or02
Water
2
litre
20
100

 

 

Now if you see this table structure you will notice

 

If a customer buys multiple products than all the details customers will be repeated again and again in each row.
Every time you enter a product you need to enter the unit which is again a repetitive data.
For our reporting purpose when we write the query we may need to use multiple aggregations and need to write complex queries to fetch data.
For solving all above mentioned issue we will divide this information in multiple tables so to avoid redundancy and get easily data.

For above example we can add below tables..

 

Customer Table: it will hold data related to customer.

Product Table: it Hold the product details.

Order Table: It will hold the for order details and customer associated with multiple order.

Order details: will have many to many relations between Order and product with Order Id and Product Id will be the foreign keys from base tables.

 

Customer
Customer ID
Customer Name
Customer Address
Gender
1
David Miller
ABC
M
2
Badree
XYZ
F
Product
Product ID
Product Name
Unit
Price per\unit
1
Sugar
Kg
50
2
Water
Lit
20
3
Milk
Lit
70
Order
OrderID
Order No
Customer Id
1
OR01
1
2
OR02
2
3
OR03
1
Order Detail
Id
OrderID
ProductID
1
1
1
2
1
2
3
2
1
4
3
3

 

Below tables will be data model for problem stated in the start of chapter which hold the entire information in small chunks.

No comments:

Post a Comment