March 2, 2010 at 12:03 pm
Todd -
I do understand why they don't want to think of this as an association, much as you describe. The part that causes me grief is their insistence that there isn't actually a many to many in the real world. Or, worse, that I should store the price of the Vendor Item (the association of Vendors to items) in two places to support their view of the world. That's the hard part - that there's data that does naturally belong to the association, but they want to build two 1:M tables and duplicate the data in both places, with all the normal assurances that they will keep it in sync in the code.
March 2, 2010 at 12:22 pm
Congrats Tim on getting your article published.
Good job.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 2, 2010 at 1:13 pm
I have another question from the article. Every PK in all three tables is called "id" which I have never used. I would name them UserId, groupId etc. Also naming columns as fk_ in bridge/link table: Is this good naming convention?
March 2, 2010 at 1:35 pm
gaurav.bhagat (3/2/2010)
I have another question from the article. Every PK in all three tables is called "id" which I have never used. I would name them UserId, groupId etc. Also naming columns as fk_ in bridge/link table: Is this good naming convention?
I prefer the way I do it. My guess is that most people don't do column naming the way I do (id for the identity column, fk_TABLENAME for the foreign key relationship). I know that the Microsoft databases (including the sys tables, etc) do it the way you suggested; however, I like to use my naming conventions because of programmatic implications down the road (ie, I can use inheritance, and other object oriented advantages to get reusability out of my database interaction).
March 2, 2010 at 2:19 pm
A little off the subject but a few things caught my eye
1. Identity column in user_group table (which someone pointed out) seems worthless.
2. The constraint on the amount of users for a group in the table
#2 stood out to me.
In my minds eye this constraint is a business rule that should not be placed on the database. This should be enforced in the application. The database should not care about this just do its job and store the groups a user is part of.
March 2, 2010 at 2:36 pm
the biggest take-away from this article should be: never use an IDENTITY primary key field in a many-to-many (junction, link, bridge, associative, etc.) table. It provides no value and allows for duplicate relationships.
ID UserId GroupId
1 100 200
2 100 200
Make the 2 foreign key ids into a composite primary key and you can't get duplicates.
March 2, 2010 at 2:52 pm
I personally disagree with the assertion that the identity column is worthless in this example (personal bias notwithstanding). Granted, in database design, the identity column is unnecessary, and not best practice; however, depending on what is going on in the code, and various components of the business logic of the application, the identity column could be valuable.
What if the USER_GROUP table did more than just create a link between User and Group?
Consider the following requirements/use case:
1. A User can join and unjoin a group, and a record needs to be kept for each time that happened.
2. When a user unjoins a group within "X" days of joining a group, the record of them joining and unjoining are both physically deleted from the database.
Based on #1, the USER_GROUP table would be modified to look as follows:
id - int
fk_user - int
fk_group - int
action - something representing either "Join" or "Unjoin"
actiondate - datetime
Based on #2, I would rather delete based on an identity column, rather than a foreign key relationship to the User table, because I assume less risk in that event.
Granted, I did not mention this functionality in the article, but I find that I assume less risk if I can key off a unique value, as opposed to some combination of column values.
By the way, I know this use case is a stretch, but my practice is to create an identity column on every table, precisely because of feature bloat, extensibility, interactions that occur between the code and the database, and the safety net that an identity column buys me.
March 2, 2010 at 3:22 pm
He's just saying that if you include an identity column in a PK then whatever you insert into the table will always be unique because an identity column always increments. I usually run into this scenario when a datetime has been included in a PK.
One thing you _could_ do is make the identity column the PK and put a unique key constraint on the composite FK combinations. This would, for example, allow you to use the Identity column as a FK to another table and not have to deal with composite keys.
March 2, 2010 at 8:46 pm
You don't have to use a primary key to enforce uniqueness. You can use an alternate key as well. So you could have a surrogate key AND have a unique index/constraint to make the USER ID/GROUPID combination have only one instance.
I do agree that putting on surrogate key on an associative table usually adds no value. However, if that associative table has child tables, having only a surrogate key migrate to the child tables can be beneficial.
But in general, I don't use surrogate keys on associative tables.
March 3, 2010 at 4:27 am
Karen Lopez - InfoAdvisors (3/2/2010)
You don't have to use a primary key to enforce uniqueness. You can use an alternate key as well. So you could have a surrogate key AND have a unique index/constraint to make the USER ID/GROUPID combination have only one instance.I do agree that putting on surrogate key on an associative table usually adds no value. However, if that associative table has child tables, having only a surrogate key migrate to the child tables can be beneficial.
But in general, I don't use surrogate keys on associative tables.
I agree, generally associative table are the only ones I don't consider adding an identity column to.
March 3, 2010 at 11:31 am
Essentially the author is telling us that some time ago he was one of the worst DB developers out there and lately he has started to learn some very basic design principles and he stopped writing those horrible db schemas. Not sure it's worth sharing it in an article
March 3, 2010 at 12:03 pm
Andrei Marculescu (3/3/2010)
Essentially the author is telling us that some time ago he was one of the worst DB developers out there and lately he has started to learn some very basic design principles and he stopped writing those horrible db schemas. Not sure it's worth sharing it in an article
I won't go into what your post tells me about you...
I'll just ask; What have you contributed that is "worth" sharing? Aside from a snide and completely unnecessary swipe at someone who DID contribute something...
/*****************
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 3, 2010 at 12:11 pm
you don't know more about me than you know about DB design
March 3, 2010 at 12:14 pm
True enough, I know quite a bit about database design, and next to nothing about you, but what I do know about you is more than enough.
There is no need to be as condescending and rude as you were, period.
/*****************
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 3, 2010 at 12:17 pm
I just said exactly what the article says
Viewing 15 posts - 46 through 60 (of 98 total)
You must be logged in to reply to this topic. Login to reply