For this article we will create a
simple database modeling when we say database modeling I will tackle Entity
Relationship Diagram and Database Normalization in Simplest form.
For
entity relationship diagram we need to identify first on what are the entities
involve and attributes that are involve on our database for this tutorial we
will create a database for Point of Sale (POS) and inventory system.
Now
lets identify the entities, we may identify what are the entities by knowing
what are the functionalities of this system, the interview and observation
method should be done to identify this functionalities or what we call system
analysis and design (SAD), let say we have done data gathering and the entities
that we know are the following.
a.
Customer– when we say customer’s module we would
like to know on what are the things that the customer would do.
b.
Product - product module is where we want to
know on what is the product that this company offers.
c.
Users - we would like to know who is the user involved
in this system the cashier etc.
Is that all the
entities and the proper name for our entities? Hmmmm. Let’s see later we will
explode this database. Now let’s identify on what are the fields/ attribute for
this entities.
Customer
module -
Name, address, birthdate, age, Contact number (Basic info), Product
purchased, quantity price,Date purchased
Product Module –
Product Name, Stocks, price,Supplier,Contact number
User
– Username, Password
For
this we already identify what are the fields and its entities the next step is
to normalize the entities with their attributes.
There
are ways to normalize a database using the first to fourth normal form actually
we already done with the first normal form wherein we explode all the attributes
of an entity, on following this forms it make me confused so I’d rather do a
more basic way? Hehehe let’s see
This
is my own way on normalizing a database I’ll give you the steps.
1.
Is to identify the primary key for each
entities/ table.
Primary key -
it is unique on a table/entity
usually it is used as an field name of ID, usually this primary key is
used to refer to another entity
2.
An attribute with several forms should be
divided, and for this different forms create a new field e.g(Address: Province, Barangay ,street etc..)
3.
A multi valued fields should be segregated and
create a new table for it.
4.
Use foreign key to connect one table to another
Note:(all table should be connected)
Foreign key - is a type of key wherein the primary key is
used to another table to show a relationship among tables
Customer
module -
Name, address, birthdate, age, Contact number (Basic info), Product
purchased, quantity price,Date purchased
Actually
we don’t need to use this entity but for the sake of demonstration let use it
lets apply the steps that I already enumerated .
Step 1 - dentify the primary key – is there any field
you can use which is unique? the name? hmm.. actually there are person who have
the same name, another is combination of two attributes which is the name and
its birthdate? Hmmmm. If we do that this primary key would have a long value it
eats a space…. The easiest way is to assign a new field a new field would be (CID) or customer’s ID.
Step
2 – multi form attribute should be divided in several forms, the attribute name, address and “birthDate”? why? How? Name can be
divided to (LastName, FirstName, MiddleName) , Address( Barangay,
Municipality/province and street) for what? For searching purposes now do we
need to divide birthDate? Hmmmmmm.. no because
a birthdate is just and stored attribute.
Step
3 - A multi valued fields should be
segregated and create a new table for it for this table a multi valued field is
what? Hmmm its Contact Number, a person may have one or more contact number, so
we will have a table for contacts( containing contact number) , another is
Product Purchased, Quantity, Price and Date Purchased, this things are
describing the product entity we need to put it on the Product Table? Hmmm.. no
because there is an entity that describes by this attributes it is entity
called (“Ordered item”) ordered item are
the product that we already sold. This attributes describes this table.
Step
4 – Identifying the foreign key, we already exploded our customer entity hmmm let’s
see.
Customer
– CID,LastName,MiddleName,FirstName,Age, Birthdate
Contacts
– ContactNumber
Ordered
item – Product Purchased, Qunatity, Price, Date Purchased
This
is the question how would you know the contact number of a specific customer?
From that structure that we already done?
We have a table name contacts but there is no connection to the table
customer so we don’t have a way to see what the contacts of a certain customer
are, solution for this is putting the CID in Contacts, now CID become a foreign
key on table contacts, another problem rises with our table ordered item how do
we know on what are the products that a specific customer had purchased? Hmmm
solution is to put CID in that table so the final Structure for customer is
Customer
– CID,LastName,MiddleName,FirstName,Age, Birthdate
Contacts
– CID,ContactNumber
Ordered
item – CID, Product Purchased, Qunatity, Price, Date Purchased
Product
Module – Product Name, Stocks, price,Supplier,Contact number
Step
1 – okay for the primary key for this table I will just create a field named
PID.
Srep2 - there is no multi Formed field so its ok.
Step3 -
Multi valued field would be the Supplier and contact number, So we will
just have to segregate the supplier and
contactnumber. So the final entities would be
Products
–PID, ProductName, Stocks, Price, Supplier
Supplier - Supplier
Suppliers_Contacts
- Contact_number
Take
note that a product may have several suppliers.
Step
4- Foreign key.
Same
as the scenario with the customer we don’t know what would be the contact
numbers of a supplier so we will just put SID to supplier Contacts, another is
we don’t know who is the supplier for a certain product we can put PID to supplier, or put it to a table named Product Supplier wherein the field PID and SID is the attributes
and take note that a supplier may supply different product the first approach
is that we assume that a supplier just supplies on product.
Products
–PID, ProductName, Stocks, Price, Supplier
Supplier -SID, Supplier
Suppliers_Contacts
–SID, Contact_number
Product
Supplier - PID, SID
Another
revision for our table ordered item is instead of using product purchased we
will change it to PID
Ordered item –
CID,PID,Quantity,Price, Date Purchased
User
– Username, Password
Step 1 – Identify the Primary
key so I’ll just use the UID but we need to add new fields, for example if the
user forgot the password then what would be the solution? We will just add a
new field which is secret question and answer, and another is to put the type
of the account, for cashiering or for inventory or what.
User
– UID,Password, Secret_Question,Answer, Account_type
Step
2 – there is no multi formed field
Step
3 - there is no multi valued field
Step
4 - foreign key
Hmm.. we should know who sold the products right so
we can put the UID to ordered item so ordered item would be
CID,PID,Quantity,price,
Date Purchased, and UID.
So the final table and entities
are:
User – UID,Password,
Secret_Question,Answer, Account_type
Products –PID, ProductName, Stocks,
Price, Supplier
Supplier -SID, Supplier
Suppliers_Contacts –SID,
Contact_number
Product Supplier - PID, SID
Customer –
CID,LastName,MiddleName,FirstName,Age, Birthdate
Contacts – CID,ContactNumber
Ordered item – CID, PID, Qunatity,
Price, Date Purchased,UID
Now were done with normalizing and
identifying the entities and its attributes..
were not yet done we would have our database schema like this, and of
course we need to do our ERD were Done with Entity the one last part is
“Relationship”
User
|
UID
|
Password
|
Secret_Question
|
Answer
|
Account_type
|
Products
|
PID
|
ProductName
|
Stocks
|
Price
|
Supplier
Suppliers_Contacts
Product Supplier
Customer
|
CID
|
LastName
|
MiddleName
|
Age
|
Birthdate
|
Street
|
Barangay
|
Province
|
Contacts
Ordered item
|
CID
|
PID
|
Qunatity
|
Date Purchased
|
UID
|
Relationship - is how does a
Entity is connected to another entity the diamond sign is used in chen model
but in crows foot is just a caption on a
line, take note that we have a type of entity which is called weak entity, this
weak entity is formed from the values from one entity it has no default value.
With this figure
we created a diagram for our POS and Inventory DB all we need to consider is
the cardinalities for each entity and the relationship among entities take note
that the relationship of one entity to another is a verb and this module ive
used a a type of ERD model which is called crows foot the cardinalities are
represented by lines before the entities.
On the USER the cardinality going to the ordered_item
is 0 to many because a user might not sell a product in the sense that there
are different types of user, then the cardinality going to the user to the
ordered item is 1 is to 1 because for every product that have been sold there
is only one person who sold it.
For the customer entity to ordered_item a 1
customer might buy one or more product, and when you look at to the ordered
item a transaction of a user can be only done once meaning if they buy one
product there is only one customer assigned to it (if I buy two candies and the
price of the candy is 6 you can’t say that the half of 6 pesos is from another
customer)
Product for the product to the
ordered_item, the cardinality is 1 to
many a product can be bought by different customer or the same customer several
times, and for every ordered product there is only one corresponding product on
the table product.
Product supplier actually we can just consider makint supplier id as foreign
key to the table product but the essence of separing this is that we are
considering that a supplier might supply multiple product or a product can be
supplied by one or more supplier.
