By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,164 Members | 1,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,164 IT Pros & Developers. It's quick & easy.

DB table design Q

P: n/a
I'm building a db to store product monthly profit and loss data. Is it
better to have:
(A) each row represents the product and all of its 30 p&l line items,
or
(B) each row represent the p&l line item

So,
tbl_A:
ProductID, Year, Month, Rev1, Rev2, Cost1, Cost2...

tbl_B:
ProductID, Year, Month, PLID, PLamount

Tbl_A rows would contain null values in some fields. Tbl_B would
contain redundant data in fields (ProductID, Year, Month).

So, the question I guess, is what's a better design? Fewer null values
in a row, or less redundancy?
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
John,

How about ---

TblProduct
ProductID
ProductName

TblPLMonth
PLMonthID
PLYear
PLMonth

TblPLMonthDetail
PLMonthDetailID
PLMonthID
ProductID
PLAmount

Note - PLAmount would be entered as a negative number for losses and a positive
number for profits.

Your data entry form would be a form subform where the main form would be based
on TblPLMonth and the subform would be based on TblPLMonthDetail. The
Linkmaster/Linkchild properties would be PLMonthID. The main form would contain
two comboboxes for picking the Year and Month. ProductID in the subform would be
entered via a combobox based on TblProduct.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com


"John" <so*********@hotmail.com> wrote in message
news:90**************************@posting.google.c om...
I'm building a db to store product monthly profit and loss data. Is it
better to have:
(A) each row represents the product and all of its 30 p&l line items,
or
(B) each row represent the p&l line item

So,
tbl_A:
ProductID, Year, Month, Rev1, Rev2, Cost1, Cost2...

tbl_B:
ProductID, Year, Month, PLID, PLamount

Tbl_A rows would contain null values in some fields. Tbl_B would
contain redundant data in fields (ProductID, Year, Month).

So, the question I guess, is what's a better design? Fewer null values
in a row, or less redundancy?

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.