Is my Table Structure Correct - Or Shold I change?

[andmunn]

Hello All,

I have a database that is used to track certain "metrics" for certain "business units" on a monthly basis. There are 31 business units, and there are 45 possible metrics that we are tracking for these business units.

I have the following relative table structure:

tblDivison - Name of Division
>pkDivID
>txtDivName

tblMetric - Name of Metric we are tracking
>pkMetricID
>fkMetricCategoryID
>txtMetricName

tblMonthlyResults
>pkMonthlyResults
>fkDivID
>fkMetricID
>MetricDate
>MetricValue
>IsImportant (captures whether the unit "captures/records" this metric).

The problem i am having is if i tag "IsImportant" field to no - i want it to be reflective that this metric is not important for ALL months. Right now, it means that that metric is not important only for that specific month.

Would i have to change my table structure around to something like:

tblDivisionMetric
>pkDivisionMetricID
>fkDivID
>fkMetricID
>IsRelevant

and this have the tblMonthlyResults look like this:

>pkMonthlyResults
>MetricDate
>MetricValue

Is this the correct way to do this? Is there some "easier" way withotu changing my table structure?

Any help appreciated.
Andrew.

 

[llkhoutx]

IsImportant should be put in the table which it is limiting. That's probably tbMetric.

 

[gemma-the-husky]

I think you are highlighting your own solution.

If your tables are analysed correctly, then any new piece of information (datum) you need to store should be attributed to a single table

In this case, you can see the pros and cons of different treatments, and it depends on which solution is relevant to you, but it indicates that your analaysis is reasonable, and this is just a design decision

What can happen is that having made a decision, you subsequently change your mind, and in these cases, you need to go back and rework, rather than try and make it fit. Its easier in the long run. You may even decide you want both - ie have a general setting, with a specific override facility.