Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Decoupling in Relational Databases

By Timothy Claason,

Before I understood relational databases, the tables I put in my databases, and the resulting architectural output was bad. And sloppy. I would put columns into tables that had 1-to-1 relationships with their foreign key counterpart, and I would duplicate those columns, to generate the effect of a bridge table.

For instance, if I had a database where I had a table called USER, and another table called GROUP, and the relationship between USER and GROUP was such that a USER could belong to 1 or more groups, I may have created the USER table like the one below

Column Name Type
id int (auto increment)
firstname varchar(50)
lastname varchar(50)
fk_group1 int (relates to GROUP table)
fk_group2 int (relates to GROUP table)
fk_group3 int (relates to GROUP table)

As you can see in the above table, I may have made a bunch of columns that have a foreign key relationship with the GROUP table. That's all fine and dandy, except in the event that a USER belongs to 1000 GROUPS. Every time a USER exceeded the number of groups available in the USER table, I would have to go back and add more columns.

The other implication of not understanding some basic tenets of relational databases is that the application code to represent the relationship between USER and GROUP was just as sloppy (or even worse!) than the database design.

It was not until I started exploring concepts in decoupling that I realized the error of my ways. One of the best ways to implement decoupling in relational databases is with bridge tables. A bridge table is a table whose relationship with other tables creates a Many-to-Many relationship. In a many-to-many relationship paradigm, the two tables are decoupled, because they don't have a direct relationship with one another.

For instance, to implement a bridge table that acts as a go-between for USER and GROUP, I would modify the USER table to look as follows:

Column Name Type
id int (auto increment)
firstname varchar(50)
lastname varchar(50)

And, just for completeness sake, I'll show what the GROUP table looks like

Column Name Type
id int (auto increment)
groupname varchar(50)

The bridge table to allow for as many user memberships as I wanted, I would create a table called USER_GROUP (you can name bridge tables whatever you want, I just like to have the name format be something like TABLEa_TABLEb).

The USER_GROUP table would look as follows:

Column Name Type
id int (auto increment)
fk_user int (foreign key to USER)
fk_group int (foreign key to GROUP)

Then, if I wanted to build a SQL query to show all groups, and their associated users, it would look something like this:

SELECT GROUP.groupname, USER.firstname, USER.lastname
ORDER BY GROUP.groupname, USER.lastname, USER.firstname

As you can see, this relationship between USER, USER_GROUP, and GROUP facilitates a solid relationship, because it not only allows for an infinite number of groups per user, but it also easily allows, at the business logic layer, to prevent a user from becoming a member of too many groups. For instance:



This decoupling also makes it easier to write code for, as well, because each of the concepts (USER and GROUP) can exist independently of one another, yet the logical relationship still exists (GROUP "has a" user). In the composition relationship between GROUP and USER, I can demonstrate in a number of ways with various code constructs, regardless of what programming language I am using.

Total article views: 8360 | Views in the last 30 days: 1
Related Articles

Expanding The Scope of Bridge Tables

Timothy Claason continues talking about database design in a new article. This one talks about expan...


Father Son Relationship



Handling Large Many to Many bridge tables

In some scenarios you will need to create a many to many relationship in your cube in SSAS. One of...


DAX Relationships

Learn how relationships work in Powerpivot workbooks and how you can build them using the DAX langua...


Creating a Group on Data

Creating a group on data without a easy relationship