Quick Database Schema Question

[clamum]

I'm sure someone will be able to answer this pretty easily but it's just something I've been wondering about.

I have three tables: A "Post" table, a "Tag" table, and a "PostTag" table. The "Post" table has an auto-increment "ID" column and a few other unimportant columns. The "Tag" table has an auto-increment "ID" column and an unimportant nvarchar column.

The question I have is regarding the "PostTag" table. This table is a connecting table between "Post" and "Tag": a record in "Post" can have multiple Tags, so "PostTag" has two columns; one to store "Post.ID" and one to store "Tag.ID". The question is: do you recommend I make a composite primary key from these two columns, or should I add another column, "ID", that is auto-increment, and make that the primary key?

If it matters, I'm using SQL Server 2008 and .NET 4.0, and I will be using LINQ-to-SQL for data access. Thanks for any input!

 

[brandonb]

If I was doing it, I'd have it have it's own IDENTITY column that is the Primary (clustered) Key.

The reason is this: Performance.

Everytime someone adds a record, it gets appended to the end of the clustered index due to the identity column, and therefore you have less inserts in whatever place in the database and potential page splits, reallocations, etc.

In addition to this, create 2 non clustered indexes based on the two other IDs (post and tag)...

http://msdn.microsoft.com/en-us/library/ms189051(v=sql.105)

 

[BrightCandle]

Normally you use a composite key for this N to N created tables because in themselves they have no data whatsoever and hence are uniquely identified by the two things they link together. Most ORMs work without a unique key on the link tables and I am pretty certain LINQ will as well.

 

[brandonb]

This is a follow up to my earlier post. Take for example:

CREATE TABLE map_person_employment
(

     id_map_person_employment  INT NOT NULL IDENTITY(1,1),

     id_person                INT NOT NULL FOREIGN KEY REFERENCES person(id_person),
     id_employment         INT NOT NULL FOREIGN KEY REFERENCES employment(id_employment),

     salary                     MONEY,
     title                       NVARCHAR(64),
      
     date_from               DATETIME,
     date_to                  DATETIME,

)

So if you have a mapping table between two different objects (such as a person and employment) you might want to store additional information for the person such as salary at the time, etc. Since salary can change and you want to store the same association, you can use the date_from and date_to and create multiple rows and you can keep a history of the salary changes through time, etc.

 

[TechBoyJK]

I'd use an autoincrement identity field.. not a composite key. IMHO a clustered index on the identity field will perform better than a composite key.

/couldbetotallywrong

 

[KB]

I design my databases using the single IDENTITY primary key like brandonb suggested.

Doing so simiplifies UPDATE and DELETE queries:

DELETE FROM table WHERE KeyField = KeyID
instead of
DELETE FROM table WHERE CompositeKey1 = Key1 AND CompositeKey2 = Key2

I don't think performance is so much an issue, but if as brandonb suggested you wish to create further parent-child relationships to the PostTag table it is better to have a single key in which you need to refer.

 

[clamum]

Yeah I was kind of leaning towards a single IDENTITY column but figured I'd see what people smarter than me thought.

That makes sense brandonb, and the non-clustered indexes on the Post and Tag columns is a good idea as well. There will not be a ton of INSERTs into this table, so I think that would work well. And I agree with KB that the UPDATE/DELETE queries are definitely simplified when going with the single column as primary key.

Do you guys recommend I put foreign key relations on the Post and Tag columns? I usually don't use foreign keys but am willing to do so in this case. If so, should I use cascading UPDATE/DELETE as well?

Thanks for the info guys.

 

[KIAman]

Cascading UPDATE/DELETE? If you want this to be simple, create a view that links your related tables together and do all your UPDATE/DELETE through the view.

Just make sure your statement only references one base table in the from clause.

 

[KB]

Yeah I was kind of leaning towards a single IDENTITY column but figured I'd see what people smarter than me thought.

That makes sense brandonb, and the non-clustered indexes on the Post and Tag columns is a good idea as well. There will not be a ton of INSERTs into this table, so I think that would work well. And I agree with KB that the UPDATE/DELETE queries are definitely simplified when going with the single column as primary key.

Do you guys recommend I put foreign key relations on the Post and Tag columns? I usually don't use foreign keys but am willing to do so in this case. If so, should I use cascading UPDATE/DELETE as well?

Thanks for the info guys.

Why wouldn't you create foriegn relations. It doesn't take long and they are the best way to guarantee database consistantly. I just about always create them and always use CASCADE delete/update. They only issue is you can't have CASCADE DELETE set on multiple child relations on one table.

 

[clamum]

KB: Yeah I guess I don't really know why I don't use foreign keys much at all. I do know the benefits but I have no good excuse for not using them. This is for a personal website so it don't matter but I think I will, and if I happen to lead a project at work I will integrate them into it as well.

KIAman: I forgot about views. I didn't know you could update the base tables from a view, though, that is cool.

 

[Markbnj]

KB: Yeah I guess I don't really know why I don't use foreign keys much at all. I do know the benefits but I have no good excuse for not using them. This is for a personal website so it don't matter but I think I will, and if I happen to lead a project at work I will integrate them into it as well.

KIAman: I forgot about views. I didn't know you could update the base tables from a view, though, that is cool.

It'll become your default approach once you really get relational databases and how they're structured.

 

[cytg111]

If I was doing it, I'd have it have it's own IDENTITY column that is the Primary (clustered) Key.

The reason is this: Performance.

Everytime someone adds a record, it gets appended to the end of the clustered index due to the identity column, and therefore you have less inserts in whatever place in the database and potential page splits, reallocations, etc.

In addition to this, create 2 non clustered indexes based on the two other IDs (post and tag)...

http://msdn.microsoft.com/en-us/library/ms189051(v=sql.105)

- cant find the "like" button, so i quote instead. Good answer. (wouldnt have catched that myself.. until now that is )