The pros and cons of standardizing data in 3NF form

The main purpose of normalization is to reduce data surpluses and avoid inconsistency data. Adaptation leads to the separation of unauthorized entities in separate entities. In fact, normalization leads to a pure database design. Because we do not store unnecessary data, we save storage space and save resources to maintain (update, delete) unnecessary data.

But there are instances where we do not fully compute the data. The example in question is a great example to explain why we allow shared data. Looking at customer profile format data, it is desirable to design a city, state, country, and zip code as separate entities where they could be represented by their own characteristics (state_id, country_id, zip_id) and that more customers may belong to the same country, state, city And zip. Suppose we designed this as a separate entity and try to retrieve data on the following issues –

"Create a report of all customers belonging to" US "and living in" TAMPA "city & # 39; city & # 39; 39; FLORIDA & # 39; 33601 & # 39;

The query would be something like –

"SELECT c.customer_first_name, c.customer_middle_name, c.customer_last_name NEW customer c, Customer_address ca, address_city act, address_zip az, Address_state as, address_country ac WHERE c.customer_id = approxcustomer_id and c.city_id = act.city_id and c.zip_id = az.zip_id and c.state_id = as.state_id and c.country_id = Ac.country_id and ac.country_name = & # 39; US & # 39; And as.state_name = & # 39; FLORIDA & # 39; And act.city_name = & # 39; TAMPA & # 39; And az.zip_code = & # 39; 33601 & # 39; "Check the link (c.customer_id = ca.customer_id and c.city_id = act.city_id etc) In SQL to retrieve the required information. SQLs connections are considered very expensive when there is a large amount of data, say we have more data Within the customer table. The four additional components must be very expensive and will lead to unacceptable response time.

If we divide data and allow country, state, city and zip data to reside within the customer's table, we could transcribe The above query as –

"SELECT c.customer_first_name, c.customer_middle_name, c.customer_last_name FROM customer c, customer_address about WHERE c.customer_id = approxcustomer_id AND approxcountry_name = & # 39; US & # 39; AND approx .state_name = & # 39; FLORIDA & # 39; And approxcity_name = & # 39; TAMPA & # 39; And ca.zip_code = & # 39; 33601 & # 39; "

After de-normalization, the query would run much faster. So, in fact, normalizing data in 3NF format is not always practical.

Source by Deepesh Joseph

Leave a Reply

Your email address will not be published. Required fields are marked *