Is it possible for total participation constraints to be enforced by an attribute

database-designerd

For a school Food Delivery application I am developing, I've been told to use the most accurate ER model that captures as many constraints as possible. In particular, I have decided to enforce a given constraint

"Each order’s food items must be from a single restaurant"

using an aggregation from Orders to Restaurant-FoodItem relationship. However, as the application layer requires to get a restaurant_id associated to an Order, what I am currently doing to get restaurant_id is to join all the way from Orders to Restaurants, following the edges on the ER model.

There was another alternative suggested by my teammate to just put restaurant_id as an attribute in Orders to enforce the constraint as well as allow easier more efficient retrieval of restaurant_id. May I know whether the suggested alternative allows us to enforce the earlier constraint?

Best Answer

This would be one way to enforce the constraint.

-- User USR exists.
--
user {USR}
  PK {USR}

-- Restaurant RST exists.
--
restaurant {RST}
        PK {RST}

-- Fare (food, drink) FAR exists.
--
fare {FAR}
  PK {FAR}

-- Restaurant RST serves fare FAR.
--
menu {RST, FAR}
  PK {RST, FAR}

FK1 {RST} REFERENCES restaurant {RST}
FK2 {FAR} REFERENCES fare       {FAR}

-- User USR placed (his/her) order number ORD#,
-- from restaurant RST.
--
order {USR, ORD#, RST}
   PK {USR, ORD#}
   SK {USR, ORD#, RST}

FK1 {USR} REFERENCES user       {USR}
FK2 {RST} REFERENCES restaurant {RST}

-- User USR ordered QTY of fare FAR
-- from restaurant RST in (his/her) order number ORD#,
-- as item number ITM# of that order.
--
order_item {USR, ORD#, RST, ITM#, FAR, QTY}
        PK {USR, ORD#, RST, ITM#}
        AK {USR, ORD#, RST, FAR}

  FK1 {USR, ORD#, RST} REFERENCES
order {USR, ORD#, RST}

 FK2 {RST, FAR} REFERENCES
menu {RST, FAR}

Note:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key

Using suffix # to save on screen space.
Works for SQL Server and Oracle, for others use _NO.
For example, rename ORD# to ORD_NO.

Related Solutions

Unenforced constraints/possible duplication in the database design

Your model includes referential cycles. This comes up quite a bit when you're modeling and you haven't worked through the differences between what relationships exist and what relationships are important to your system.

Without knowing more about your business rules and their justification, I would suggest that you consider dropping teachers.topic_id and possibly also teachers.subject_id.

Your lessons table relates subjects and topics. Since you have a many-to-many relationship between teachers and lessons, you can use lessons (via teachers_and_lessons) to tell you which teachers can teach which topics and which subjects.

I doubt that neither teachers.topic_id nor teachers.subject_id are correct anyway, since this would imply that any given teacher can only teach one topic and one subject. I don't know about your school, but it sounds much too specialized to me!

Edit: Recognizing Teaching Qualifications

Based on a comment by OP (below) the question is how can a teacher be recognized as being qualified to teach a topic without there being an actual lesson assignment made (yet)?

Issue 1: The relationship of topics to subjects:

It isn't clear from the original question whether topics belong to one subject or possibly to multiple subjects. If topics (e.g. fractions) belong to only one subject (e.g. math) then there should be a foreign key like so: topics.subject_id. If this were so, then lessons.subject_id would be redundant and could also be dropped. This would simplify the model and make things much clearer while reducing the risk of inconsistent foreign keys.

Issue 2: What teachers can do vs. what they are doing:

The teachers_and_lessons table shows which lessons teachers are actually teaching. Before this assignment is made, OP would like to be able to record which topics a teacher is qualified to teach. This could be done with a second M:N intersection, like so:

teacher_qualifications:
(teacher_id (PK/FK), topic_id (PK/FK))

This design assumes the simplification suggested under Issue 1, above. One potential drawback of this simplistic solution is that there is no constraint against assigning a teacher to teach a lesson for which they are unqualified. This constraint, if it is important, could be accommodated by replacing the teacher_qualifications and the teachers_and_lessons tables with new tables as follows:

teacher_qualifications:
(qualification_id (PK), teacher_id (FK,UN1), topic_id (FK,UN1))

lesson_instructor:
(qualification_id (PK,FK), lessons_id (PK,FK))

Again, these tables assume the simplification from Issue 1. Even without that simplification, these tables could be amended to use an analogous approach.

Enforce a constraint problem

Justin Cave's answer here and Tom Kyte's pointed me to a solution using a function based index. I think this can be made even simpler with some more thought but this works now:

CREATE OR REPLACE FUNCTION UNIQUE_START_STAGE (
   phase_id_in   IN NUMBER,
   stage_id_in   IN NUMBER)
   RETURN NUMBER
   DETERMINISTIC
IS
 -- PURPOSE:enforce business logic that a phase can have only one stage where
 -- the disabled field has a value of 0 and IS_START_STAGE has a value of 1

   v_count   NUMBER (9);
BEGIN
   SELECT COUNT (s.id)
     INTO v_count
     FROM STAGE s
    WHERE     S.IS_START_STAGE = 1
          AND s.disabled = 0
          AND S.PHASE_ID = phase_id_in;

 IF v_count = 1
 THEN
     --return the primary key if there is only one
     v_count := stage_id_in;
  ELSIF v_count < 1
  THEN
     v_count := NULL;
  END IF;

   RETURN v_count;
END UNIQUE_START_STAGE;

and then we create an index based the idea that there can only be one child stage that is enabled for a phase that is the start stage

CREATE UNIQUE INDEX unique_start_stage_idx
   ON stage (
      CASE
         WHEN disabled = 1 THEN NULL
         WHEN is_start_stage = 0 THEN NULL
         ELSE UNIQUE_START_STAGE (phase_id, id)
      END);

--and add the same constraint to the other table

CREATE UNIQUE INDEX unique_start_stage_idx2 ON PHASE_FIRST_STAGE ( UNIQUE_START_STAGE (phase_id, stage_id));

This solution partially solves the problem:

it enforces that there is only one entry in STAGE for each value of PHASE_ID where IS_START_STAGE =1 and DISABLED = 0
it enforces this same uniqueness in PHASE_FIRST_STAGE
it does not enforce that an entry in STAGE is also in PHASE_FIRST_STAGE
you could replace the PHASE_FIRST_STAGE table with a view of STAGE that cleans up the last issue

Best Answer

Related Solutions

Unenforced constraints/possible duplication in the database design

Enforce a constraint problem

Related Question