Nomarlization

Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.

Beside that, Normal forms are numbered from one (the lowest form of normalization, referred to as first normal form) to five (fifth normal form).

In practical analysis and my project as ecommerce website, I have done my database design in 4 steps:

1st step: Unnormalization Form:

I gathered all data that I have :

pd_id, pd_name, pd_description, pd_image, pd_thumbnail, pd_price, pd_qty, cat_id, pd_date, pd_last_update,
user_id, user_name , user_password, user_regdate, user_last_login, sc_name, sc_address, sc_phone sc_email, sc_shipping_cost, sc_currency, sc_order_email, od_id, od_qty, od_date, od_last_update, od_status od_memo, od_shipping_first_name, od_shipping_last_name, od_shipping_address1, od_shipping_address2, od_shipping_phone, od_shipping_city, od_shipping_state, od_shipping_postal_code, od_shipping_cost, od_payment_first_name, od_payment_last_name, od_payment_address1, od_payment_address2, od_payment_phone, od_payment_city, od_payment_state, od_payment_postal_code, cy_id, cy_code, cy_symbol,
cat_parent_id, cat_name, cat_description, cat_image, ct_id, ct_qty, ct_session_id, ct_date.

2nd Step: First Normal Form (1NF) sets the very basic rules for an organized database:

* Eliminate duplicative columns from the same table.
* Create separate tables for each group of related data and identify each row with a unique column (the primary key).

od_id, od_qty, od_date, od_last_update, od_status od_memo, od_shipping_first_name, od_shipping_last_name, od_shipping_address1, od_shipping_address2, od_shipping_phone, od_shipping_city, od_shipping_state, od_shipping_postal_code, od_shipping_cost, od_payment_first_name, od_payment_last_name, od_payment_address1, od_payment_address2, od_payment_phone, od_payment_city, od_payment_state, od_payment_postal_code.

pd_id, pd_name, pd_description, pd_image, pd_thumbnail, pd_price, pd_qty, cat_id, pd_date, pd_last_update, cy_id, cy_code, cy_symbol,
cat_parent_id, cat_name, cat_description, cat_image, ct_id, ct_qty, ct_session_id, ct_date, user_id, user_name , user_password, user_regdate, user_last_login, sc_name, sc_address, sc_phone sc_email, sc_shipping_cost, sc_currency, sc_order_email.

3rd Step: Second Normal Form has some methods:

* Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
* Create relationships between these new tables and their predecessors through the use of foreign keys.

These methods mean the 2NF attempts to reduce the amount of redundant data in a table by extracting it, placing it in new table(s) and creating relationships between those tables.

od_id, od_date, od_last_update, od_status od_memo, od_shipping_first_name, od_shipping_last_name, od_shipping_address1, od_shipping_address2, od_shipping_phone, od_shipping_city, od_shipping_state, od_shipping_postal_code, od_shipping_cost, od_payment_first_name, od_payment_last_name, od_payment_address1, od_payment_address2, od_payment_phone, od_payment_city, od_payment_state, od_payment_postal_code.

od_id, pd_id, od_qty.

pd_id , pd_name, pd_description, pd_image, pd_thumbnail, pd_price, pd_qty, cat_id, pd_date, pd_last_update, cy_id, cy_code, cy_symbol,
cat_parent_id, cat_name, cat_description, cat_image, ct_id, ct_qty, ct_session_id, ct_date, user_id, user_name , user_password, user_regdate, user_last_login, sc_name, sc_address, sc_phone sc_email, sc_shipping_cost, sc_currency, sc_order_email.

Final Step: 3rd Normal Form has its own methods to analysis:
* Already meet the requirements of both 1NF and 2NF
* Remove columns that are not fully dependent upon the primary key.

od_id, od_date, od_last_update, od_status od_memo, od_shipping_first_name, od_shipping_last_name, od_shipping_address1, od_shipping_address2, od_shipping_phone, od_shipping_city, od_shipping_state, od_shipping_postal_code, od_shipping_cost, od_payment_first_name, od_payment_last_name, od_payment_address1, od_payment_address2, od_payment_phone, od_payment_city, od_payment_state, od_payment_postal_code.

od_id, pd_id, od_qty.

pd_id , pd_name, pd_description, pd_image, pd_thumbnail, pd_price, pd_qty, cat_id, pd_date, pd_last_update.

cy_id, cy_code, cy_symbol.

cat_id , cat_parent_id, cat_name, cat_description, cat_image.

ct_id, pd_id, ct_qty, ct_session_id, ct_date,

user_id, user_name , user_password, user_regdate, user_last_login.

customer_id, customer _name , customer _password, customer _regdate, customer _last_login.

sc_name, sc_address, sc_phone, sc_email, sc_shipping_cost, sc_currency, sc_order_email.