Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
FROM GROUP
INNER JOIN USER_GROUP ON GROUP.id=USER_GROUP.fk_group
INNER JOIN USER ON USER_GROUP.fk_user=USER.id
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:

IF ( (SELECT COUNT(*) FROM USER_GROUP WHERE fk_user='MY USER') > 3)

THEN --> DO NOT ALLOW USER TO JOIN ANOTHER GROUP

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: 8348 | Views in the last 30 days: 1
 
Related Articles
ARTICLE

Expanding The Scope of Bridge Tables

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

FORUM

Father Son Relationship

Relationships

BLOG

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...

ARTICLE

DAX Relationships

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

FORUM

Creating a Group on Data

Creating a group on data without a easy relationship

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones