4 key primary key vs 1 key artificial primary key

Hi all

I have the following table

CREATE TABLE [dbo].[property_instance] (
[property_instance_id] [int] IDENTITY (1, 1) NOT NULL ,
[application_id] [int] NOT NULL ,
[owner_id] [nvarchar] (100) NOT NULL ,
[property_id] [int] NOT NULL ,
[owner_type_id] [int] NOT NULL ,
[property_value] [ntext] NOT NULL ,
[date_created] [datetime] NOT NULL ,
[date_modified] [datetime] NULL
)

I have created an 'artificial' primary key, property_instance_id. The 'true' primary key is application_id, owner_id, property_id and owner_type_id

In this specific instance
- property_instance_id will never be a foreign key into another table
- queries will generally use application_id, owner_id, property_id and owner_type_id in the WHERE clause when searching for a particular row
- Once inserted, none of the application_id, owner_id, property_id or owner_type_id columns will ever be modified

I generally like to create artificial primary keys whenever the primary key would otherwise consist of more than 2 columns.

What do people think the advantages and disadvantages of each technique are? Do you recommend I go with the existing model, or should I remove the artificial primary key column and just go with a 4 column primary key for this table?

Thanks Mattno the subject of surrogate and natural keys, we already have many threads (http://www.dbforums.com/search.php?action=showresults&searchid=479872&sortby=lastpost&sortorder=descending) -- not sure if that will work, it's a search for surrogate and natural

one thing troubles me, and that is how you have both owner_id and owner_type_id in your candidate key

doesn't that violate some normal form or another? isn't owner_type_id totall dependent on which owner it is?|||Originally posted by r937
no the subject of surrogate and natural keys, we already have many threads (http://www.dbforums.com/search.php?action=showresults&searchid=479872&sortby=lastpost&sortorder=descending) -- not sure if that will work, it's a search for surrogate and natural

one thing troubles me, and that is how you have both owner_id and owner_type_id in your candidate key

doesn't that violate some normal form or another? isn't owner_type_id totall dependent on which owner it is?

Thanks for the search keyword tips. I did indeed find a number of helpful threads.

Matt|||I believe the general resolution was that Plain-Bellied Sneetches prefer natural keys, while Star-Bellied Sneetches see the advantages of surrogate keys.

Experts opinions varried:
Moe: Surrogate Keys
Larry: Natural Keys
Curly: Nyuck nyuck nyuck

We are still awaiting final Papal Dispensation on the subject.|||hilarious

i'm afraid that rome will tell you that it can only be a natural primary key, as surrogates are the work of the devil

one of curly's best lines is "i'm tryna think but nothin happens!" which is how i feel sometimes when asked to list the pros and cons of surrogate keys versus natural

maybe i should just say the words "surrogate key" again, and perhaps also the words "natural key" -- so it shouldn't be a total loss, that way they will be salted quite heavily in this post, and therefore this thread, for the benefit of search engines

moe and larry: "niagara falls!! slowly i turn... step by step... inch by inch..."
[moe and larry advance on curly, who backs up into the bathroom, and falls into the bathtub]
moe: "what are you doing in there?!"
curly: "so it shouldn't be a total loss, i'm takin a bath"
moe: "a bath?!"
curly: "yeah, a bath"
moe (after a slight pause): "move over"|||For what it's worth:

it depends upon the scale of the data, the query composition and whether or not you need to sub index on a primary's sub key.

given

application_id, owner_id, and property_id

being the key, the whole key and nothing but the key, do you ever need to index to owner_id alone? if yes then:

owner_id, application_id, and property_id

and so on.

Do you join across tables, i.e. owner_id -> owner details and property_id -> property details? If so, which ordering of the composite key gives fastest access? Who the hell knows without knowledge of the data definition? Nobody, that's who.

There's no generic solution to this question, it depends entirely on your data, and you should normalize to a correctly third normal multiple key and then procedurally denormalize to a compound key if your unique scenario requires it.