Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««678910»»»

Decoupling in Relational Databases Expand / Collapse
Author
Message
Posted Wednesday, March 3, 2010 4:08 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 17, 2014 7:45 PM
Points: 338, Visits: 1,420
Tim,
Your article is essentially completely correct. I like your honest style, admitting how you used to design database tables before you found out about adding this extra type of table. This is the discussion thread for the article so expect all the faults to be found, the only criticism seems to be that the article title was slightly misleading for problem the article actually solved. A lot of other SQLServerCentral articles are completely correct and generate no responses in the discussion thread, your's raised a lot of discussion and I even learned something from it about all the different names there are for this type of table. This is also one of the most important concepts to understand about database design and it seems like a lot of people who didn't know about this method before you wrote the article know now. So it was a very useful article.
Post #876425
Posted Wednesday, March 3, 2010 5:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 18, 2013 8:56 AM
Points: 22, Visits: 140
Thanks for your kind words...hopefully part 2 (scheduled for March 23) is less offensive
Post #876466
Posted Wednesday, March 3, 2010 8:46 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 24, 2012 5:33 AM
Points: 15, Visits: 120
Well said and to add to the name menagerie, I have always called them Join Tables since they join one entity to another.

For those who think this is "bad design", the world is filled with many-to-many relationships that need to be represented in the architecture of many databases. I'm with you on this point.

As for the "n00b"ness of the article, I have no problem with that since I would think n00bs would turn to a publication such as this for part of their learning experience. I do think the title was an unfortunate choice, and save the redundant key, it was right on target for someone trying to learn the fundamentals of good design.
Post #876504
Posted Thursday, March 4, 2010 12:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 36,711, Visits: 31,159
Karen Lopez - InfoAdvisors (3/2/2010)
All this discussion about what to call these tables points out that we as a profession do a lousy job of defining our profession .

These tables can be referred to as:

- Associative tables
- Link Tables
- Bridge Tables
- M:N or many-to-many Tables
- Resolution Tables
- Intersectional Tables
- Relationship Tables


...depending on whom you read, when you read it, or what tools you use. Oddly enough, I'm getting lots of push back from developers these days that having such tables is "bad design". I'm trying to track down a source for this "knowledge". What devs are telling me is that the real world does not have many-to-many relationships and that these are just artifacts of a designer trying to push relational theory onto a non-relational world. I don't see that

What I enjoyed about this article is the "confession" part that showed how a bad design can get one into trouble. Articles should have such anti-patterns to show that there's a reason why something is a better solution.



Agreed... I've also heard them being referred to as "Mapping" tables, "Dual ID" tables, and a couple of names not fit for print.

I don't agree that there's no such thing as a many-to-many relationship in the real world, though.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #876609
Posted Thursday, March 4, 2010 4:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:25 PM
Points: 5,162, Visits: 2,766
Tim,

I certainly hope that none of my comments caused offence. I always intend to improve my own knowledge and try to achieve that through reading followed by intelligent debate. The internet has both improved this and been a retrograde step.

I now can discuss with the author and a group of people of differing experience, skill sets and backgrounds about the given topic.

Unfortunately, with the terseness of forums, the immediacy and permenancy of comments together with the fact that some of the commenters are writing in English which is not their first language (take the Americans for example - oh come on, of course I am joking!!!), people tend to rush in with criticisms as they get exasperated when they select something that takes up their precious time but is not applicable to them.

