Home » Developer & Programmer » Designer » When OrderDetails is Zero (any)
When OrderDetails is Zero [message #335065] Sun, 20 July 2008 11:40 Go to next message
justbob3377
Messages: 5
Registered: July 2008
Location: Bay Area
Junior Member
Here is something I've been pining over...

Let's say you have a database with an Expense and ExpenseDetail table, respectively.

And, let's say that there are two types of data entry forms:

Form #1: Expense-level only
Form #2: Expense-level + ExpenseDetail-level

For most things (e.g. Groceries, Retail Purchases, etc), you would want to use Form #1 because you likely purchase many "Items" for a given trip to the store (i.e. "Expense").

But, for some "one-off" things (e.g. Haircut, Shoe Shine, Gas Pump Receipt) you could use Form #2 to streamline data-entry.

(Why enter an ExpenseDetail for a Haircut when you only ever get one Haircut at a time - unless you have multiple heads!!) Razz


Now for the question...


While such a design wouldn't break any Data Modeling Rules - as far as I know - it could cause conflicts when you query data!!

For instance, if you have a total of 10 purchases, and all 10 have Expense-level data, but only 6 have Detail-level data, then if you queried table ExpenseDetail to find "Total Expenditures" you would get incorrect results.

(If you queried "Total Purchase Amount" in table Expense, however, you would "catch" all 10 purchases and find "Total Expenditures".)


Is this a problem?

Can a person just be mindful of this when you create queries/reports, or is this a "Design Flaw"??



Just Bob
Re: When OrderDetails is Zero [message #335066 is a reply to message #335065] Sun, 20 July 2008 11:46 Go to previous messageGo to next message
Frank
Messages: 7877
Registered: March 2000
Senior Member
First off: I think you mixed up the reference to Form 1 and 2 in your grocery-haircut example.

For your question: Not 100% sure if I get your problem, but this sounds like the perfect place for an outer join.

[Edit: on second thoughts/read: I think you should enter the details for the single-item expense. If you are just thinking forms, you can add it automatically, in the background.
So I guess my answer is "yes, it looks like a design flaw"]

[Updated on: Sun, 20 July 2008 11:48]

Report message to a moderator

Re: When OrderDetails is Zero [message #335067 is a reply to message #335066] Sun, 20 July 2008 12:05 Go to previous message
justbob3377
Messages: 5
Registered: July 2008
Location: Bay Area
Junior Member
Frank wrote on Sun, 20 July 2008 09:46
First off: I think you mixed up the reference to Form 1 and 2 in your grocery-haircut example.


Yah, you are right, I screwed that up! Embarassed


Quote:
[Edit: on second thoughts/read: I think you should enter the details for the single-item expense. If you are just thinking forms, you can add it automatically, in the background.
So I guess my answer is "yes, it looks like a design flaw"]


Ugh! So it is a bad design to not enter an ExpenseDetail for every Expense?

Is that for the reason I was trying to point out?


Just Bob

[EDITED by LF: fixed [quote] tags]

[Updated on: Sun, 20 July 2008 14:09] by Moderator

Report message to a moderator

Previous Topic: Data Modeling Question
Next Topic: NGO ER
Goto Forum:
  


Current Time: Tue Jul 29 22:59:17 CDT 2014

Total time taken to generate the page: 0.09729 seconds