ERD for a Lab Database

Question

Question: In a database for a clinical reference lab a specimen, when taken, triggers a test order (for testing obviously) creation.If that test order produces a failure, the specimen is given a new test order and tested again until a non-failure result is obtained. And test order is created for exactly one specimen.

Would Test Order be a weak entity type since it is dependent on the specimen being taken, and without a specimen it wouldn't exist? Also, would this weak entity type be a self referencing one in the ERD since it generates another order if it is a failure? I am having trouble with this definition.

Answer 1

In your diagram, TestOrder is a regular entity since it's identified by its own attributes (TONo). A weak entity would be identified by SpecNo and a value (e.g. an ordinal) that is unique within the context of each SpecNo at least.

Your design calls for total participation of TestOrder in its relationship with Specimen, i.e. a TestOrder can't exist without being related to Specimen. While weak entities necessarily participate totally in their identifying relationships, total participation don't imply weak entities.

It appears in your diagram that Specimen may participate totally in the relationship too, if a specimen can't exist without at least one TestOrder.

See also my answer to a similar question on StackOverflow. It may be easier to understand these concepts using Chen's original notation which separates relationships from foreign key constraints.

Practically, I think your design is fine without using weak entities.

I also think there's no need for a self-relationship on TestOrder. They're already related by being associated with the same Specimen. All you need is the order of TestOrders (e.g. via a weak entity's ordinal or via a TOTestDate attribute).