CODEWITHSUNDEEP
databasedatabase-designrelational-databaserelationship
thebenman
thebenman

Reputation: 1621

Modelling 1 to Many relationships that repeats multiple times

I have three tables Initiative, Category and Forecasts. The relationship is 1 to many across all tables.

Initiative can have multiple Category can have multiple Forecasts

Initiative can be either recurring or not based on which the Category names can be different. For example for a recurring Initiative it will have a set of specific Categories that will repeat for all Initiatives of recurring type.

And forecasts will be per category for months of the year. This is how it is currently and the fiddle is here

enter image description here

The thing with this approach is that for each initiative of a particular type the category name is repeated that many times although it is the same.

Another problem with this is when we need to add an initiative of a particular category we will have to maintain another AllInitiative table with all the Initiative and all the Categories to map them initially to the Category table. After which forecasts can be added to the newly added initiative and category.

How do I better design this structure so that I have one AllInitiative table which has all types of initiatives exactly once and an AllCategory table which has a list of Categories for each type of Initiative.

And use another table to keep track of a particular type of initiative and category without duplicating category names across each instance of a particular type of initiative.

Upvotes: 0

Views: 123

Answers (1)

Raul Cuth
Raul Cuth

Reputation: 2469

You can have the Initiative table, with all initiatives once, another one for Category, and for 1-n relationships you need a linking table for example Initiative_Category in which you have foreign keys for initiative and category.

In this table you can store each initiative with the corresponding categories.

    +---------------+-------------------+
    | initiative_id |    category_id    |
    +---------------+-------------------+
    |       1       |         2         |
    |       1       |         3         |
    +---------------+-------------------+

Upvotes: 0

Related Questions