Database normalization for electricity monitoring system

Question

I've read a lot of tips and tutorials about normalization but I still find it hard to understand how and when we need normalization. So right now I need to know if this database design for an electricity monitoring system needs to be normalized or not.

So far I have one table with fields:

• monitor_id
• appliance_name
• brand
• ampere
• uptime
• power_kWh
• price_kWh
• status (ON/OFF)

This monitoring system monitors multiple appliances (TV, Fridge, washing machine) separately. So does it need to be normalized further? If so, how?

Answer 1

Honestly, you can get away without normalizing every database. Normalization is good if the database is going to be a project that affects many people or if there are performance issues and the database does OLTP. Database normalization in many ways boils down to having larger numbers of tables themselves with fewer columns. Denormalization involves having fewer tables with larger numbers of columns.

I've never seen a real database with only one table, but that's ok. Some people denormalize their database for reporting purposes. So it isn't always necessary to normalize a database.

How do you normalize it? You need to have a primary key (on a column that is unique or a combination of two or more columns that are unique in their combined form). You would need to create another table and have a foreign key relationship. A foreign key relationship is a pair of columns that exist in two or more tables. These columns need to share the same data type. These act as a map from one table to another. The tables are usually separated by real-world purpose.

For example, you could have a table with status, uptime and monitor_id. This would have a foreign key relationship to the monitor_id between the two tables. Your original table could then drop the uptime and status columns. You could have a third table with Brands, Models and the things that all models have in common (e.g., power_kWh, ampere, etc.). There could be a foreign key relationship to the first table based on model. Then the brand column could be eliminated (via the DDL command DROP) from the first table as this third table will have it relating from the model name.

To create new tables, you'll need to invoke a DDL command CREATE TABLE newTable with a foreign key on the column that will in effect be shared by the new table and the original table. With foreign key constraints, the new tables will share a column. The tables will have less information in them (fewer columns) when they are highly normalized. But there will be more tables to accommodate and store all the data. This way you can update one table and not put a lock on all the other columns in a denormalized database with one big table.

Once new tables have the data in the column or columns from the original table, you can drop those columns from the original table (except for the foreign key column). To drop columns, you need to invoke DDL commands (ALTER TABLE originalTable, drop brand).

In many ways, performance will be improved if you try to do many reads and writes (commit many transactions) on a database table in a normalized database. If you use the table as a report, and want to present all the data as it is in the table normally, normalized the database will hurt the peformance.

By the way, normalizing the database can prevent redundant data. This can make the database consume less storage space and use less memory.

Answer 2

It is nice to have our database normalize.It helps us to have a efficient data because we can prevent redundancy here and also saves memory usages. On normalizing tables we need to have a primary key in each table and use this to connect to another table and when the primary key (unique in each table) is on another table it is called the foreign key (use to connect to another table).

Sample you already have this table :

Table name : appliances_tbl
-inside here you have

-appliance_id : as the primary key
-appliance_name
-brand
-model
and so on about this appliances...

Next you have another table :

Table name : appliance_info_tbl (anything for a table name and must be related to its fields)

-appliance_info_id : primary key
-appliance_price
-appliance_uptime
-appliance_description
-appliance_id  : foreign key (so you can get the name of the appliance by using only its id)
and so on....

You can add more table like that but just make sure that you have a primary key in each table. You can also put the cardinality to make your normalizing more understandable.