Database Modelling

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              

SID

Supplier

Suppliers_Contacts

SID	Contact_number

Product Supplier             

PID

SID

Customer

CID

LastName

MiddleName

Age

Birthdate

Street

Barangay

Province

               

Contacts

CID	ContactNumber

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.