Database Table Relationships

The first type of relationship we will discuss is the one-to-one relationship. A one-to-one relationship is where an entity from within one table references one and only one entity within another table. For instance, within most offices you will find users that have laptops. This relation is typically found as a one-to-one relation as follows, one user per one laptop. Users do not typically share laptops like they may with standard desktops. The users would be within one table and the laptops would be found in another table if they were represented within a relational database. This relation can be seen as follows:

 

Users (User_ID, First_Name, Last_Name, Phone, Service_Tag)

Foreign key: Service_Tag to Laptops table

Laptops (Service_Tag, Brand, Model, CPU, Memory)

 

The second type of relationship you will find within a referential database is a one-to-many relationship. A one-to-many/many-to-one is all a matter of perspective. With a one-to-many relationship there is one entity within one table that relates to many entities within another table. My example here is something that you will commonly find within a small office, 5-10 users. Typically a small office will only have 1 networked printer and many users will use that printer. This relationship can be shown in the following diagram.

 

Users (User_ID, First_Name, Last_Name, Phone, PrinterID)

Foreign key: PrinterID to Printers table

Printers (PrinterID, Brand, Model, Location)

The last relationship I will show is the many-to-many relationship. With a many-to-many relationship you will find many entities within one table that relate to many entities in another table. To do this properly you need what is called a intersection table, junction table or a link table. I personally use the term link table. This link table is used to split up the many-to-many relation and will create two one-to-many relationships this greatly simplifies the logic within the database and can help prevent issues that may come up latter down the road. This helps enforce referential integrity. Let’s take the previous example with the small office and the single printer and many users and expand this to a larger office with many printers. For instance, with an office of 100 users there is likely going to be at least 1 printer for every 20 or 25 users. In addition to this each user may print to more that or printer depending on what they want to print, some printer may be color while others only black and white. This example can be show as such:

 

Users (User_ID, First_Name, Last_Name, Phone)

PrinterUserLink (User_ID, PrinterID)

Foreign key: User_ID to Users table

Foreign key: PrinterID to Printers table

Printers (PrinterID, Brand, Model, Location)

 

References:

Adamski, J., & Finnegan, K. (2008). Microsoft Office Access 2007. Boston: Course Technology.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s