Types of Relationships

Because we try to normalize our tables, databases often end up with many different tables that relate to one another. For instance, in my Halloween 2015 Trick or Treaters Database (here), I had a table that held only the different types of candy I had at my house. The information in this table was related to each individual kid that came to my house (which was stored in another table). To keep the database in normal form, I needed multiple tables even though the information was all related. There are three types of relationships between tables: One-to-One The simplest relationship between tables is one to one. In this case, one item in one table can only be related to one item in the related table and vice versa. For instance, I am building a database for a local gunsmith shop. The owner needs to track the acquisition and disposition of every gun. There is one table for gun information which holds gun specific information such as the gun id (the primary key for the table, this value is auto-incrementing), type, caliber, manufacturer, model, etc. There is also an acquisition table which holds the acquisition date, the name of the person the gun was acquired from, and the gun id of the gun. Each gun id can only be acquired once. If the gun were to be acquired by the owner, sold by the owner, then acquired again, that gun would have a new entry in the gun table along with a new gun id. Therefore, each entry in the gun table can only be related to one entry in the acquisition table and vice versa. This would be noted in a diagram with a single straight line connecting the two tables, as shown below: Another example of a one-to-one relationship is social security numbers. Each person can only have one social security number and each social security number is only assigned to one person. To design this, you must have a primary key for each table (as usual!). Each table will also have a column for the primary key of the other table. For instance, Table 1 will have the columns: table1_id (the primary key), table1 attributes (however many of these columns you need), and finally table2_id. Each entry in Table1 will refer to one corresponding entry in Table2 by that entry’s primary key. One-to-Many The next type is one-to-many. In the gunsmith database, there is also a table for manufacturers. It has a one-to-many relationship with the gun table because each gun can only have one manufacturer but each manufacturer can have more than one gun. This is called a one-to-many relationship. It would be noted in a diagram with one branch touching the table that can only have a single entry and several branches touching the table that can have many. Table 1 in this case would be manufacturers and Table 2 would be guns. Another example of a one-to-many relationship is biological parenthood. One man may have many children, but every child only has one biological father. The table design to this is the same as in one-to-many with only one distinction. Many rows in Table2 may point back to a single row (primary key) in Table 1. Many-to-Many Many-to-many is easy to understand conceptually but is the most difficult to design. Imagine for Halloween I allowed kids who dressed up to take more than one full sized candy bar. In my Trick or Treaters database, there would be a many-to-many relationship between kids and candy because each kid could have many candies and each candy could have many kids. Conceptually, this would be shown in a diagram like this: However, you cannot build this into a normalized table. Remember, to be normalized each cell must only contain one piece of information. If there is a column in the kid database for candy, it would have to hold many different candy ids! To get around this we must build a junction table, which I like to think of as a middle man table. It would be shown like this: I typically name the middle man table table1table2 (with the real table names, of course). This table will have three columns: the table1table2 primary key column (which will be my standard not null, auto-incrementing integer), table1 primary key, and table2 primary key. There will be a column for table1table2 primary keys in both table1 and table2. This will allow you to later look in the middle man table to see the primary keys that correspond with entries in either Table1 or Table2. It took me a little while to wrap my head around many-to-many relationships but they are very simply two one-to-many relationships. When thought of in this way, they are much easier to tackle.