Database design for tracking progress over time

Question

I have a seemingly simple problem, but I can't quite figure out a solution. I am creating a database design to store goals. The goals are updated manually, and I need an entry each time the goal is updated. For Example:

Lose 10 pounds:

Day 1: lost 1 pound. Day 3: lost 2 pounds. day 7: lost 7 pounds.

And then once the total of the pounds reaches the goal amount, that goal is completed. Here is my design so far, but I am seeing some issues with it:

Goals Table:

GoalId - int - PK

UserId = int - FK

GoalTypeId = int - FK

Title - string

Progress Table:

ProgressId - int - PK

GoalId - int - FK

IntervalX - string?

IntervalY - string?

GoalAmount - String?

Is this the best way to track this? Has anyone seen a base schema out there I can build off of to accomplish this?

Another thought I had was maybe use this design for all my raw data, and rely on stored procedure and views to present the data in the way I want it?

EDIT:

Sorry, I will elaborate a bit. Interval X and Y are going to be what the interval values are on a graph. So if X = 1 and Y = 10 then the x axis will go 1,2,3,... and Y will go 10,20,30,... (This is something else entirely I need to figure out the best way to implement, but that's on the backburner for now)

Types of goals are tricky since there are many I would like to do. They will need to be a bunch of different data types:

Examples of goals:

Read daily - boolean

lose 10 pound - int or float

save $5000 - money or float

hit sales quota - float

Learn a new language - string? (not sure the best way to track this one)

And so on.. Hope this helps clarify a bit

Answer 1

You're certianly on the right track. The only other thing I can recommend is looking into key value indicators, and using this principle in your design. KVIs (or KPIs, as they're referred to in management) are values of disparate sources, which are converted into a common set of values that can be processed using common logic. This will help with evaulating progress on goals that have milestones of different types, and for compound goals, this is a crucial step. I'll elaborate a bit on this:

A goal is defined as reaching a certain milestone or combination of milestones within a certain period. The milestone is the value the needs to have a common processing value, or key value indiator. For example, losing 10 pounds, you could have a key value type of "Weight Loss", converting 1 pound to 1 KVI. Where you wish to compare milestones with one another, you may wish to adjust the weights. For example, I wish to become fitter and feel more energetic (goal). In order to do this, I must lose 10 pounds, cut sugar from my diet and cycle at least 15 miles per day (milestones). When comparing these values, 1 mile does not equate to 1 pound. More like 30 miles. Cutting sugar from my diet is not easy, but let's call the KVI "Days without sugar", and give each day without sugar a value equivalent to half a pound. The KVIs are then:

1 pound = 2 KVI
1 day without sugar = 1 KVI
1 mile = 1/30 KVI

If I ride an extra 15 miles per day, I can probably forgive myself a little bit of sugar, so this should be built into the milestones. In other words, I can achieve 200% of my cycling milestone and only 75% of my sugar milestone, and still achieve my overall goal. However, I can't go over that and still expect to feel healthy. My milestone for this goal would therefore look something like:

Lose 10 pounds: KVIType="Weight Loss", Target=20KVI, cap=100%
No sugar for period (let's say 2 weeks):KVIType="Days without sugar", target=14KVI, cap=100%
Cycle 15 miles per day: KVIType="Cycling", target=7KVI, cap=200%

Learning a new language is a good example. This requires learning the grammatical nuances of the language, sometimes a different alphabet, a whole new vocabulary and then tying these all together into daily use. So here's an example:

Learn language grammar = 100 KVI, which you can work as a percentage of a grammar course completed, for example
1000 words vocabulary = 100 KVI
Conversation = 20 KVI

In this example, you would cap each milestone at 100%. You may know the grammar off by heart and have 10,000 words under your belt, but until you've spent some time speaking the language, you haven't learned it.

By adjusting the weights in your conversion table, you can start comparing goals with one another in a way that makes sense to you. I can afford to lose 10 pounds but don't need to, so I wouldn't put too high a price tag on that one. My friend Luka, however, is 100 pounds overweight and has to for health reasons, so he'd have a higher KVI value for that one. You can also expand on how the milestones are combined to provide a progress indication of the goal (i.e. using the sum of all KVIs, average or minimum percentage completed for any component milestone).

This is sort of what I had in mind:

CREATE TABLE KVIType (
    KVITypeId INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
    KVIName VARCHAR(50),
    Description VARCHAR(200),
    Multiplier DOUBLE PRECISION
)

CREATE TABLE Goal (
    GoalId INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
    UserId INT FOREIGN KEY REFERENCES User(UserId),
    GoalName VARCHAR(50),
    GoalStart DATETIME,
    GoalComplete DATETIME,
    TargetKVI DOUBLE PRECISION,
    CurrentKVI DOUBLE PRECISION
)

CREATE TABLE Milestone (
    MilestoneId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    GoalId INT FOREIGN KEY REFERENCES Goal(GoalId),
    KVITypeId INT FOREIGN KEY REFERENCES KVIType(KVITypeId),
    MilestoneName VARCHAR(50),
    Description VARCHAR(200),
    TargetKVI DOUBLE PRECISION,
    CurrentKVI DOUBLE PRECISION,
    TargetDate DATETIME,
    CompletedDate DATETIME,
    Cap INT)

CREATE TABLE Progress (
    ProgressId INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
    MilestoneID INT FOREIGN KEY REFERENCES Milestone(MilestoneId),
    InputValue DOUBLE PRECISIoN,
    KVIValue DOUBLE PRECISION,
    OccuranceDate DATETIME
)

CREATE TABLE User (
    UserId INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
    UserName VARCHAR(100)
)

Answer 2

Would it not work to have something like this:

Goals(Goal_ID, User_ID, Goal_Type_ID, Title, Goal_Amount)
Progress(Goal_ID, Date_Time, Progress)

PK_Goals(Goal_ID)
PK_Progress(Goal_ID, Date_Time)

FK_Progress_Goals(Goal_ID)

Progress can be either a 'since last time' or total amount - it doesn't matter. I'm not really sure what functions interval X and interval Y serve in your design - can you elaborate further on that?

Answer 3

If all the goals are related to numbers.. i.e., reducing pounds, running miles etc .. then sure, you can do it ..

Something like

UserTable Userid(PK) UserName

GoalTable GoalId(PK) GoalExpectedProgress(Int) UserId(FK)

ProgressTable GoadId(FK) Progress_Int

This Progress_Int can be added for the same GoalId & then make sure that it meets the GoalExpectedProgress..

But for goals like finishing a book, purchase groceries etc.. you have to come up with a better plan.