March 2, 2010 at 7:36 am
I thought this was a great article, for two reasons. The first is because I've seen databases full of bad design as described in the article and it explains why it was wrong. The article can benefit a great number of people who don't know how to build foreign keys or handle many-to-many relationships.
The second is because articles don't need to be perfect, as the discussion afterwards is often the true value. This article caused all of you to respond and if some newb stumbles on this article (maybe because they searched for decoupling tables 😉 ) and actually reads the discussion, then they learn a lot more than just reading the article itself.
I think articles are picked by the number of responses they get, not by how "good" it is.
March 2, 2010 at 7:43 am
Larry Aue (3/2/2010)
snip...I think articles are picked by the number of responses they get, not by how "good" it is.
That can't be the case as I got to this article through my Daily Update and happen to be the third poster (ignoring the 'Tim posted this' post).
I like the variety in the list I get and the varied levels, quality, depth etc. As Larry said, the added value of the forum makes an article so much more worthwhile.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
March 2, 2010 at 7:49 am
shan plourde (3/2/2010)
Why a surrogate PK in the User/Group table?
Edit: I misread your question as "tables" and explained why I would want ones in the User and Group tables, respectively. Time for coffee!
March 2, 2010 at 7:52 am
I have heard them called "intermediate tables".
The information in the article is good information. I know of a hiring manager who eliminated candidates for a database developer position because they could not diagram a many to many relationship in an RDBMS environment. I could be wrong, but I don't think this hiring manager cared what the candidate called the table.
March 2, 2010 at 8:25 am
@tim-2:Great article. I wish I would of had this article many years back.
@ the critics out there. If you don't like the article read another or better yet write a better one. Remember that not everyone is at the same technical level.
I worked many years as the sole IT/Technical person in a company and didn't have anyone else to communicate with. Before that I worked as a Telco Engineer and was introduced to databases. When I moved on from the sole IT/Tech role job I was on a team with other DBA's and was grateful for their patience in teaching me proper terms. I knew how to do my job but I didn't always have the proper vocabulary. Because I had been exposed to many other facuets of IT I had knowledge they didn't and was able to share.
I spent and continue to spend a lot of time filling in knowledge gaps especially with terminology. From the Telecom world to the IT world there are many terms that mean the exact same thing it's almost like 2 different languages. Sometimes between Developer and the rest of IT I think there is a different language.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
March 2, 2010 at 8:29 am
I found the article sound. His use of the term bridge table might not be my choice but he gave us his definition of the term, and adaquately described the decoupling process. I have heard the terms "link table", "join table", "map table", "juntion table", "intersection tables" and "many to many resolution table" to describe a tables that contain the keys from other tables in a many to many relationship. I wish I had read an article like this when I had started out, It would have saved me some headaches down the line. A rose is a rose and good database design is good database design. This article helps others get closer to that end.
March 2, 2010 at 8:30 am
Karen Lopez - InfoAdvisors (3/2/2010)
...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
I'm very intrigued by this idea of Karen's developers...and like her, "I don't see that"
How would they design a database with Items and Vendors? I have a piece of safety equipment that I can buy from multiple distributors, we'll call it 3M-EarPlug. Any of those vendors can sell me more than just this earplug (3M-FaceShield and 3M-Respirator, just to name a few). How would these developers that Karen works with suggest designing this relationship?
As a distributor in the supply chain, we will purchase the 3M parts mentioned above, not from 3M themselves, but from the distributor that has the best price, an easy thing to find when using a many-to-many relationship in a normalized relational database.
Would love to hear Joe Celko on this one 😀
March 2, 2010 at 8:43 am
jinlye (3/2/2010)
What surprises me is that this was the number one 'featured article' in this week's mailing from sqlservercentral.com. This must either be because:A) The 'featured articles' are just chosen at random, in which case it would be better to call them 'randomly-chosen articles', OR...
B) The person who chooses the featured articles wasn't able to tell (like surely most SQL Server developers would be able to at a glance) that this is a significantly flawed article - which would be rather scary.
Something went wrong here.
The featured article is something we are calling out for the day. It is an original article published here.
I highly disagree that this is a significantly flawed article there are people that view this as decoupling information from tables, and while that might not be what we refer to it as DBAs, you ought to be aware that developers do view things this way. Putting this knowledge out there is one more way to get the word out to developers.
March 2, 2010 at 8:53 am
tstaker (3/2/2010)
@ the critics out there. If you don't like the article read another or better yet write a better one. Remember that not everyone is at the same technical level.
I thought that all but one comment was trying to be discursive or helpful - including mine. Having read Tim's own comments in response I thought that he did too.
It is great that Tim took the time and effort to submit an article but I think he would have been disappointed if everyone was sycophantic about it. I, for one, have learnt through the ensuing discussion. As always "Hats off to the instigator" - usually it is that Steve fella with his editorials but this time the Kudos is Tim's.
We must not knock people questioning the content of articles. It is too important to have these discussions. We are professionals after all.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
March 2, 2010 at 8:57 am
I believe the article should be called ""Proper Normalization". I was hoping to read an article about "decoupling" databases/applications using "bridge" tables that span information from one application/database to another. I was hoping to read about a method for replication that maintains the data of a "bridge" table in both databases automatically and reliably. Often, I have linked tables from one database or server to another and thus coupled the applications/databases/servers. This has allowed the database elements to be maintained by the proper application but also used throughout the information system. However, because the applications/databases/servers are now "coupled" I have systems that are dependant on one another that do not necessarily need to be. If anyone has a good article/method for "decoupling" applications/databases/servers using some kind of "bridge" table, I would love to read it.
March 2, 2010 at 9:16 am
I agree with most of the replies that this article is misnamed as 'decoupling databases' rather than Designing database. But, the purpose of article is more served than intended with so many responses and healthy discussion.
I am always more interested to read the comments after any article. Cause that is where we get the actual knowledge content.
SQL DBA.
March 2, 2010 at 9:19 am
Lets see if I can talk like one of karens developers. As I developer I think of objects with properties and methods. So If I want to buy a 3M widget I look at its availablity property to get a collection of vendors that sell the item. From the other side of the many to many relationship I get the Catalog property of a vendor if I want a collection of the items a vendor sells. Of course in the relational database world I am pulling records from the Vendor_Item table to populate the collection. But In the real world I would build my availabity list from previous purchase orders and by searching the catalogs of my vendors, they would be distinct lists with differening Items and vendors. In the data base world we might be tempted to place a price in our Vendor_Item table to indicate the price that was can be found in the Vendor's catalog. Of course the prices change when the vendor publishes a new catalog so we update our table in the database world. In the developer world we would create a new catalog collection and assign it to the vendor by updating the catalog property. I wouldn't go so far as to say the many to many relationship doesn't exist but the different perspective makes us think of it differently.
March 2, 2010 at 9:38 am
Todd,
I'll admit up front that I lack MUCH developer insight, thus my interest in Karen's response. How does the developer know that each vendor is indeed selling the exact same widget?
In relational database design, my "3M-Widget" item number is a unique record in the item table. With the link table (the many-to-many table that stands between the item and vendor tables), I'm guaranteed to know that each vendor that's linked to the 3M-Widget is indeed selling the same widget. So, when I compare their prices, I'm looking at an apples-to-apples relationship. This comparison is an intrinsic nature of the relational model. I assume the developers trust the database for that same apples-to-apples comparison at this point? (Again, I'm not a developer).
March 2, 2010 at 11:46 am
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.
The real world doesn't have many to many relationships????!?!?!?!
Ask your developers how they would model the relationship between classes and students without an intersection table (my preferred name for them).
As for pushing a model too far, they need to look in the mirror to their beloved OO model. Being hierarchical, it cannot possibly accurately model the "real world" where hierarchies are actually fairly rare. Sure we tend to put things into hierarchies because it helps us make sense of things, but it doesn't take too long before you realize that most of our efforts at taxonomy are of fairly limited use and not without controversy.
/*****************
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
*****************/
March 2, 2010 at 11:50 am
Oh, and for all those who wrote in criticizing the article, give it a rest. The article is very basic, but as some have pointed out, there are people of all experience levels reading SSC. If just a few newbies read this and are helped by it, it was well worth Timothy's time in writing it and worth SSC's time in publishing it.
Nicely done Timothy.
/*****************
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
*****************/
Viewing 15 posts - 31 through 45 (of 98 total)
You must be logged in to reply to this topic. Login to reply