My struggles in understanding and learning about Object Oriented design, and the tools and knowledge I've taken from them.

Tuesday, September 1, 2009

Decoupling in Relational Databases

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

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:

id - int (auto increment)
firstname - varchar(50)
lastname - varchar(50)

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

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:

id - int (auto increment)
fk_user - int (relationship with USER)
fk_group - int (relationship with 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

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 that with either a LIST variable, or by implementing an IEnumerable interface.


Search This Blog

Powered by Blogger.