数据库设计可跟踪一段时间内的进度 [英] Database design for tracking progress over time

问题描述

我有一个看似简单的问题，但我还不太想办法。我正在创建一个数据库设计来存储目标。目标是手动更新的，每次更新目标时都需要输入一个条目。例如：

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:

减掉10磅：

第1天：减掉了1磅。
第3天：减了2磅。
第7天：丢了7磅。

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:

目标表：

GoalId-整数-PK

GoalId - int - PK

UserId =整数-FK

UserId = int - FK

GoalTypeId =整数- FK

GoalTypeId = int - FK

标题-字符串

进度表：

ProgressId-int-PK

ProgressId - int - PK

GoalId-int-FK

GoalId - int - FK

IntervalX -字符串？

IntervalX - string?

时间间隔-字符串？

GoalAmount-字符串？

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?

编辑：

对不起，我会详细说明。间隔X和Y将成为图形上的间隔值。因此，如果X = 1且Y = 10，则x轴将变为1,2,3，...，Y轴将变为10,20,30，...（这是我完全需要找出的最佳方法的实现方式，但目前尚不成熟。

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:

目标示例：

每天读取-布尔值

丢失10磅-整数或浮点数

lose 10 pound - int or float

节省$ 5000-金钱或浮点数

save $5000 - money or float

热门销售配额-浮动

学习一种新语言-字符串？ （不确定最好的跟踪方式）

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

依此类推。.希望这有助于澄清一下

And so on.. Hope this helps clarify a bit

推荐答案

您肯定在正确的轨道上。我唯一可以推荐的另一件事是调查关键价值指标，并在设计中使用这一原理。 KVI（或KPI，在管理中称为KPI）是不同来源的值，这些值会转换为一组通用值，可以使用通用逻辑进行处理。这将有助于评估具有不同类型里程碑的目标的进度，对于复合目标，这是至关重要的一步。我将对此进行详细说明：

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:

目标的定义是在一定时期内达到某个里程碑或里程碑的组合。里程碑是需要具有通用处理值或键值指示符的值。例如，减掉10磅，您可能会有一个减肥的键值类型，将1磅转换为1 KVI。在您希望相互比较里程碑的地方，您可能希望调整权重。例如，我希望变得更健康，精力充沛（目标）。为此，我必须减掉10磅的体重，从饮食中减少糖分，每天至少骑车15英里（里程碑）。比较这些值时，1英里等于1磅。更像30英里。从我的饮食中减少糖分并不容易，但我们称其为KVI无糖天，并给每一天无糖的价值相当于半磅。则KVI为：

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

如果我每天多骑15英里，我可能可以原谅自己一点糖，所以这应该成为里程碑。换句话说，我可以达到我的骑行里程碑的200％，而我的糖里程碑只有75％，并且仍然可以实现我的总体目标。但是，我无法克服这些，仍然希望自己保持健康。因此，我实现此目标的里程碑应该是：

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

例如，您将每个里程碑的上限设置为100％。您可能会心生语法，并掌握10,000个单词，但是直到您花了一些时间讲这种语言之前，您还没有学会它。

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.

通过调整转化表中的权重，您可以开始以对您有意义的方式相互比较目标。我可以承受减轻10磅的负担，但不必这样做，所以我不会在上面加太高的价格。我的朋友卢卡（Luka）超重100磅，并且出于健康原因必须这样做，因此他的KVI值更高。您还可以扩展里程碑的组合方式，以提供目标的进度指示（即，使用所有KVI的总和，任何组件里程碑的平均完成百分比或最小百分比）。

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).

这是我的想法：

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)
)

这篇关于数据库设计可跟踪一段时间内的进度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！