Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2003
    Location
    SF Bay Area
    Posts
    18

    Unanswered: Using Timestamp in a primary key

    Anyone want to share anything regarding the idea of using a Timestamp in a composite primary key with an ID?

    Thanks

    Bryan

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    WHY?
    How will you avoid duplicates?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2003
    Location
    Bangalore,India
    Posts
    51
    Na Na...

    Dont do it... Doesn't give you any much help...

    Cheers
    Life is already complex...lets keep programming simple

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Use it ONLY if you can guarantee that you won't enter a row in the same time period. Typically using a time stamp as part of the primary key is not a good idea and it doesn't gain you anything. Personally, I always use sequences as my primary key unless there is a good reason in the data not to.
    Last edited by beilstwh; 08-10-04 at 09:45.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105

    maybe...

    if you were doing some sort of history tracking....Is this a dimension table in a data mart?

    If so...go with the standard:

    surrogate_key, business_key (id), current_flag, start_date, end_date

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by dstachon
    if you were doing some sort of history tracking....Is this a dimension table in a data mart?

    If so...go with the standard:

    surrogate_key, business_key (id), current_flag, start_date, end_date
    While I admit that that data must be indexed, for speed, I would suggest that you keep the size of your indexes down. If I was setting up the table, I would have the following.

    Primary Key - Surrogate_key
    Customer_index - business_key,current_flag
    Date_index - Start_date,end_date

    That way if you have to find a specific customer, you have it indexed. If you need to find a number of records in a date range, You have it indexed. If it was part of the primary key, a full san of the index would still need to be done.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Nov 2003
    Location
    SF Bay Area
    Posts
    18
    The problem is two fold. Versioning changes in attributes that represent a historical view of an entity. The argument that I face is that this can not be represented in ERD, which I disagree with.

    So for example:

    First Last Start End
    -----------------------------------
    Susan White 06/07/67 present

    Gets married and becomes

    Susan White 06/07/67 03/01/04
    Susan Smith 03/01/04 present

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    customer ID with an ACTIVE indicator and an active date.
    Code:
    Cust_Id      Name      Active_Ind       Active_Date
    12345     Susan White      N              06/07/67 
    12345     Susan Smith      Y              03/01/04
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Dec 2003
    Location
    Buenos Aires, Argentina
    Posts
    86
    I agree with the Duck, that would be a good solution.
    Regards,

    Manf

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    to add to that, for the PK you could also have
    a unique name_id (sequence) to represent each name.

    Code:
    name_id    cust_id
    56781       12345
    95628       12345
    PHP Code:
    select from customer_name
    where name_id 
    =
    (
    select max(name_idfrom customer_name
    where cust_id 
    12345); 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  11. #11
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105

    ERD or Data Model?

    Is your table strictly for reporting or is it operational?

  12. #12
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105
    Quote Originally Posted by The_Duck
    customer ID with an ACTIVE indicator and an active date.
    Code:
    Cust_Id      Name      Active_Ind       Active_Date
    12345     Susan White      N              06/07/67 
    12345     Susan Smith      Y              03/01/04

    Why not a surrogate key? For true history preservation, you would want to join to the transaction table as the customer was at the time of the transaction.

    i.e

    cust_sid cust_id name
    1 12345 Susan White
    2 12345 Susan Smith

    order_no cust_sid
    O1 1
    O2 1
    O3 2

    we now know, she placed orders 1 and 2 as Susan White, and O3 as Susan Smith...elegant! Kimball.....very smart man.

  13. #13
    Join Date
    Jul 2003
    Posts
    2,296
    my second post added a key for just what you described.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  14. #14
    Join Date
    Nov 2003
    Location
    SF Bay Area
    Posts
    18
    This is an operational table. The application is required to build temporal view of an entities attributes begining in time at the first known attribute about the entity, while versioning changes.

    As attributes are added or modified over time, the changes need to be versioned. The goal is to build a timeline of attributes that applied to an entity for the life of the entity. I am not an expert at warehousing, but this seems to be right in line with this.

    The design I am currently leaning towards looks a bit like The Ducks proposal with the addition of current view tables for performance.

    Something like this:


    CREATE TABLE IDENTITY (
    first_name,
    middle_name,
    last_name,
    suffix,
    title,
    start_date,
    end_date,
    instime,
    status,
    person_id
    );

    ALTER TABLE IDENTITY
    ADD ( PRIMARY KEY (person_id, start_date, end_date, status) ) ;

    CREATE TABLE IDENTITY_HISTORY (
    identity_no,
    first_name,
    middle_name,
    last_name,
    suffix,
    title,
    person_id,
    start_date,
    end_date,
    status
    );

    ALTER TABLE IDENTITY_HISTORY
    ADD ( PRIMARY KEY (identity_no) ) ;

    CREATE TABLE PERSON (
    person_id,
    person_type,
    start_date,
    end_date,
    instime,
    updtime,
    worker_class_id
    );

    ALTER TABLE PERSON
    ADD ( PRIMARY KEY (person_id) ) ;

    ALTER TABLE IDENTITY
    ADD ( FOREIGN KEY (person_id)
    REFERENCES PERSON ) ;

    ALTER TABLE IDENTITY_HISTORY
    ADD ( FOREIGN KEY (person_id, start_date, end_date, status)
    REFERENCES IDENTITY ) ;

    ALTER TABLE PERSON
    ADD ( FOREIGN KEY (worker_class_id)
    REFERENCES WORKER_CLASS ) ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •