Unanswered: stuck on 2nd and 3rd level detail design
So, the main objective for this database/web application is reporting.
The issue/situation is:
1. The customer has 10 goals
2. Each goal has at least some detail shown in bullet points via a word document...this is how we'll get the data at first. we're going to develop the UI of the web application after the customer has more specs...main objective, get web reporting!
3. Each detail could be broken down into a further / 2nd level of detail
4. The 2nd level of detail could be further detailed by a 3rd level
...and, not knowing how in depth they'll provide the detail, there will be activities associated to points 2, 3, or 4...depending on how deep/specific the detail.
So, you might have one goal of "build a fence". And this goal only goes to the first detail level of say, "make it stucco" and that's it...no other detail.
A second goal might be, "landscape the front yard", and it's broken down into further levels of 2nd and 3rd:
1. Make stepping stones
**Three 2nd levels of:
--1. Make path curvy
--2. Use flagstone
--3. Lead up to front door
2. Plant trees
**Three 2nd levels of:
--1. Use 5 Aspens
--2. Use 2 dogwoods
--3. Use 1 maple
3. Fill in bare spots
**Two 2nd levels of:
--1. Use bermuda
*****example of 3rd level
--2. Ensure dirt is fertilized
----1. Use Miracle grow
----2. Must be via hose, not granular
Now the tricky thing I'm stuck on is that be it the 1st level of detail, the 2nd, or the 3rd level, each could have activities. So, if the detail stops at the 1st level, activities will be tied to the 1st level. If detail stops at the 2nd level, activities will be tied to the 2nd level...and so on.
I was wondering about a detail table coming off the 10 goal/master table which would have columns of:
What I'm wondering is how do I link the activities? Should I just have a 3rd table, "activities", and it would have:
...and LinkID could be either tied to Detail_Level_ID, 2ndLvlDetail_ID, or 3rdLvlDetail_ID
This way I'm not too sure about referential integrity via the DB diagram to cascade deletes & updates because Detail_Lvl_ID could be non-unique.
Another option would be to have 4 tables:
1. 10 goal main table
2. 1st level of detail table
3. 2nd level of detail table (links to 1st level)
4. 3rd level of detail table (links to 2nd level)
..and the activites table would now have to be split into three different, i.e. DetailAct, 2ndDetailAct, 3rdDetailAct.
Right? Any suggestions are welcome.
Like I said, reporting is the main concern at first, and the customer wants to be able to either show/hide activities, so sub-reporting to the detail, 2nd lvl, 3rd level for the activities shouldn't be too bad.