Use composite keys? Or always use surrogate keys?

I am always voting for keys that are as narrow as possible, static (never change) and thus I usually favor a surrogate INT as a key over a compound key If you want to reference a compound key from another table, you'll always have to specify several conditions - which can get quite unwieldy at times! Also check out some of those links: agiledata.org/essays/keys.html rapidapplicationdevelopment.blogspot.com... (of course, others will likely post at least 10 links PRO natural keys :-) ) There's no harm in using a surrogate key - go for it! :-) Marc.

I am always voting for keys that are as narrow as possible, static (never change) and thus I usually favor a surrogate INT as a key over a compound key. If you want to reference a compound key from another table, you'll always have to specify several conditions - which can get quite unwieldy at times! Also check out some of those links: agiledata.org/essays/keys.html rapidapplicationdevelopment.blogspot.com... (of course, others will likely post at least 10 links PRO natural keys :-) ) There's no harm in using a surrogate key - go for it!

:-) Marc.

In this example, UserId and GroupId are not natural keys. They are foreign keys that each reference a surrogate. The question of natural keys doesn't even arise in this case.

– Walter Mitty Jun 23 '09 at 9:54.

Whenever I've omitted a surrogate primary key from a join table, I've come to regret it. It just seems like when I get around to writing the code to manage the relation that having a surrogate primary key is very handy. I typically follow an Active Record pattern and am using ASP.NET MVC, though, so your mileage may vary.

In the MVC world, having a single id key you can put on the end of the URL is very advantageous.

– Vinko Vrsalovic Jun 22 '09 at 21:41 The basic reason is that in MVC the url pattern is typically, /controller/action/id. If I don't use a surrogate key, then my url ends up as /controller/action/? User_id=...&group_id=..., which also means that I need include both ids in the view as hidden inputs, etc. A single id just fits into the pattern more neatly.

– tvanfosson Jun 22 '09 at 22:04 Why would there be a URL to a link "object"? There would only be URLs to group entities or member entities. A link should not be an object in your solution space.

A link is a artifact of a database design which facilitates object collection membership in your solution space. – Cade Roux Jun 23 '09 at 4:55 When you want to delete an association, for example. Or, when the join table actually has (or gets) more information -- say it represents a registration for an event, which eventually gets an associated amount charged and a timestamp.In my experience, there have been enough times I didn't use a surrogate key, that I later had to add it that now I just always use it.

– tvanfosson Jun 23 '09 at 12:17.

Given than both user_id and group_id are already surrogate keys and thus guaranteed to be unique (given proper implementation of the app), adding a third id is totally redundant. Unless you are using some kind of ORM which (sadly) usually make more complex the handling of composite keys. In that case you must evaluate the cost of redundancy versus the ease of development with your chosen ORM.

The first example is sufficient. Even if you were to add attributes to the many-to-many table construct (like is_main_grp, etc. ), there is no real need for a surrogate in the link table, and you'll probably always want a unique constraint on user_id, group_id anyway. Only if you were to hang another many-to-one relationship on the link relationship (like tags), THEN I would think about having a surrogate in the user_group table, and I would not do it until it was a requirement (since a surrogate is relatively easy to add): user id* name group id* name user_group id* user_id group_id is_main_grp user_group_tags user_group_id* tag_id* tags id* tag_txt.

I would ordinarily agree with Vinko, but if you're using an ORM, like it can be a happier relationship (between you and bernate) if you give everything a surrogate key.

But a link is a database design artifact, and has nothing to do with the object model - it simply allows the database to link entities - this table should not even be visible to applications. – Cade Roux Jun 22 '09 at 21:38.

Allways ... please check complete analysis here.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.