DB normalization theory question...

[John]

I'm designing a db that will hold financial profit and loss data. I'm
tracking products and their: gross units, marketing costs, sales
costs, etc. The question is, is it better to have one table for each
of these categories where each table represents a category AND year,
or just the category? Let me demonstrate:

Option 1
tblGrossUnits:
Product
Year
Month
GrossUnits

Option 2
tblFY02GrossUni ts
Product
Month
GrossUnits

(each year would need a new table)

Thanks for any help!

[Larry Linson]

Re: DB normalization theory question...

Your first option would be better. You'd have to jump through hoops to query
for multiple years of data with the second approach. You could consider that
the second approach uses the tablename itself as "data" and that isn't good.

Larry Linson
Microsoft Access MVP

"John" <soundneedle@ho tmail.com> wrote in message
news:90fab935.0 401111927.597f1 15c@posting.goo gle.com...[color=blue]
> I'm designing a db that will hold financial profit and loss data. I'm
> tracking products and their: gross units, marketing costs, sales
> costs, etc. The question is, is it better to have one table for each
> of these categories where each table represents a category AND year,
> or just the category? Let me demonstrate:
>
> Option 1
> tblGrossUnits:
> Product
> Year
> Month
> GrossUnits
>
> Option 2
> tblFY02GrossUni ts
> Product
> Month
> GrossUnits
>
> (each year would need a new table)
>
> Thanks for any help![/color]

[James Neumann]

Re: DB normalization theory question...

John,

I would second Larry's advice. Unless you are talking about an
enormous amount of data, the second approach will be an absolute
nightmare. Only if you have so much data that you need to archive it
would it make sense to have the second strcuture. Only once have I
seen a situation where that approach was necessary, and that was in a
database containing trading records of a rather large fund for over
ten years. And even then, I have come to believe that throwing some
better hardware at it would have been a more cost-effective solution.

FWIW, almost every time I have departed from good realtional design in
Access or SQL Server, I've regretted it.

JCN

"Larry Linson" <bouncer@localh ost.not> wrote in message news:<oOoMb.124 94$EL4.8080@nwr ddc02.gnilink.n et>...[color=blue]
> Your first option would be better. You'd have to jump through hoops to query
> for multiple years of data with the second approach. You could consider that
> the second approach uses the tablename itself as "data" and that isn't good.
>
> Larry Linson
> Microsoft Access MVP
>
> "John" <soundneedle@ho tmail.com> wrote in message
> news:90fab935.0 401111927.597f1 15c@posting.goo gle.com...[color=green]
> > I'm designing a db that will hold financial profit and loss data. I'm
> > tracking products and their: gross units, marketing costs, sales
> > costs, etc. The question is, is it better to have one table for each
> > of these categories where each table represents a category AND year,
> > or just the category? Let me demonstrate:
> >
> > Option 1
> > tblGrossUnits:
> > Product
> > Year
> > Month
> > GrossUnits
> >
> > Option 2
> > tblFY02GrossUni ts
> > Product
> > Month
> > GrossUnits
> >
> > (each year would need a new table)
> >
> > Thanks for any help![/color][/color]

[John]

Re: DB normalization theory question...

Thanks for your help, James and Larry.