Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Posts
    5

    Unanswered: Surrogate or composite primary key?

    My previous post was not really clear, so I'll try again with a (hopefully) better (even if longer) example...

    Consider the following...

    A JOB describes the processment of a document.
    Each document can exist in two versions: English and French.
    A JOB can have 1 or 2 TASK, each describing the processement of either the English or French version.
    So we have the following:

    A: JOB (JobNum [PK], DocReference, StartDate, EndDate, ...)
    B: TASK (JobNum [PK] [FKa], Version [PK], Priority, ...)

    that is there is an identifying 1:M (where maxium allowed for M is 2) relationship between JOB and TASK; TASK being identified by JobNum and Version (where the domain for Version is {E, F}).

    Each TASK may require a TRANSLATION sub_task.
    Each TASK may require a TYPING sub_task.
    Each TASK may require a DISTRIBUTION sub_task.

    For example, for a given doc, the English TASK requires TRANSLATION and DISTRIBUTION, while the French only DISTRIBUTION.

    That is, there is a 1:1 not-required relationship between TASK and TRANSLATION, TYPING and DISTRIBUTION.
    So we have the following:

    A: JOB (JobNum [PK], DocReference, StartDate, EndDate, ...)
    B: TASK (JobNum [PK] [FKa], Version [PK], Priority, ...)

    C: TRANSLATION (JobNum [PK] [FKb], Version [PK] [FKb], DueDate, ...)
    D: TYPING (JobNum [PK] [FKb], Version [PK] [FKb], DueDate, ...)
    E: DISTRIBUTION (JobNum [PK] [FKb], Version [PK] [FKb], Copies, ...)

    As you can see I am using the PK of TASK as FK and PK for each of the three SUB_TASKs.

    To complicate things, each SUB_TASK has one or more assignments. The assignments for each SUB_TASK records different information from the others.
    So we have...

    A: JOB (JobNum [PK], DocReference, StartDate, EndDate, ...)
    B: TASK (JobNum [PK] [FKa], Version [PK], Priority, ...)

    C: TRANSLATION (JobNum [PK] [FKb], Version [PK] [FKb], DueDate, ...)
    D: TYPING (JobNum [PK] [FKb], Version [PK] [FKb], DueDate, ...)
    E: DISTRIBUTION (JobNum [PK] [FKb], Version [PK] [FKb], Copies, ...)

    F: TRA_ASSIGN (JobNum [PK] [FKc], Version [PK] [FKc], Index [PK], Translator, ...)
    G: TYP_ASSIGN (JobNum [PK] [FKd], Version [PK] [FKd], Index [PK], Typyst, ...)
    H: REP_ASSIGN (JobNum [PK] [FKe], Version [PK] [FKe], Index [PK], Pages, ...)

    that is there is an identifying 1:M relationship between each SUB_TASK and its ASSIGNMENTs, each ASSIGNMENT being identified by the SUB_TASK it belongs to and an Index.

    I wish I could send a pic of the ER diagram...

    Maybe there is another and better way to model this: if so, any suggestion?

    Given this model, should I use for TRANSLATION, TYPING and DISTRIBUTION a surrogate key, instead of using the composite key, like for example:

    C: TRANSLATION (TranslationID [PK], JobNum [FKb], Version [FKb], DueDate, ...)
    D: TYPING (TypingID [PK], JobNum [FKb], Version [FKb], DueDate, ...)
    E: DISTRIBUTION (DistributionID [PK], JobNum [FKb], Version [FKb], Copies, ...)

    this will "improve" the ASSIGNMENTs tables:

    F: TRA_ASSIGN (TranslationID [PK] [FKc], Index [PK], Translator, ...)
    G: TYP_ASSIGN (TypingID [PK] [FKd], Index [PK], Typyst, ...)
    H: REP_ASSIGN (DistributionID [PK] [FKe], Index [PK], Pages, ...)

    I could even go further using a surrogate key even for TASK, which leads me to the following:

    A: JOB (JobNum [PK], DocReference, StartDate, EndDate, ...)
    B: TASK (TaskID [PK], JobNum [FKa], Version , Priority, ...)

    C: TRANSLATION (TaskID [PK] [FKb], DueDate, ...)
    D: TYPING (TaskID [PK] [FKb], DueDate, ...)
    E: DISTRIBUTION (TaskID [PK] [FKb], Copies, ...)

    F: TRA_ASSIGN (TaskID [PK] [FKc], Index [PK], Translator, ...)
    G: TYP_ASSIGN (TaskID [PK] [FKd], Index [PK], Typyst, ...)
    H: REP_ASSIGN (TaskID [PK] [FKe], Index [PK], Pages, ...)

    I don't really like this second solution, but I'm still not sure about the first solution, the one with the surrogate key only in the SUB_TASks tables.

  2. #2
    Join Date
    Sep 2003
    Posts
    364
    Many people will debate the use of surrogate primary keys. I won't say which I prefer because Brett will probably have something to say about it LOL. Eventually, you'll have to weigh the pros and cons of each approach and decide for yourself. You're the one that has to live with the results.

  3. #3
    Join Date
    Feb 2004
    Posts
    492
    Quote Originally Posted by peterlemonjello
    (...) You're the one that has to live with the results.
    LOL! and it's true as a turtle.

Posting Permissions

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