Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2013
    Posts
    2

    Weak entity primary key question

    Hi all,
    Say I have a weak entity whose primary key is a composite key made up of the primary keys of two strong entities (hence they are foreign keys). Is it possible then to have another weak entity related to that first weak entity, using those foreign keys as a composite key again in the new entity? Or should I simply make the first weak entity a strong one by giving it its own surrogate key, making the foreign keys simply normal keys in the table (no longer the entity's primary key), and use the new surrogate key as the primary key of the second weak entity? This is confusing I know, but I hope somebody can make sense of it. Thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    You have to consider this from two distinct points of view, which give opposite answers. You'll have to decide which answer works better for your schema/application.

    From a purely RA (Relational Algebra) point of view, the two strong FKs (Foreign Keys) form a completely acceptable PK (Primary Key). The PK from this weak entity can certainly propagate to its dependant entities, so the to FKs can be used as the PK in those dependant entities. This is completely logical, and meets all of the requirements for good (normalized) relational design.

    From a practical standpoint, it may be better to establish an SK (Surrogate Key). When I have full control of a schema, for every entity MyThings there will be a ThingId which is always a SK. I always declare an NK (Natural Key) using a UNIQUE CONSTRAINT to preserve the RA integrity too, but I use the PK instead of the NK. This has significant practical benefits within the database, but even more benefits within the application structure because an application framework can be built around the ability to quickly and easily manage tables and table operations based on the PK.

    Pick the answer that works for your application/purpose.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2013
    Posts
    2
    Quote Originally Posted by Pat Phelan View Post
    You have to consider this from two distinct points of view, which give opposite answers. You'll have to decide which answer works better for your schema/application.

    From a purely RA (Relational Algebra) point of view, the two strong FKs (Foreign Keys) form a completely acceptable PK (Primary Key). The PK from this weak entity can certainly propagate to its dependant entities, so the to FKs can be used as the PK in those dependant entities. This is completely logical, and meets all of the requirements for good (normalized) relational design.

    From a practical standpoint, it may be better to establish an SK (Surrogate Key). When I have full control of a schema, for every entity MyThings there will be a ThingId which is always a SK. I always declare an NK (Natural Key) using a UNIQUE CONSTRAINT to preserve the RA integrity too, but I use the PK instead of the NK. This has significant practical benefits within the database, but even more benefits within the application structure because an application framework can be built around the ability to quickly and easily manage tables and table operations based on the PK.

    Pick the answer that works for your application/purpose.

    -PatP
    Thank you, that was very clear.

Tags for this Thread

Posting Permissions

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