Database Normalisation

Only available on StudyMode
  • Download(s) : 15
  • Published : March 29, 2013
Open Document
Text Preview
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 relational designs. They exist mostly to get around limitations in databases like Access, where the number of column was limited, thus creating the need to split tables up. They are also sometimes used to optimize the performance of the database. * One-To-Many (1:N) relationship

A relationship between two tables, where multiple rows in a child table can be linked to a single row in a parent table. For example: * +------------+ +------------+
* | person | | country |
* +------------+ +------------+
* | person_id | |-1| country_id |
* | name | | | name |
* | country_id |*-| +------------+
* +------------+
This is in fact the only "real" type of relationship in a relational database. (See the next point for the reasoning behind that assertion.) * Many-To-Many (N:M) relationship
A relationship between two tables, where multiple rows in one table can be linked to multiple rows in another table. This type is "artificial" in a a way, because this kind of relationship can not be created directly between tables. To accomplish this type of relationship you need to create a third table; an intermediary table that contains FKs to both parents, linked via a set of 1:N relationships. * +-----------+ +--------------+ +--------------+ * | shop | | prices | | product | * +-----------+ +--------------+ +--------------+ * | shop_id |1-| | product_id |*---1| product_id | * | shop_name | |-*| shop_id | | product_name | * +-----------+ | price | +--------------+ * +--------------+

Normalization
To help us properly design our tables we have a set of guidelines which, if followed...
tracking img