A database will commonly have more than one table. For example, a doctors surgery might want to keep records on both doctors and patients. There is often a connection between two tables of data in the database. Often this will be formalised as a relationship. To extend the example slightly these tables might be needed by the surgery:
- doctors
- patients
- appointments
- prescriptions
In database terms these are known as entities but tables is an easier word.
There will be connections between a few of these. Doctors will be connected to appointments. So will patients. It might help to visualise these relationships like this:
Here the relationship is between doctors and the appointments they have. Note that the relationship is drawn from the primary key in the doctors table (DoctorID is marked by a key symbol as it is the primary key). It then goes to the other table where there is another DoctorID field. The relationship works because when both DoctorIDs are the same the records are connected.
The relationship is shown as a connecting line and each end of the line is different. This is because this is a one-to-many relationship. One doctor can have many appointments (or none technically). There are various ways of showing this, this is one.
The one side is the primary key of what is known as the parent table. The many side of the relationship is an identical field known as the foreign key. The foreign key is in the "child" table.
Not all databases are relational databases. Not all relationships are one-to-many. However, this is preparation for the PHP/MySQL tutorial pages and MySQL is a relational database management system (RDBMS) and one-to-many relationships are all you will use.
Other types of relationship
There are two other possibilities which you can read about if you are interested. If not then go to the next page but if you ever find yourself tempted by a many-to-many relationship read this bit first.
Many-to-many
Continuing with the example from above you might expect to see a relationship between doctors and patients. However, although any doctor may have many patients on their list each patient may also have a number of doctors (over time). The link between them would be many-to-many. When you see a many-to-many relationship you need to deal with it by splitting it into two one-to-many relationships. The appointments and prescriptions tables do this. They stand between doctors and patients and make many-to-many relationships into a pair of one to many:
- one doctor will have many appointments and one patient may have many appointments
- one doctor writes many prescriptions and one patient may receive many prescriptions
Handling the idea that one doctor will be the registered GP for a number of patients is harder. The split will probably seem artificial but it allows relational databases to work. The relationships are:
Interestingly many people resist correcting this sort of problem but when you do the database often works much better as well. In this example the database can now handle people moving between doctors but still keeping a record of which doctors was responsible at what time.
Another example of many-to-many: an author might write many books and any one book may have many authors but by adding and extra table ("works" or "contributions" maybe) can link them using one-to-many (one author makes many contributions, one book may have many contributors).
One-to-one
On the surface these are not needed. Any time you have two tables and a one-to-one relationship the data could just go into one table. However there are reasons you might not want to put all of the data into one table:
- there may be so many fields that it is just easier to cope with if you split the table
- you may access some information a lot and other information hardly ever
- you might want some information to be available and other information (about the same thing) to be secure
- You might hold data about (for example) products but different products may have different other information held such as books having ISBNs while DVDs do not - the type-specific information can go into separate tables especially where that extra data is quite large and lots of empty fields would take up too much space.
Why relate?
It is much simpler not to relate tables. Instead you can add and access data as needed by getting matching data at the time you need it. However, this ignores the need for referential integrity.
Using the example above if a user tries to create an appointment for a doctor who does not exist it will not be allowed. If the tables were not related then the user could create appointments for imaginary doctors!
