Does my table structure Look ok?

[andmunn]

Hello All!

I want to make sure i've "normalized" my data before i start inputting all these numbers, so here's some background. I am creating a table to track "metrics" (numbers) across various divisions in a company. There are several divisions (31) as well as several metrics (45) we are trying to track.

Currently, i have 5 tables:

tblDivision:
> pkDivID
> fkManagerID
> DivName
> DivResponderFirstName
> DivResponderLastName

tblManager (the contact in case the division above doesn't respond)
> pkManagerID
> ManagerFirstName
> ManagerLastName

tlbMetric
> pkMetricID
> fkMetricCategoryID
> MetricName
> MetricDescription

tblMetricCategory (6 categories that the metrics above fall into)
> pkMetricCategoryID
> CategoryName

tblFigures (where i actually gather the data)
> pkFigures
> fkDivID
> fkMetricID
> Nov
> Dec
> Jan
> etc....for 12 months of the fiscal year.

Does this look like a structure that should work? Am i missing something here? I am by no means an "Access expert", but i find it much easier when i'm designing my forms if my tables make sense!

Thanks for any help.

Andrew!

 

[georgedwilkinson]

No, it will not work for very long. You need to read up on normalization (not an Access topic).

First, don't create a column for each month. What will happen when next January rolls around? The following year? When they decide they want it by week, quarter, hour?

In your Divisions table, you have a FK to the Managers table and then have some responder's name. What happens if you want to track something other than manager and responder and why would you consider tracking the 2 in 2 different ways? What if you have to track the person who serves tea at the division party? What if they decide we're not gonna have responders but instead, we're gonna break it into thissers and/or thatters? Your model will not handle any of these scenarios.

Where are your metrics coming from? If this is a data warehouse/reporting system (not OLTP), you really need a totally different kind of model where normalization isn't as important and you might be happier going with a star or snowflake schema. Not really enough information to tell.

 

[MarkK]

tblFigures doesn't look right. I'd want each date's data in a distinct record.

tFigure -> too generic - figures of what? Sales? Hirings? Degrees?
FigureID
DivID
MetricID
Date
Value

Also, but of lesser impact, DivResponder .... Consider a tPerson table with a Role field ...

tDivision
DivisionID
DivisionName

tPerson
PersonID
DivisionID
RoleID
FirstName
LastName

tRole
RoleID
RoleName -> data like Manager, Responder...

 

[MarkK]

Yeah, what George says. Almost exactly.

 

[andmunn]

First, don't create a column for each month. What will happen when next January rolls around? The following year? When they decide they want it by week, quarter, hour?

Hi George - thanks for your detailed reply - very much appreciated. I actually realized this "problem", however, wasn't sure how i could structure the tables such that they could go on indefinately. This would probably me a much better solution.

In your Divisions table, you have a FK to the Managers table and then have some responder's name. What happens if you want to track something other than manager and responder and why would you consider tracking the 2 in 2 different ways?

To clarify, i'm not really "tracking" the responders / managers, i merely use this field so i know who to contact if i'm missing certain metrics.

Basically, i send a spreadsheet to 30 + different "responders" each responsible for a different division. This spreadsheet contains the 40+ metrics i'm tracking (they are just simple numbers, i.e.// number of apples sold). The manager field is meant that if i dont' receive the statistics for a certain responder for the current month, i could look up that responder's manager email easily...

I agree though - perhaps having a people table would be a better idea, and having roles defined within it?

Where are your metrics coming from? If this is a data warehouse/reporting system (not OLTP), you really need a totally different kind of model where normalization isn't as important and you might be happier going with a star or snowflake schema. Not really enough information to tell.

I have no idea what a snowflake / star schema is... With that said, the "data" i receive is in the form of a spreadsheet, and i populate it (eventually) via a form into this database.

Sample of Excel Spreadhsset Received:

Division: North America

Metric: Nov / Dec / Jan
# of Apples Sold: 1 / 10 / 3
# of Banana's Sold: 2 / 6 / 20
etc..

Hopefully this clears up things - i would receive a similiar report as above for the remaining 30ish divisions in the same spredsheet.

Any help is appreciated.
Thanks,
Andrew.

 

[georgedwilkinson]

This really seems like a data-warehouse/reporting application to me and my normal rules don't necessarily apply.

Your "Figures" tables (probably should be called MonthlySales) should have whatever you need to identify it (division, item sold, quantity, etc.) along with a column for the month and year. Then have a different row in that table for each month. You'll be able to write queries that can pull the data back out summed, averaged, counted, sliced, diced, etc. a lot more easily that way (1 row per 1 discrete reportable event).

 

[andmunn]

Hi George,

For the "MonthlySales" table will the table then look something like this:

Division | Product | Quantity | Month | Year |
i.e.//
North America | Apples | 25 | September | 2009
North America | Oranges | 30 | Spetember | 2009
North America | Apples | 25 | October | 2009
North America | Oranges | 50 | October | 2009

Am i on the right track?

 

[georgedwilkinson]

For a reporting database (no transactions), this seems about right. I don't really spend a huge amount of time worrying about DSS but it certainly seems to be about right. Just remember that you're not working with dates here so date arithmetic/sorting/filtering will not work. You could change that by making your date information into a single date field populated with the first (or last) day of the month.

Also, you CAN (if you want to and it makes sense), use FKs in a reporting database. I frankly would only worry about that if the database is huge.