Timestamp as part of composite primary key ?

Posted on 2006-06-03
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.
Question by:bwtoh
    LVL 30

    Accepted Solution

    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.

    Author Comment

    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:
    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 ?

    LVL 30

    Expert Comment

    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.

    Author Comment

    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.
    LVL 30

    Expert Comment

    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.
    LVL 30

    Expert Comment

    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.

    Author Comment

    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.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
    Creating and Managing Databases with phpMyAdmin in cPanel.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    732 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now