This is the fifth in a series of blog posts on SQL, the first covered creating a database, the second selecting information from a database, the third commands to modify the structure and contents of an existing database, the fourth on advanced selection. This post covers database design, as such it is a little lighter on the code examples. No claim of authority is made for these posts, they are mainly intended as my notes on the topic. These notes are based largely on Head First SQL.
The goal of database design is to produce a database which is straightforward and efficient to search. This is done by splitting data into a set of tables, with lookups between those tables used to build the desired output results.
Efficient database design is normally discussed with reference to “normal forms“, the goal being to reach the highest order normal form. In practice, pragmatism is applied which means it may be sensible to hold back a little on this.
PRIMARY KEY (sid)
For a composite key, this form is used:
PRIMARY KEY (column_1,column_2,column_3)
In a normalised relation a non-key field must provide a fact about the key, the whole key and nothing but the key.
Relationships between tables in a database are indicated like this:
PRIMARY KEY (order_id),
FOREIGN KEY (customer_sid) REFERENCES customer(sid)
(Example borrowed from here). PRIMARY KEY and FOREIGN KEY are examples of ‘constraints’, primary keys must be unique and a foreign key value cannot be used in a table if it does not exist as a primary key in the referenced table. The CONSTRAINT keyword is used to give a name to a constraint (a constraint being one of NOT NULL, UNIQUE, CHECK, Primary Key, Foreign Key). CHECK is not supported in MySQL.
Keywords: PRIMARY KEY, CONSTRAINT, FOREIGN KEY, REFERENCES, CONSTRAINT