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.
