Entity relationship diagrams (ERD). Normalization.
An Entity Relationship Diagram (ERD) is a graphical illustration of how “entities” relate to each other within a system. It shows the relationships between tables in database.
Database Normalization - Store only the minimal amount of information ('atomic' data - data item that cannot be broken down any further)
The table database is not normalized if
it has data redundancy;
it consumes additional memory space;
it makes it difficult to process and update the database without losing data.
First Normal Form (1NF):
all data must be atomic, i.e. each data element cannot be broken down further;
all attributes in the table must have unique names and consist of different characteristics;
a table doesn't contain duplicate records;
a table has a primary key.
Second Normal Form(2NF):
It should be in the First Normal form.
Create separate tables for sets of values that apply to multiple records.
Relate the tables with a foreign key.
Non-key attributes must depend on every part of the primary key (it has Transitive Dependency)
Third Normal Form(3NF):
It should be in the Second Normal form.
Eliminate fields that do not depend on the primary key (it doesn't have Transitive Dependency).
Each non-primary key attribute must be dependent only on a primary key
Benefits of normalization
The database does not have redundant data, it is smaller in size so less money needs to be spent on storage
Because there is less data to search through, it is much faster to run a query on the data
Because there is no data duplication there is better data integrity and less risk of mistakes.
Because there is no data duplication there is less chance of storing two or more different copies of the data
Once change can be made which can instantly be cascaded across any related records.