Proper Table Structure for a Hierarchy

[Scott_Hall]

OK I’m dying here.

I’ve built and rebuilt these tables in my database over and over trying to find the best/correct way to do so. Someone somewhere must have done this before, but I’ve gotten so deep into it that I can’t see it clearly any longer.

My team is doing project management on projects that scale from very simple - $5000 and three days of work - to complex…$1.5m and two years of work. When budgeting for these projects the data requirements need to scale in complexity. A small project should not require the scrutiny and data entry that a major project requires. Pretty straightforward.

Base Tables:
tblProjects – the main table
tblBudgets – one Project to many Budgets…uses FK from Projects table
tblStatus - source of Status list
tblPhase - source of Phase list
tblActivity - source of Activity list


A budget hierarchy consists of

• 
  Status
  • 
    Phase
    • 
      Activity

Each level is one to many with the level above it. A Phase can have many activities but an Activity is in just one Phase, etc.

As far as scaling a budget, a simple project may not actually have specific Activities called out, but may end at the Phase (or Status) level.

Where things have gotten complex was trying to manage the additional data. Each level of the budget (Status,Phase,Activity) could have dollars or Start/End dates associated with them, but the values must roll up correctly. If a Phase has four Activities each worth $25 the Phase should equal $100. Dates also shouldn’t break their boundaries. If an Activity is scheduled outside the Phase date above it the Phase should move to the extant. This really goes beyond just a two dates and a dollar figure, as each line has numerous items…systems impacted, PO number, funding released, etc.

So is the budget line item data best flattened into one table that has all the values of Status, Phase, Activity, or three hierarchal tables using FKs? One table is convenient for management and keeping the secondary data fields consistent, but the hierarchy was more intuitive / similar to the visualized data structure…though data consistency was hard.

In the flat data effort I had a tblBudgetData table. In the multiple table approach I had tblBudgetStatus, tblBudgetPhase, tblBudgetActivity. I’m not sure how to move. Each time I think I’ve had it I’ve run into an issue two weeks later about pulling data out or keeping consistent secondary values like money. I think I’m so deep into it that I’m likely overthinking it, but I really need to settle into a reliable structure and move on.

Attaching a quick example database that might clarify my ramble.

Thanks for any help you can offer,

Scott

 

[isladogs]

Nothing attached to

clarify your ramble

 

[jdraw]

see these for ideas
http://www.utteraccess.com/wiki/ind...rarchical_Data_Structure&diff=3261&oldid=3233

https://www.experts-exchange.com/articles/2153/Managing-Trees-and-Hierachies-in-Access.html

 

[plog]

. Each level of the budget (Status,Phase,Activity) could have dollars or Start/End dates associated with them

That doesn't sound right. Please give me an example where the Phase Start/End date wouldn't be the earliest/latest Activity Date attached to it. Also, a Phases budget just be the Sum of all its activities budget? Please provide example data to demonstrate.

 

[Scott_Hall]

View attachment Example.accdb

Sorry just got back.

As for an example:
Project X has a Planning period, Design time, Construction, and Post-Occupancy work.

Those are four of the statuses used, and each has separate events under them. I'll just do a bit to clarify.

Status
--- Phase
------- Activity

Design
--- Development
------- Site Visit $500, 4/22/17
------- Develop Preliminary Plan $1500, 4/25/17-6/1/17
------- Draft Statement of Work $500, 6/15/17

--- Procurement
------- Contracting Review/Authorization
------- Solicit Bids
------- Award Contract

When a user creates an initial draft budget, they don't really know what specific activities might be required for a project, so a quick estimate might just have $10,000 attached to Design.

As they gain more knowledge of the project, more data is added. This could be bookend dates, dollars, etc.

We can roll dollars/dates up since the cost of the line items will total their phase or status...bookend dates of each activity would fall within the bookends of the phase, like MS Project. But if the data is stored in different tables how do you keep that consistent, etc.

Overall Development should have a total budget of $2500 and run between 4/22/17 to 6/15/17. If a user edited the Site Visit to cost more or last longer, the data upstream of it should adjust accordingly.

 

[Scott_Hall]

JDraw,

I've looked at the example files and read the information with them.

I'm trying to picture how I would adopt this table structure since it does sound appropriate for me.

How would I manage the relationship between Status etc?

 

[The_Doc_Man]

So is the budget line item data best flattened into one table that has all the values of Status, Phase, Activity, or three hierarchal tables using FKs?

Yes.

Your auditors and accountants will want the details. Your management will want the summary. Your engineers will want something in between, perhaps broken out by performing group / department.

This is a case for one of the "Old Programmer" rules: Access won't tell you anything you didn't tell IT first (or at least tell it HOW to tell you). The practical translation of that advice is simple: Decide what you want to get out of this beastie and be sure that you are getting data needed to support those outputs. Sometimes that means working backwards from requirements.

