I'm currently designing a database for concerts, so I have created the following entity-relationship diagram (ERD):

fig 1.: ER-Diagram

Business domain characteristics

A concert can take place on a regular event or a festival. A festival "uses" all the attributes an event has, but it also holds some additional ones. The same goes with festival_concerts and concerts.

As festival_concerts and concerts are processed the same way in some places but completely different in other places, they need to be separated.

The question

Because of that, I planned to store the additional attributes in a second table that uses the concert primary key as primary key too. The other way was to store them in completely seperate tables. If using my plan, I'd like to clean up the ERD so this weird rectangle-relationship doesn't exist anymore. Which way do you recommend?

  • I can't see why you would separate "festival_concert" from "concert". Your assertion "...are processed the same way in some places, but completely different in other places..." just means that you have a concert type and only process concerts based on type. A festival is a type of event, so they should be collapsed also and event also have an EventType field indicating it is a festival. You have created loops because the two objects "festival_concert" and "festival" are subtypes of other Objects. – blobbles Mar 12 '17 at 23:46
  • I reworked it. The rectangle relationship stays. festival_concerts have to be stored differently, because they can relate to stages. A regular concert must not relate to a stage. Storing all concerts in one table could damage the database integrity as well, since concerts could happen on another day as the event, although the event is no festival. – Leonhard Kreissig Mar 20 '17 at 22:32
  • My current way is: event_festival shares the primary key of event in an c:1-Relation, concert_festival shares the primary key of concert in an c:1 Relation, concert has the foreign key "event_id" of event in an cm:1-Relation and concert_festival has the foreign key "event_id" of event_festival in an cm:1 relation. – Leonhard Kreissig Mar 20 '17 at 22:35

Your Answer

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

Browse other questions tagged or ask your own question.