SQL Table Design
Remarks[edit | edit source]
The Open University (1999) Relational Database Systems: Block 2 Relational Theory, Milton Keynes, The Open University.
Properties of a well designed table.[edit | edit source]
A true relational database must go beyond throwing data into a few tables and writing some SQL statements to pull that data out.
At best a badly designed table structure will slow the execution of queries and could make it impossible for the database to function as intended.
A database table should not be considered as just another table; it has to follow a set of rules to be considered truly relational. Academically it is referred to as a 'relation' to make the distinction.
The five rules of a relational table are:
- Each value is atomic; the value in each field in each row must be a single value.
- Each field contains values that are of the same data type.
- Each field heading has a unique name.
- Each row in the table must have at least one value that makes it unique amongst the other records in the table.
- The order of the rows and columns has no significance.
A table conforming to the five rules:
- Rule 1: Each value is atomic.
Manageronly contain a single value.
- Rule 2:
Idcontains only integers,
Namecontains text (we could add that it's text of four characters or less),
DOBcontains dates of a valid type and
Managercontains integers (we could add that corresponds to a Primary Key field in a managers table).
- Rule 3:
Managerare unique heading names within the table.
- Rule 4: The inclusion of the
Idfield ensures that each record is distinct from any other record within the table.
A badly designed table:
|3||Sue||Friday the 18th July 1975||2, 1|
- Rule 1: The second name field contains two values - 2 and 1.
- Rule 2: The DOB field contains dates and text.
- Rule 3: There's two fields called 'name'.
- Rule 4: The first and second record are exactly the same.
- Rule 5: This rule isn't broken.