There are many TEAMS that engage in ACTIVITIES in a many-to-many relationship with TEAM_ACTIVITIES as a joining table. Each ACTIVITY has a set of MARKERS that are used to track each team's progress in the activity Each MARKER belongs to a single ACTIVITY.

Teams choose which activities they are going to be engaged in, then, on a monthly basis, for each activity they are engaged in they report progress against the markers for that activity. This progress is stored as OUTPUTS.

Each OUTPUT belongs to one TEAM and one MARKER and includes a month and a value.

My problem is that I feel there is some redundancy in this structure, namely that the activities in which each team is engaged is modelled by the TEAM_ACTIVITIES table, but also is in the OUTPUTS table, since that joins TEAMS to MARKERS which belong to ACTIVITIES.

That means to keep integrity I'll have to make sure that OUTPUTS only include entries for team-activity pairs that exist in the TEAM_ACTIVITIES table.

The other way to structure this would be to make OUTPUTS belong to TEAM_ACTIVITIES instead of TEAMS but then the redundancy has just shifted since the activity for an output could come through the TEAM_ACTIVITIES table or through the MARKERS table,

Is there any good way to structure this without redundancy, so that I don't have to do extra checking to ensure integrity?

  • 1) Joining tables or bridge tables are not needed to connect only two entities 2) you can have a joining table between two joined tables, or between a joined table and an entity, too. – peterh - Reinstate Monica Aug 21 '18 at 4:11
  • @toby-1-kenobi Can you share an example of what you have, a structure with sample data? also what you are trying to achieve? – Kapil Bhagchandani Aug 21 '18 at 5:42
  • @peterh 1) if two entities are related in a many-to-many way then you do need a joining table between them. 2) yes I know, that's why I said that relating OUTPUTS to TEAM_ACTIVITIES is an option, however it doesn't help with the redundancy problem. – Toby 1 Kenobi Aug 21 '18 at 9:38
  • @Kapil Bhagchandi I'm trying to achieve what I describe in the question except without data redundancy. How would you like me to show you sample data? You want me to draw a diagram or give an SQL dump or what? – Toby 1 Kenobi Aug 21 '18 at 9:41
  • 1
    @toby-1-kenobi you can create diagram and share sample data. – Kapil Bhagchandani Aug 21 '18 at 13: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.