I have the following entities: Producer, Consumer.

A Producer produces Food for the Consumer. 
A Consumer consumes Food produced by the Producer. 

Now I am tasked to design a schema which maps which consumers can eat food produced by each producer.

As an example, I have Producers p1, p2, p3 and Consumers c1, c2, c3.

   c1  c2  c3
p1  N   Y   N
p2  Y   N   N
p3  N   N   Y

where N = No and Y = Yes

Keep in mind that

  1. the number of consumers and producers will keep growing.
  2. the default state of whether a consumer can eat food from a new producer is No

How should I define this schema?


One approach I thought of is to create a table

producer_consumer_allowed (producer_id, consumer_id).

A consumer is allowed to consume food from a producer if an entry for both this producer and consumer exist in this table.

Whenever we add a new producer, we do not need to do anything since the default state of consumers-producers is No.

Whenever we add consumers, we also do not need to add entires into this table since the default state is also No.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.