I'm leaning towards the full hierarchical structure where each layer adds another key.

Project:
    ProjID (PK), data about the project
Budget:
    ProjID (FK), BudgID (either PK or member of compound PK), information on budget
Status:
    ProjID (FK), BudgID (FK), StatID (PK or member of compound PK), information on status
Phase:
    ProjID (FK), BudgID (FK), StatID (FK), PhasID (PK or member of compound PK), information on phase
Activity
    ProjID (FK), BudgID (FK), StatID (FK), PhasID (FK), ActID (PK or member of compound PK), information on activity.

It doesn't matter if you have standardized statuses, phases, or activities that can be plucked from a list. The code for that individual items could be just a code number taken from a list of options (and thus offer a secondary or translation relationship to identify details about the item in question). This is where queries and relationships will save your hide. They can build the joins for you if you define the relationships correctly.

If you relate each record type to its hierarchical parent then a bunch of queries involving a GROUP BY clause will help you evaluate things more quickly.

For instance, let's say you wanted to detect a line-item that has a date after the parent item's projected end date. You would include a Yes/No field in EACH item so that you could run tests on dates. For example,

' to test for date violations and flag the offending line item

UPDATE tblBudget INNER JOIN tblStatus ON 
    (tblBudget.ProjID = tblStatus.ProjID) AND
    (tblBudget.BudgID = tblStatus.BudgID)
        SET tblStatus.DateErrorFlag = True
            WHERE tblStatus.EndingDate > tblBudget.EndingDate ;

' to generate a rollup budget based on statuses

SELECT ProjID, BudgID, SUM(DollarValue) As StatusDollars 
    FROM tblStatus GROUP BY ProjID, BudgID ;

Lot of typing to be had, perhaps, but strict hierarchies lend themselves to this kind of joining pretty easily. (Easy - but tedious.)

 

[Scott_Hall]

Thank you that is a very helpful explanation. I had never really thought about having an all purpose y/n field like that.

For the structure, are you indicating that each level would be its own table (5 data tables and some value tables), or have one data table with all of the foreign key fields in it? Sorry, I'm just trying to picture this. For some reason my brain does not want to visualize this.

 

[Scott_Hall]

View attachment Example.accdb

I updated my example dB to start trying to apply your description, but I think I'm off track somewhere

 

[The_Doc_Man]

I am not talking about one table. From the context of your discussion, the "properties" of each level differ and at least some of them are predictable elements. Like in Design status you have Development and Procurement phases, which have comment elements that represent either roll-ups from or limits to the items of the next level down in your pyramid.

Also you might wish to have more than one marker depending on how many different things you can test for compliance at each level. This is up to you, of course, but there is no reason you couldn't have a cash flag and a schedule/date flag just based on the two things you named earlier.

Normally I would NOT recommend this structure but I have worked in an environment that was at least similar. It IS possible to have roll-ups of the information at the next lower level, but you stated that sometimes you put estimates in a record and see if you can make the next level down conform to that number, so the roll-up would be a comparison to the original estimate. Therefore, normalization is not being violated because the two items you would compare differ in the time at which they were created (and in the method of creation as well since one is an estimate and one is a measurement.)

Unfortunately, due to issues I've had in the past with downloading other people's samples, I don't download databases. Other folks here might do so but once bitten, twice shy as the old phrase goes.

 

[plog]

I did look at your structure and it is incorrect. I'm basing that on my knowledge in general, not on my understanding of your database (which I don't have). I am looking at your relationship tool and the key violation I see is multiple paths between tables.

For example, between tblBudgetStatus and tblActivities I can trace 4 paths. that is incorrect, there should only be 1 way to get from tblBudgetStatus to tblActivities. I don't know what that path should be in your instance, but there should only be one.

Again, I think you should budget only at the lowest level (tblActivities) and then roll it up from there whenever you want to see the budget at a higher level.

Lastly, the concept I cannot understand is your Status. You refer to Status as an object in your database (putting it among Phase and Project and Activity), but to me Status is an attribute. It seems to be outside those objects and modifying them: A Phase can be in X status, a Project is in the Y status, etc.

That makes it seem like a Status should also be applied at the lowest level (Activity) and then rolled up similar to the budget.

 

[Scott_Hall]

Thanks for the replies! A lot of my trouble with this are the constraints the company wants...they of course want easy scalable data collection that takes little work and provides excellent metrics. I am also stuck with the terminology given to me, so I understand the confusion, particularly with Status.

To this organization, activities are specific events like "install HVAC unit", which happen during a phase of the project like "construction", which are grouped into categories they call a status.

The concept behind this database is that we need scalable project tracking…in some cases from a one line budget, all the way up to a few hundred line items, ditto for dates.

We may have a project that simply needs to cut a check for $1000 and the user enters a brief date period and that single dollar figure.

