?
Solved

Timestamp as part of composite primary key ?

Posted on 2006-06-03
7
Medium Priority
?
1,322 Views
Last Modified: 2010-08-05
I have a table which has a composite primary key - my primary key consists of 3 columns.  After some thoughts, I find that those 3 columns does not make my records unique enough.  I plan to add another column to those 3 and make it a 4 column primary key.  The one I thought of adding is a timestamp column to ensure that records are unique.  Is it all right to have timestamp as primary key ?  Will I have problems retriving single rows if my primary key has a timestamp col ?  Should I use datetime instead of timestamp ?  Why ?

Thanks for answering.
0
Comment
Question by:bwtoh
  • 4
  • 3
7 Comments
 
LVL 30

Accepted Solution

by:
todd_farmer earned 840 total points
ID: 16824102
From a database design perspective, I would encourage you to consider adding a single column that is the primary key using MySQL's auto-increment feature.  My experience has been that almost all composite primary keys contain business data that can (and does) change.  It is a much better practice, from my perspective, to create a surrogate primary key that has absolutely no business meaning at all.

You may still want to create a composite unique index on the columns - this makes perfect sense from a data integrity standpoint.  But I would steer away from using business data as part of a primary key.
0
 

Author Comment

by:bwtoh
ID: 16831704
Actually I do use meaningless key/artificial key as primary key as mentioned by you, todd_farmer.

I use it on all my strong entities.  So, lets say I have:
Strong_Entity1(se1_id(PK))
Strong_Entity2(se2_id(PK))
and I have a many-to-many relationship between Strong_Entity1 & Strong_Entity2.

Since I need to resolve that many-to-many rel to 2 1-to-many relationships, I'll have a weak entity in the middle:
Weak_Entity(se1_id(PK), se2_id(PK))

As we know both se1_id(PK), se2_id(PK) are foreign keys from the 2 different strong entities.  Do you recommend to use these 2 foreign keys as a single composite primary key for the weak entity or, again, create a new meaningless single field ID primary key for the weak entity ?

To me, as long as I don't use ON DELETE/UPDATE SET NULL / SET DEFAULT, it is ok to use se1_id(PK), se2_id(PK) as the primary key and do not need to create a new meaningless primary key for the weak entity.

Note that my weak_entity is an event entity..and thus, it has a datetime/timestamp field.

Now, my problem as mentioned above is : I found out that se1_id(PK), se2_id(PK) is also not unique enough because different occurrances at different times can cause duplicate records in the weak entity as far as the se1_id(PK), se2_id(PK) is concerned.  

Thus, to make the records unique, I planned to add the datetime/timestamp field as part of the composite primary key - thus, se1_id(PK), se2_id(PK), time_field.

Thus, comes my questions as mentioned in initial question -
Is it all right to have timestamp as primary key ?  Will I have problems retriving single rows if my primary key has a timestamp col ?  Should I use datetime instead of timestamp ?  Why ?






0
 
LVL 30

Expert Comment

by:todd_farmer
ID: 16833574
TIMESTAMP might work better for you, as you can define it to insert the CURRENT_TIMESTAMP on INSERT (you can't do the same thing within the schema definition when using a DATETIME column).

I would still create another column that uses auto-increment as a primary key.  I've used table designs almost exactly like what you are talking about here, and I've run into a lot of problems with them.  If they work for you, that's cool.
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 

Author Comment

by:bwtoh
ID: 16838566
todd_farmer, thanks for the reply.

We use single-field auto-increment meaningless key/artificial key as primary key AND NOT a business-related primary key because business-related might change.

But since weak entity can use foreign fields se1_id(PK), se2_id(PK) which both are meaningless keys/artificial key that we know it will never change... why do you still recommend creating a new single-field auto-increment meaningless key/artificial key for each weak-entity ?

I need to know why.  Understanding why is important to me... as every design needs to have a reasoning/justification behind it.
0
 
LVL 30

Expert Comment

by:todd_farmer
ID: 16839182
The reason behind it is that the primary key value exists to give the database a concept of identity.  I really ran into this problem when using Object Relational Mapping tools (like Hibernate for Java).  Having a column that defines in unambiguous terms the row identity is a good, good thing.  In the legacy database I dealt with, we had a lot of composite primary keys, composite foreign keys, and keys that included business fields.  All of those things are things I try hard to avoid now - in many cases, we ended up resorting to using the Oracle ROWID, which is Oracle's auto-generated primary key that defines row identity.

Now, you are using primary keys on other tables as part of the primary key on this table, and those primary keys have no business meaning.  This is good.  But what I also hear you saying is that those columns alone do not provide uniqueness - so you have to add a timestamp column.  That seems (in my opinion, again, not knowing your exact business problem) as a bit artificial for defining row identity.  Are you certain that there will never be a need to have two simultaneous events processed for the same values in the other primary key columns?  What about modifying the time?

Additionally, composite keys are simply more difficult to maintain, especially when there are more simple alternatives to establishing row identity.
0
 
LVL 30

Expert Comment

by:todd_farmer
ID: 16839427
Here's one more reason I would use a single-column artificial primary key.  Say you decide at some point in the future to extend your application and database so that each row can be related to multiple rows in another table.  To do this, you need to establish row identity (which row in tableA are we relating these rows to).  In the current implementation, that means that you have to use the key values from the Strong_Entitys and the timestamp.  Effectively, you have to establish an equality relationship between 4 disccrete columns on two different tables.  In my book, it's far better to have one column that defines row identity in unambiguous terms, and to use other constraints to manage relationships between tables and data integrity.
0
 

Author Comment

by:bwtoh
ID: 16849066
Thanks for the very detailed reply todd_farmer, I really appreciate that.

Something new which I'd learnt from your replies above is that, you recommend separating the definition/tasks of :
1) defining row identity in unambiguous terms (primary key)
2) defining constraints to manage relationships between tables (foreign keys)
3) defining constraints to ensure data integrity. (unique index)

Before hearing you, I tend to lump all these with a primary key/foreign key definition...on the same fields.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month14 days, 13 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question