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:
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:
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;
SELECT dog.id,dog.name,breed.name AS breed FROM dog,breed WHERE dog.breed_id = breed.id ORDER BY dog.name ASC;
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.