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?
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"
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.
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.