You might have noticed that the comments which appeared to be more vitriolic in content, as opposed to discussing the content from a theoretical and practical viewpoint, all seemed to be posted by 'Forum Newbie's. Although I have been coming to this website for many years (the joining date on my profile lies - don't ask), I have only been really active in the last twelve months yet over all that time the discussions often get heated but rarely to vicious.

To sum up: Keep posting.


Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
Post #876699
Posted Thursday, March 4, 2010 6:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 36,711, Visits: 31,159
I agree with Gary's comments (and some of the others), as well. If this were the script to one of those 3 to 5 minute little movies that show people how to do something, people would be raving about how rapidly the verbal script got to the point. There are no spelling errors, it starts out by identifying a very common problem and the very well recognized solution, and is generally well written and easy to understand. This is an excellent introduction into "decoupling" and "bridge" tables (whatever you want to call either of those today ) and I'm really surprised that more folks can't see the value of such simple and to-the-point articles especially as primers to newbies.

I'll also state that one of the purposes for an article is to give people the chance to "cus'n'discuss" about such things so don't ever let that put you off.

Well done, Tim. Keep up the good work and hope to see another primer such as this from you in the near future.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #876777
Posted Thursday, March 4, 2010 9:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 21, 2013 1:51 PM
Points: 6, Visits: 33
I had never heard of a 'bridge' table in my 30 years of relational database design and modeling.

So I was surprised to see that the author, obviously a newcomer to relational databases, had not yet learned about normalization (illustrated painfully in his design with repeating groups), nor about intersection tables to resolve many-to-many table relationships.

I do agree with one of the responders to this article that there's a "teachable moment" here, when he pointed out that the intersection table (aka 'bridge' table) should have a composite primary key of the two fk_keys, instead of a single surrogate key pk.

Actually I have to throw a criticism here to our good friends at SqlServerCentral.com for allowing this article to be posted. I'm guessing the 90% of the readers of these articles are seasoned practitioners, but even the other 10% need to know the accepted tenets and nomenclature of relational databases. Quality control in order here?

Marvin
Post #876922
Posted Thursday, March 4, 2010 9:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:25 PM
Points: 5,162, Visits: 2,766
marvin.elder (3/4/2010)
Actually I have to throw a criticism here to our good friends at SqlServerCentral.com for allowing this article to be posted. I'm guessing the 90% of the readers of these articles are seasoned practitioners, but even the other 10% need to know the accepted tenets and nomenclature of relational databases. Quality control in order here?


I'd have to disagree with Marvin here. All articles should be read in context of both their rating and linked discussion. You would NEVER get a article like this from a DBA. As a developer, I think Tim gave it the perspective akin to the one I wish some DBAs would give developers regading Data Access Layers. It is because these two pieces are often looked at in isolation that I have seen so many horrendous implementations.

No to censorship. The freedom of this site gives it its valued diversity.

FYI I have no connection to SSC (beyond user/membership), Red Gate and I have never played rugby with Steve (Union I am assuming young man!!!).


Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
Post #876943
Posted Thursday, March 4, 2010 10:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, May 3, 2014 1:19 PM
Points: 1,035, Visits: 409
I might agree with Marvin's sentiment IF there were an accepted nomenclature for the concept addressed in the article. Yes, at a high level, it's just normalization. But when discussing the specifics of many to many relationships, there is no generally agreed upon name for them. "Decoupling" is maybe a bit unconventional, but actually makes quite a bit of sense; after all the process of normalization is really that of decoupling-or removing-functional dependencies in the data...

Besides, as useful as SSC is, and has been over the years, anyone who has frequented it for any amount of time should realize that most of the articles are written by members of the community and as such, should not be considered "authoritative" which is not to say that they can't be useful or even quantifiably correct. Steve has always had more of an open door policy to contributions, which means that just about any lunatic can get published...(speaking primarily of myself who used to be a fairly active author on SSC)



/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek



*****************/
Post #877002
Posted Thursday, March 4, 2010 10:53 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, September 5, 2013 2:13 PM
Points: 829, Visits: 4,099
marvin.elder (3/4/2010)
I had never heard of a 'bridge' table in my 30 years of relational database design and modeling.

So I was surprised to see that the author, obviously a newcomer to relational databases, had not yet learned about normalization (illustrated painfully in his design with repeating groups), nor about intersection tables to resolve many-to-many table relationships.

I do agree with one of the responders to this article that there's a "teachable moment" here, when he pointed out that the intersection table (aka 'bridge' table) should have a composite primary key of the two fk_keys, instead of a single surrogate key pk.

Actually I have to throw a criticism here to our good friends at SqlServerCentral.com for allowing this article to be posted. I'm guessing the 90% of the readers of these articles are seasoned practitioners, but even the other 10% need to know the accepted tenets and nomenclature of relational databases. Quality control in order here?

Marvin


Not everyone who enters into the field of relational databases knows that they are, indeed, relational. Many are thrown to the SQL wolves by necessity or a manager with a bee in his/her bonnet (Hey, I heard of this thing called SQL, we bought a license, now use it!) and told to create a database. Even if they do know it is relational, how to implement the many-to-many relationships isn't always clear.

SQLServerCentral caters to the newbie and the expert. Gotta have articles that are understandable by both. We've had this discussion before on a few threads as to whether or not Steve needs to change his editorial process and/or acceptance process for articles because some are not as technically correct as they should be, perhaps.

Steve wants to encourage people to write articles, to use SSC as a place to cut their writing teeth on as it were. Those with any brains whatsoever will check the forums to see what the response to the post is. It is here that the community corrects itself, provides better solutions and hopefully improves overall. And, when there are items in the forum discussion about the article that the reader should know about, then Steve will mark the article with a note that it is best to read the discussion as well.

I'm an SQL newbie myself. My job includes running ad hoc queries and reports from SQL and monitoring/debugging the processes on one server. I'm not into designing a database yet, although I have one on the side that I'm fiddling around with when I get a bit of spare time. I haven't done any formal training in SQL, just picked it up on the job. Articles like this can help me avoid mistakes like the one he made.

Thanks for the article, Tim.


-- Kit
Post #877005
« Prev Topic | Next Topic »

Add to briefcase «««678910»»»

Permissions Expand / Collapse