Today, we will create a simple database for customer order some products in a store.
Let's begin.
Database normalization
When we going to the restaurant and order somethings with the servant, after that we will have a order bill, in that will have a name of your, your address, time order, food you have order yet, price of food, and total all that. So if we create database with that information we will have a table like this.
Order
Customer FirstName
Customer LastName
Customer PhoneNumber
Customer Address
Product Name
Product Price
Product Unit
Time Orders
Total Price
The table database above is not good, it is not up to standard. Let me tell a little bit about database normalization. Every table database must obtain 3NF- Third Normal Form standard to reduce data redundancy.
1NF - First Normal Form : Value stored in cells must be single values and in the table there are not repeating columns.
2NF - Second Normal Form: Any filed in table that is not a key must be depend on the primary key.
3NF - Third Normal Form: Every filed that is not a key must be depend only on the primary key.
Comeback with Order table.
Customer FirstName, Customer LastName, Customer PhoneNumber, and Customer Address is not depend on order is depend on Customer.
Product Name, Product Price, and Product Unit is not depend on order is depend on Product.
Base on that we will have three table
Next step: Identify relationship
Next step we must identify relationship for three table above.
One Customer has many order and one order belong to one customer => Relationship is 1:n
One order has many product and one product belong to mane order => Relationship is n:n
( read this post to know more about relationship https://www.thienhang.com/2023/07/this-paper-deep-into-fundamental-of.html)
Final Step: Add foreign key
With relationship 1:1 we don't need to add foreign key, but with relationship 1:n we need add foreign key from table beside 1 to table beside n.
Now we have a complete database, very easy. You must memorize with relationship n:n we must change to 1:n to avoid data redundancy. In design database avoid data redundancy is very important, it can make database clean and reduce error when we access to the database to get data.
Tags:
mlops