There is no need to read this unless you want to know more about database design.  If you can do what was on the last page that is enough for Web development.

One-to-many relationships

The relationships on the last page were one to many relationships.  One doctor holds many appointments.  Many real world situations have this type of relationship and they are easy for computers to use.  The database management system can look in the first table for a doctor.  Then it can collect all the records in the second table which also have the same doctor id.  The one doctor record and the many appointments they have are connected (or related) by having the same doctor id.

The first table in a one to many relationship is known as the parent table and the related table is the child table.  In the parent table the doctor is mentioned once.  In the child table the doctor is mentioned many times.  One parent and many children.

Note that in computing many is taken to include 0 or 1 as well as actually many.  So one doctor (a new one) can start out with no appointments, then one and then many.


Continuing with the example from above you might expect to see a one-to-many 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 at least). The link between them would be many-to-many. Many to many relationships are not good for computers as they are hard to handle programmatically.

When you see a many-to-many relationship you need to deal with it by splitting it into two one-to-many relationships. In this example you use the appointments table between doctors and patients.  One doctor will have many appointments and one patient may have many appointments.  The appointments table mentions both the single doctor and the single patient.

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).


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.  A table is also called an entity because it is about one type of thing (e.g. doctors).  It is impossible to think of a thing/entity which has one and only one other thing related to it.  Remembering that a doctor with just one patient is not an example of this because they might one day have another patient and so the relationship is one-to-many just that many currently means one!

However there are reasons you might not want to put all of the data into one table.

Reducing complexity

There may be so many fields that it is just easier to cope with if you split the table to get two easier-to-handle tables.  DBMSs can even have complexity limits (e.g. the number of fields or the total size of data for a record) which can be beaten by splitting the structure.

Varied performance needs

You may access some information a lot and other information hardly ever so put a small amount in a frequently accessed table and the bulk of it in a less used table (maybe even on a slower server somewhere).  You only log on to most systems once but other information might be needed a lot more (account details or game characteristics).


You might want some information to be freely available and other information (about the same thing) to be secure and much harder to access (the second table could be limited to access by only a few users).  You might be happy to display names but not dates of birth or gender.  You can keep things private within a table but putting them in two places makes it easier to avoid accidentally showing private data.


You might hold data about something (for example products) which are all that type of thing but differ a bit.  A shop may want to hold different information about different products but they are all products.  An example could be Amazon who sell both books (which have ISBNs) and DVDs (which do not).

The first table could hold information all products have (productID, name, price, weight etc.) and each type of product could have a different sort of table to hold the individual information (ISBN).  You have a product table and book, DVD, food, tool and so on tables which only hold the extras.  Every product will have one record in the product table and one matching record in a specialist table.  Each book in the product table will still only ever have one ISBN in the specialist table.