Database Normalisation
Key concepts
Let me start by naming a few of the most important concepts in relational database design. A basic understanding of these will be required to understand the rest of the article. * Primary Key (PK)
A column with a unique value for each row. Although not all database management systems (DBMS) require you to put a PK into each table, from a design perspective a PK is a requirement. No table should be without one. * Foreign Key (FK)
These define relationships between tables. When you want a row in one table to be linked to a row in another table, you place a FK column in the child table and use the value of the parent row's PK as the value of the FK field. * Composite Key
This is a key that is made up of more than one column. This is typically used when you want to prevent a table from using the same combination of values twice. For example, in a table that lists item prizes for shops, you would only want each shop to have a single price for each item. So, you create a FK for the shop and a FK for the item, and then you create a composite PK out of those two columns. This would cause the DBMS to forcefully restrict entries that would create rows where the combined values of these fields are duplicated. - This type of key is commonly used in N:M relationships. (Explained below... with visual aids.) * One-To-One (1:1) relationship
A relationship between two tables, where a single row in one table is linked to a single row in another table. * +------------+ +----------------+ * | person | | person_contact | * +------------+ +----------------+ * | person_id |1---1| person_id | * | first_name | | email | * | last_name | | phone | * +------------+ +----------------+
This type of relationship is practically non-existent in normalized
Let me start by naming a few of the most important concepts in relational database design. A basic understanding of these will be required to understand the rest of the article. * Primary Key (PK)
A column with a unique value for each row. Although not all database management systems (DBMS) require you to put a PK into each table, from a design perspective a PK is a requirement. No table should be without one. * Foreign Key (FK)
These define relationships between tables. When you want a row in one table to be linked to a row in another table, you place a FK column in the child table and use the value of the parent row's PK as the value of the FK field. * Composite Key
This is a key that is made up of more than one column. This is typically used when you want to prevent a table from using the same combination of values twice. For example, in a table that lists item prizes for shops, you would only want each shop to have a single price for each item. So, you create a FK for the shop and a FK for the item, and then you create a composite PK out of those two columns. This would cause the DBMS to forcefully restrict entries that would create rows where the combined values of these fields are duplicated. - This type of key is commonly used in N:M relationships. (Explained below... with visual aids.) * One-To-One (1:1) relationship
A relationship between two tables, where a single row in one table is linked to a single row in another table. * +------------+ +----------------+ * | person | | person_contact | * +------------+ +----------------+ * | person_id |1---1| person_id | * | first_name | | email | * | last_name | | phone | * +------------+ +----------------+
This type of relationship is practically non-existent in normalized