Merge several data sets into one tabel or not?

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

Hello,

I want to set up a new database with actual sales figures, budgets and
forecasts.

The actuals and forecasts are updated on a daily base and the budgets on a
bi-monthly base. The update method for each data set is different.
Despite the differences there is one communality: All the datasets are using
the same fields, so I was wondering from a design point of view, whether I
should use three seperate tables or merge the three data sets into one table?
What are the pros and cons?

Where can I find more information regarding this subject?

Thanks in advance for your reactions!
 
Hello,

I want to set up a new database with actual sales figures, budgets and
forecasts.

The actuals and forecasts are updated on a daily base and the budgets on a
bi-monthly base. The update method for each data set is different.
Despite the differences there is one communality: All the datasets are using
the same fields, so I was wondering from a design point of view, whether I
should use three seperate tables or merge the three data sets into one table?
What are the pros and cons?

Where can I find more information regarding this subject?

Thanks in advance for your reactions!
Click to expand...

That comes down to a judgment call. How are you going to actually be using the
data? If (as I would speculate, but I don't know your business rules or model)
actual, budget and forecasts are three separate entities, and a record in the
"Budgets" category for a given date should never be confused with an "Actual"
or a "Forecast" for that date, I'd be inclined to use three tables. For one
thing, that will make it a bit easier to create a query joining two or all
three tables to compare data in the three.

You could make a case though that you just need one addtional field in the
record to indicate that this record is Actual, or Budget, or Forecast, and use
a self-join to pull the data together.

Either way will work, but my inclination (especially given the different
update methods) would be to go with three tables.

John W. Vinson [MVP]
 
Back
Top