A Simple MySQL Relational Database

By April 24, 2009Technical

I’ve searched far a wide and all I wanted is a MySQL database schema that incorporated all the common relationships that tie into a relational database i.e one to one, one to many and many to many but haven’t had much luck.

So here you go a straight to the point post that will give you:

  • a fully functional MySQL databse schema along with inserted dummy data
  • a relational database with common relationships such as one to one, one to many and many to many
  • a list of example select queries to use to see how you can create table joins between tables

To download the database please click here

Diagram Of The Database
The following diagram will help give you a good visual of all the tables in the database and what relationships are in place:

dog-er diagram

Select Queries
So you have got all your information in different tables (relational database) and you need to perform table joins. Below are a list of the most common types of table joins using the different relationship types one-to-one, one-to-many and many-to-many:

One-To-One

SELECT dog.id, dog.name, rfid_dog.bar_code AS rfid
FROM dog,rfid_dog
WHERE dog.id = rfid_dog.dog_id
ORDER BY dog.name ASC;

One-To-Many

SELECT dog.id,dog.name,breed.name AS breed
FROM dog,breed
WHERE dog.breed_id = breed.id
ORDER BY dog.name ASC;

Many-To-Many

SELECT breeder.name,breeder.address,breeder.phone,breeder.email,breed.name AS breedName
FROM breed,breeder,breed__breeder
WHERE breed__breeder.breed_id = breed.id
AND breed__breeder.breeder_id = breeder.id
ORDER BY breeder.name ASC;

And just like that you now have your head around relational databases!

If you have come to this point and your still excited and want to learn more please see our wiki article which goes into more detail about relational databases plus talks about optimisation techniques to speed things up.