justbob3377 Posted July 20, 2008 Share Posted July 20, 2008 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!!) 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted July 21, 2008 Share Posted July 21, 2008 I'm afraid your examples don't make any sense to me (or have you got them back-to-front?) Can you illustrate with sample data Quote Link to comment Share on other sites More sharing options...
justbob3377 Posted July 22, 2008 Author Share Posted July 22, 2008 I'm afraid your examples don't make any sense to me (or have you got them back-to-front?) Can you illustrate with sample data Sorry, I cross-wired my #'s before. Let me start over with a clearer example to follow... Quote Link to comment Share on other sites More sharing options...
justbob3377 Posted July 22, 2008 Author Share Posted July 22, 2008 Hopefully this is a clearer description of my question/problem... 1ST PURCHASE: I go to Target and buy a CD, Scotch Tape, Toothpaste, and 5 T-Shirts. There is one "Expense" (i.e. Receipt/Order/Invoice) and there are many (8 actually) "ExpenseDetails" (i.e. CD, Scotch Tape, Toothpaste, and 5 T-Shirts). **BLUE denotes data in "Expense" table **RED denotes table in "ExpenseDetail" table So, in this case I would write... ExpenseID = 1 ExpenseDate = 7/19/08 MerchantName = "Target" --------------------------- Description = "CD" UnitPrice = $10 Quantity = 1 ExtendedPrice = $10 ... Description = "Scotch Tape" UnitPrice = $2 Quantity = 1 ExtendedPrice = $2 ... Description = "Toothpaste" UnitPrice = $4 Quantity = 1 ExtendedPrice = $4 ... Description = "T-Shirt" UnitPrice = $6 Quantity = 5 ExtendedPrice = $30 --------------------------- TotalSale = $46 Straight-forward enough, right? 2ND PURCHASE: I then go to SuperCuts and get a Haircut. There is still one "Expense" (i.e. Receipt/Order/Invoice), but it almost seems pointless to create an "ExpenseDetails" (i.e. 1 Haircut) for this "one-off" deal. Yes, I confess that I am thinking from a Forms standpoint more than a Data/Tables standpoint! On the backend, it is a matter of having... ExpenseID = 2 ExpenseDate = 7/20/08 MerchantName = "SuperCuts" --------------------------- Description = "Haircut" UnitPrice = $20 Quantity = 1 ExtendedPrice = $20 --------------------------- TotalSale = $20 versus just having... ExpenseID = 2 ExpenseDate = 7/20/08 MerchantName = "SuperCuts" --------------------------- --------------------------- TotalSale = $20 I guess my logic was, that in the "abbreviated" version, you really don't have a need for "UnitPrice", "Quantity", and "ExtendedPrice" - among other fields - when you know that you got a Haircut at "SuperCuts" for $20. (And actually, what set this off was for all of my Utility bills. In my mind, all I need to see is "PG&E" and "$120" and I know I have an Electric Bill.) See why I started questioning things? Just Bob Quote Link to comment Share on other sites More sharing options...
mbeals Posted July 22, 2008 Share Posted July 22, 2008 I see nothing wrong with your structure, except you will want to add an expenseID reference in the Detail table Using your syntax: ExpenseID = 1 ExpenseDate = 7/19/08 MerchantName = "Target" ExpenseID = 2 ExpenseDate = 7/20/08 MerchantName = "SuperCuts" --------------------------- Description = "CD" UnitPrice = $10 Quantity = 1 ExtendedPrice = $10 ExpenseID = 1 ... Description = "Scotch Tape" UnitPrice = $2 Quantity = 1 ExtendedPrice = $2 ExpenseID = 1 ... Description = "Toothpaste" UnitPrice = $4 Quantity = 1 ExtendedPrice = $4 ExpenseID = 1 ... Description = "T-Shirt" UnitPrice = $6 Quantity = 5 ExtendedPrice = $30 ExpenseID = 1 ... Description = "Haircut" UnitPrice = $20 Quantity = 1 ExtendedPrice = $20 ExpenseID = 2 --------------------------- Yes you may have a single "detail" entry for a single "expense", but that's okay. Quote Link to comment Share on other sites More sharing options...
justbob3377 Posted July 23, 2008 Author Share Posted July 23, 2008 I see nothing wrong with your structure, except you will want to add an expenseID reference in the Detail table Yes you may have a single "detail" entry for a single "expense", but that's okay. But that was the point... I was originally thinking that if I had an Expense (e.g. Haircut) where the Expense = ExpenseDetail, that you could just not create an ExpenseDetail (to save data entry). For example... ExpenseID = 2 ExpenseDate = 7/20/08 MerchantName = "SuperCuts" --------------------------- --------------------------- TotalSale = $20 After talking to someone else, it sounds like that would defeat the purpose of having... (1) Expense ---> (m) ExpenseDetails Follow me? Just Bob Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.