Database Design

[Guest]

I am designing a database for actual, budget, and forecast data. The table
structure for all 3 types is the same. Is is better to have 3 tables - 1 for
actual, 1 for budget, and 1 for forecast or should I have 1 table that holds
the data for all 3 types and add a type field to determine whether the record
is actual, budget, or forecast? Are there pros/cons to each design? Thanks
for your help.

Weste

 

[Guest]

It depends. How often will you need to access all 3 sets of figures and how
often each individually? I'd lean toward using 3 tables. If you have
different people responsible for different functions, it might be better for
the data to be in separate tables so less chance of conflicts. Also multiple
smaller tables will be faster to process than one large table.

-Dorian

 

[Guest]

Thanks. I was leaning towards using the 3 tables too. I appreciate your
input.

 

[Larry Daugherty]

This is a day late viz your design request but I recommend that you
use a variation on your 2nd choice. It is the more "relational"
approach:

One table will list every budget item and will have a complete
description of the item ~tblbudgetItem That way, there is no
possibility that the same item be entered with different names. Any
corrections or notes about the budget item need be entered in only one
place/table.

Thee will be a related table that has records of the various budget
function entries say tblBudgetItemFunction Each of those records
would have field that indicates its function type (there could be a
3rd (lookup) table that lists the functions). Budget Item Function
records would also probably have a date and any notes about that
record.

Doing it as above will yield a good basis for Queries and Reports and
for future application enhancements. It also provides a base that can
be iterated more than once, as many budget processes do. By placing
date fields in the tblBudgetItemFunction records you could track a
single budget term and you could accumulate records over the years and
show trending, etc. for each item that repeats from one budget term to
the next.

HTH