The next project though, may require very detailed accounting for compliance scrutiny, outside accounting audits, etc., so the entry system needs to be able to scale deeper.

Poor terminology aside, many PM packages allow this kind of data collection, but how do you properly build a data structure for it?

The end game for management is:
• The ability to pull up a list of projects and see “expected cost vs actual cost”, “expect timeframe vs actual”.
• The list of projects should have a “status” field that is an indication of where the project is in its lifecycle.
• The ability to drill into a project so that failure points can be identified and examined.
• The ability to stage gate activities with approvals.

The structure that got defined (Status/Phase/Activity) basically represents that scaling of details. A simple project enters values at the Status level and goes no further. A single (or a very small amount) line to contain a cost figure and some dates.

A very complex project would need all specific work mapped down to the Activity level with dates and dollars all along the tree.

Attached is a screenshot of the current structure.
tblStatus, tblPhase, tblActivitity are just data source tables for field names...status ID 3 is "Construction" for example. Those IDs are saved into the tblBudgetStatus for a project. Not all projects will have all statuses, etc.

 

[Scott_Hall]

That makes it seem like a Status should also be applied at the lowest level (Activity) and then rolled up similar to the budget.

One of my attempts was done this way, but was rejected by management because it required users to log even simplistic projects down to a high level of detail. Some projects we really don't know (or care) about specific activities, just that it gets done.

 

[plog]

Sorry to keep posting the same idea, but I feel you may not completely grasp that it will accomplish what you want. Let me demonstrate it with data using just Project->Phase->Activity to track budget and deadlines for 2 projects--one with multiple activities, one with just one activity:

tblProject
ProjID, ProjDescription
1, Pay 2016 Property Taxes
2, Build Tool Shed

tblPhase
PhaseID, ProjID, PhaseDescription
1, 1, Pay 2016 Property Taxes
2, 2, Design
3, 2, Order Materials
4, 2, Construction

tblActivities
ActivityID, PhaseID, ActivityDescription, Deadline_Due, Deadline_Actual, Cost_Budget, Cost_Actual
1, 1, Pay 2016 Property Taxes, 12/31/2016, 12/21/2016, 3800, 3815.46
2, 2, Get Customer Specifications, 1/1/2017, 1/8/2017, 100, 37.50
3, 2, Send to architecture firm, 1/8/2017, 1/10/2017, 1500, 1407.99
4, 2, Finalize plans and permits, 2/1/2017, 1/31/2017, 450, 512.37
5, 3, Order foundation, 2/1/2017, 1/31/2017, 2300, 2219.47
6, 3, Order lumber, 2/1/2017, 1/31/2017, 800, 1108.12
7, 4, Foundation pour, 3/1/2017, 3/14/2017, 600, 776.88
8, 4, Frame building, 3/8/2017, 4/1/2017, 1600, 1522.77
9, 4, Roofing, 3/15/2017, , 500, ,


Now, when I want to report on budget or deadlines I just roll up everything from the Activities table. It works no matter how in-depth a project is.

 

[Scott_Hall]

I've get what you are going for - doing all the dates and funds at the activity level rolls up fine, and it does in my current dB.

I think the issue is more of a perception problem from my users. The PM doing your first example is not willing to expand the Phase down to that single Activity. I kid you not, that is a constraint placed on me. They want to type in their $3800 tax bill without diving into any hierarchies...that means not expanding down to the Activity.

The user will never click the "+" to expand the Phase, they just want to add money at that higher level. Your example is good but imagine not being able to use the "Activity" word for that first budget.

I could have some placeholder Activity automatically added (but the user doesn't see it) and store the money there anyway...the user sees what they want, but the data is stored in this manner. Maybe by doing the form unbound and saving the data through code rather than binding to fields.

 

[nhorton79]

Hi, been following this thread and though I would add an idea which may help with using a rough typed in estimate vs a total of the underlying "estimates".

I have developed a database very similar to the one you are talking about.

What I did was have a checkbox chkEstimate and a field Estimate at the Project level which if the checkbox was ticked it would allow the user to enter a figure to the textbox on screen, which would get saved back to the field.

When the form loaded, it would do a check of whether the checkbox was checked and if so change the record source to the Estimate field, but if not checked it would total up the estimates of the underlying records. It wouldn't save this to the Estimate field though. I just had a function that would take the ProjectNum and total everything associated down the line to save writing code over and over.

Others may say it might not be the best way to achieve this, but it worked and allowed those simple projects to be entered super quick.


Sent from my iPhone using Tapatalk

 

[Scott_Hall]

That is an interesting idea. That is simple but covers the scaling consistency issue. I was trying something similar once...if no values were found at a hierarchy level, ignore it and use the numbers above.

The other thing I am doing at the moment is playing with a treeview demo because the underlying data structure (which is self-join based) seems solid. I may or may not use the control since it is Active X, but the structure seems good. This was mentioned back near the beginning of the thread.