stuck on design for 2nd and 3rd level of detail for goals

Question

stuck on design for 2nd and 3rd level of detail for goals

bubberz

Valued Member

Points: 52
More actions
December 21, 2007 at 9:17 pm

#177659

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:
1. Goal_ID
2. Detail_Level_ID
3. Detail_Level_Desc
4. 2ndLvlDetail_ID
5. 2ndLvlDetail_Desc
6. 3rdLvlDetail_ID
7. 3rdLvlDetail_Desc
What I'm wondering is how do I link the activities? Should I just have a 3rd table, "activities", and it would have:
1. ActivityID
2. ActivityDesc
3. LinkID
...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.
Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

There are ways to query an hierarchy and they get easier all the time, but if you know there are up to 3 levels, I'd build 3 tables, linking each to the parent above. In the grandchild, you might even choose to include the parent ID to make querying quicker.

Use a separate activities table, tie it to the appropriate level. You could use GUIDs for all surrogates keys and you wouldn't have duplicates, you could include all 3 IDs in the activities table, but I wouldn't recommend that.

You could also make a separate activities table for each level. That brings some simplicity to the design and it's easy to build against without any confusion of data and which children go to which table. I'd probably do this .

bubberz Valued Member Points: 52 More actions · Answer 2

Thanks for the reply!

I'm concerned about scalability, and if they decide they wanna throw in more levels of detail all of a sudden.

What do you think about:

create table Category

(CategoryId int IDENTITY(1,1) PRIMARY KEY,

CategoryDescription nvarchar(50),

CategoryParentId int);

CREATE TABLE Item

(ItemId int IDENTITY(1,1) PRIMARY KEY,

ItemDescription nvarchar(50),

CategoryId int);

So, the category table would describe if it's a detail, 2nd level detail, 3rd level detail, etc...and link back to the parent table.

..and the Item/Activities table would be the activities linking back to the category table?

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

Then, it should be a single table. If you make it an "adjacency" (parent/child) model, you may be ok depending on how you're going to drill down and how many rows there are. The problem with the "adjacency" model is that you're recalculating the "path" every time you call on the same box in the hierarchy.

You might want to consider Joe Celko's "Nested Set" model... makes for some nasty fast and very easy queries that require absolutely no recursion. See the following... if you like the idea, consider buying the book where he get's into much more detail...

http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bubberz Valued Member Points: 52 More actions · Answer 4

Thanks for the reply!

I really don't see the parent table, listing the "goals", getting more than 200 rows....1000 in a year or two.

It's an important reporting database, but won't get very large at all.

The child table, could have at least 20-30 rows per parent.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

Then take a look at "recursive CTE" in Books On Line for 2k5 and look for "Using a recursive common table expression to display a hierarchical list". They've got some pretty good hierarical examples although recurrsion is comparatively slow compared to Celko's nested sets...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChiragNS One Orange Chip Points: 26137 More actions · Answer 6

Hi

I have some hierarchial data that go to many levels (not 3 or 4 levels). Usually I keep them in a single table.

Iam using recursive CTE's for querying and till now havent come accross a problem with this. Of course i make SURE that the CTE DOES NOT HAVE TO PROCESS MANY ROWS.

"Keep Trying"

bubberz Valued Member Points: 52 More actions · Answer 7

Hello everyone!

I created a test schema with:

1. Parent/main table

2. Child/details table

3. Item/Activities table

I did this because all data coming into the database is in one of three categories:

1. A goal which goes in the parent table

2. A detail which no matter what level goes in the Child/details table

3. An activity associated to a detail

I've populated with test data as to customer's requirements, and have been able to successfully report off the data.

Thanks for all your help, and I will definitely keep the recursive single table idea in mind for future applications.