Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Jul 2008
    Posts
    32

    When OrderDetails is Zero

    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

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    This question has NOTHING to do with Oracle & is off-topic for this forum
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    I have moved this out of Oracle as Anacedent kindly suggested.

    Just because you have 2 different forms to facilitate different usage scenarios doesn't mean you need 2 different underlying database designs. You can always record the single expense detail from Form #2 in the ExpenseDetail table.

  4. #4
    Join Date
    Jul 2008
    Posts
    32
    Quote Originally Posted by andrewst
    I have moved this out of Oracle as Anacedent kindly suggested.

    Just because you have 2 different forms to facilitate different usage scenarios doesn't mean you need 2 different underlying database designs. You can always record the single expense detail from Form #2 in the ExpenseDetail table.
    I wasn't trying to have two different design models, but I was/am unsure if there are two different data scenarios.

    Here is an example...

    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

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    If it helps, imagine that one one occasion the barber uses a lotion you particularly like and then offers to sell you a bottle. In the UK they used (before my time) to say "Something for the weekend, sir?" - meaning contraceptives.

    Yes, the master/detail table design may be "overkill" for some scenarios, but it is simpler than having 2 designs (e.g. simpler to sum total expenditure as you pointed out), and there is no reason why it should burden your user with complications (you can have 2 forms if it helps them).

  6. #6
    Join Date
    Jul 2008
    Posts
    32
    Quote Originally Posted by andrewst
    If it helps, imagine that one one occasion the barber uses a lotion you particularly like and then offers to sell you a bottle. In the UK they used (before my time) to say "Something for the weekend, sir?" - meaning contraceptives.
    HEY, you keep your lotion to yourself!

    (Who woulda ever thunk that Database Design and Birth Control could be related?!)


    Quote Originally Posted by andrewst
    Yes, the master/detail table design may be "overkill" for some scenarios, but it is simpler than having 2 designs (e.g. simpler to sum total expenditure as you pointed out), and there is no reason why it should burden your user with complications (you can have 2 forms if it helps them).
    Far enough. Mea culpa!

    Okay, some progress!


    (Hey, it looks like I can finally post attachments?!)

    Attached are 3 "mockups":

    #1 - Grocery purchase using my current Form/SubForm layout.

    #2 - Haircut using my current Form/SubForm layout.
    ===>> What I am trying to avoid.

    #3 - Haircut using just a parent Form, but with SubForm fields.
    ===>> What I think "andrewst" is pointing me towards?!


    Is that what you were saying, Andrew?


    Just Bob
    Attached Thumbnails Attached Thumbnails 01_ExpenseForm_Groceries_SubForm.gif   02_ExpenseForm_Haircut_SubForm.gif   03_ExpenseForm_Haircut_NoSubForm.gif  

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Yes, that's what I meant.

  8. #8
    Join Date
    Jul 2008
    Posts
    32
    Quote Originally Posted by andrewst
    Yes, that's what I meant.
    So how would I go about implementing that?

    (Am I now going to get yelled at by "anacedent" for asking an Oracle/SQL question in a "Design" forum?!)


    When the user clicks "Submit" on a single form similar to the one I posted before, would I fire off SQL code similar to this...


    Code:
    INSERT INTO Expense
    (ExpenseID, ExpenseDate, MerchantName, Employee, TotalSale)
    VALUES
    (<someSeqNo>, '9/9/2009', 'SuperCuts', 'Jack-the-Ripper', $20);
    Code:
    INSERT INTO ExpenseDetail
    (ExpenseID, Description, category, SubCategory)
    VALUES
    (<ValueOf_ExpenseID_Above>, 'Haircut', 'Service', 'Personal');
    And then that way I would be able to "streamline" certain forms for things like Haircuts while still correctly writing the RIGHT Data to just ONE Database Design, right?

    Is that what you were getting at, Andrew? (Oops, I mean Mr. Ton Andrews!!)


    Just Bob

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    its a single purchase.. but its still a purchase
    the fact that you are buying one haircut is irrelevant.. you are still buying a quantity of a good or service.
    arguably you could buy one haircut, or many (you could get a promotion prep pay, you could be getting you and your children's hair cut)

    you could have a discrete form which only allows for single purchases.. but why bother.. its a purchase.

    as I see it whetehr you buy one pc (or 10 Pc's) or one supercomputer, there is not substantive difference int he purchasing process in system terms.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Jul 2008
    Posts
    32
    Quote Originally Posted by healdem
    its a single purchase.. but its still a purchase
    the fact that you are buying one haircut is irrelevant.. you are still buying a quantity of a good or service.
    arguably you could buy one haircut, or many (you could get a promotion prep pay, you could be getting you and your children's hair cut)

    you could have a discrete form which only allows for single purchases.. but why bother.. its a purchase.

    as I see it whetehr you buy one pc (or 10 Pc's) or one supercomputer, there is not substantive difference int he purchasing process in system terms.
    Thanks for the reply. All valid points. But allow me to explain where there is a difference between "theory" and "application" and where my concern/problem/confusion arose...

    Yes, in retrospect, you are 100%, and it is funny that I got confused.

    But here is the issues that created this post...

    1.) I'm not used to working with "real" databases - read non-MS Access.

    And even though I went back to school in 2002 to study Oracle and learn how enterprise databases work, and how to do "good" database-design, it seems that ending up not working as an Oracle Developer/DBA, and instead going into Project Mgt/Business Analysis, combined with using MS Access at home for the last 12+ years has rotted my brain!!

    While seductive, most users (and non-users) of MS Access don't realize how much MS Access perverts how you do things versus how you SHOULD do them.

    Because everything in MS Access uses "bound" Forms/Controls where you never "write" to the table(s), you just type data in like a spreadsheet, it clutters one's understanding of Creating/Reading/Updating/Deleting records.

    And since I am used to "One Form for All Data", I didn't realize I could create a different, single Data-Entry Form that contained both "Expense" and "ExpenseDetail" fields, and then just use SQL to write that "disparate" data to multiple backend tables. (You couldn't do that with an MS Access Bound Form!!)

    So that, and lots of other accumulated "bad habits" was one reason for my screwy thinking.

    But I digress...


    2.) I am entering thousands of historic receipts - by hand - and LOTS of them are one-off's. I wanted to streamline Data Entry.


    3.) Moving forward, because a lot of my bills will follow this one-off pattern, I figured it would be silly to have to entered "Description", "UnitPrice", "Quantity", "ExtendedPrice", etc. for my Electric Bill when all I really need is MerchantName = 'PG&E' and TotalExpense = <some value>.

    It seemed superfluous to have to deal with "ExpenseDetails". (Especially since I will be using "Sub-Types" and I don't want to enter a Quantity = 177.5 KWH.)

    Actually, that is one thing that confused me, and maybe still does...

    I say "Quantity" is ONLY for "Quantity of Products" (or maybe "Quantity of Services"), but not as a catch-all for units. It is an Integer field designed to capture a pretty clear thing.

    By contrast, I say that something like "TotalGallonsOfGas", "TotalKWH", or "TotalTherms" would better reside in a Sub-Type Table, or at least a field that handles Real values.

    I guess it could go either way, but I don't like the idea of plopping any "quantity" in the "Quantity" field.

    "Bob" and "California" are both names, but you wouldn't want to put them in the same field, right?

    "$27.50" and "$1.95" are both Dollar Amounts, but because the first is an Order Total and the second Sales Tax, they shouldn't be in the same field.

    There is also a flaw in putting KWH under "Quantity"...

    My electric bill is comprised of not just KWH, but numerous other components. So to enter KWH in "Quantity" kind of implies that the anything you are buying/paying for is the KWH, which isn't entirely true.

    Anyways, that is how I see it on this particular point.


    Hey, in the end, I know what I know, and if I had all of the solutions I wouldn't be here!



    Just Bob

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    access is a chameleon.. it can be a spreadsheet, it can be a db front end.

    when you buy something the actual transaction
    is an overall purchase comprised of what ever detail elements are purchased.
    that could be one item it could be more than one oitem, it could be muliples of any / or of those items

    any item may attract sales tax (in the UK we have whats called VAT which has a different rate depending on the type of goods/servies bought.. the rate can be exempt (no sales tax) or a percentage of the sales price.

    so in my books a detal sale receipt should include
    the details of the sales transaction (eg date, time, purchaser etc...)
    the details of the goods or services bought (eg quantity, price, discount (if applicable))
    personally for accounting purposes I also record the sales tax per item (arguably you ould handle this using soem for of change control to track when a price changes and or when a tax rate changes.. However In my books its to easy to circumvent.. for auting purpsoes I prefer to record permanently the circumstances prevailing at the time the purchase was made (and therefore the transaction legally occurred and the tax point)

    there may also be other elements attached to an order eg delivery or standing charge).. effectively you are still buying a service in delivery or standing charge. whether that is the same for all sales (eg the same delivery charge per transaction, or the same standing charge per electrcity user or a banded one (ie houses of this type all are on charge X, that type charge Y, or each property is on a unique charge is largely irrelevant)
    but effectively you have bought
    n Kwh of power at &#163;y.zz per unit
    PLUS any standing charge
    PLUS any sales tax

    if you buy fuel your are quoted a price per litre/gallon but you don't neccesarily buy in whole units

    some sales may have a minimum charge or quantity.. ferinstance some companies have a minimum invoice amount of &#163;10... irrespective of what you buy.. buy a 5p widget y'get charged &#163;5
    some have a fixed charge per invoice eg..... delivery
    some have a minimum order value.

    but ultimatley it depends on what your business requirement actually is. if you are not Sales Tax registered then arguably you dont need the sales tax elements.
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Jul 2008
    Posts
    32
    Quote Originally Posted by healdem
    access is a chameleon...
    Too nice of an animal!!


    Again, you make interesting points.

    Okay, so I buy into the Haircut is a purchase with a UnitPrice and Quantity. And now that my brain is slowly detoxing from Bound Forms, I see that I can create customized forms with data that gets written to multiple tables, no problem.

    However, I'm not sure I buy in to your thoughts on Quantity.

    Physically, putting NoOfItems, NoOfKWH, NoOfGallonsOfGas can all fit in the Quantity field - assuming I change it to a Real data-type.

    But logically, does this make sense?!

    To me, not really.

    Apples to Apples and Oranges to Oranges.

    So, since we are discussing this tangential point, is there a problem if I did this...


    Expense (super-type)
    -----------------------
    ExpenseID (PK)
    ExpenseDate
    MerchantName
    TotalSale ---> $88.61

    Fuel (sub-type)
    --------------------
    ExpenseID (FK)
    FuelType ---> "Unleaded"
    TotalGallons ---> 18.655
    CostPerGallon ---> $4.75


    ExpenseDetail (child to Expense)
    ---------------------------------
    ExpenseDetailID (PK)
    ExpenseID (FK)
    Description ---> "Gasoline"
    UnitPrice
    Quantity ---> 1 or Null
    ExtendedPrice ---> $88.61


    Maybe this is loopy, I don't know?!


    But, in my mind, it accomplishes this...

    1.) Now have a Child Record
    2.) Gallons are in their own "logical" field and not mixed in with "Quantity"
    3.) There is a $$ Amount that is the same as "TotalSale", so regardless of how I query things, I would get the same results.

    Of course, here would be another approach...

    Expense (super-type)
    -----------------------
    ExpenseID (PK)
    ExpenseDate
    MerchantName
    TotalSale ---> $88.61


    ExpenseDetail (child to Expense)
    ---------------------------------
    ExpenseDetailID (PK)
    ExpenseID (FK)
    Description ---> "Gasoline"
    UnitPrice
    Quantity ---> 1 or Null
    ExtendedPrice ---> $88.61


    ExpenseDetail_Fuel (sub-type to ExpenseDetail)
    -------------------------------------------------
    ExpenseDetailID (FK)
    FuelType ---> "Unleaded"
    TotalGallons ---> 18.655
    CostPerGallon ---> $4.75


    Now the Sub-Type table is at the "Details" level which might make more sense?!

    Simply my thoughts...



    Just Bob

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    traditionally I'd expect something like

    Sales
    ID
    Date
    CustomerID


    SalesDetail
    SalesID
    ItemID 'depending on how you do it.. it can either be an autonumber column, or it could be an ascending number within an order
    ProductID
    Quantity
    Price
    VAT/Sales Tax

    from the quantity & price & sales tax elements you can generate the invoice
    ok so there is some duplication here..
    I prefer to store the complete details of the sales transaction as is.... it makes regenerating invoices or providing the audit trail fairly trivial. with disk space being pretty cheap it seems daft to me to be creating elaborate normalsed models in this field

    intrinsically it doesn't matter if the item sold is fuel, supercomptuers or haircuts. y'make a sale, that sale is with a customer, that customer may come to the invoice point with one or more products, and may have one or more of each product... heck someone somewhere may decide to by more than one supercomputer....
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Jul 2008
    Posts
    32
    Quote Originally Posted by healdem
    intrinsically it doesn't matter if the item sold is fuel, supercomptuers or haircuts. y'make a sale, that sale is with a customer, that customer may come to the invoice point with one or more products, and may have one or more of each product... heck someone somewhere may decide to by more than one supercomputer....
    But to be clear, I am building a "Personal Financial System", so I want to keep my widgets in some logical fashion!

    Ny fear is that if I store ItemQuanities, GallonsOfGas, TotalKWH, etc in one field that it will be confusing as hell pretty soon when I start doing data analysis.

    Again, it is the same reason why you store FirstName, LastName, City, State, FavoriteColor, MaidenName, Gender, etc in seperate fields, right?!

    The bigger questions is, am I creating a "Data Quagmire" if I choose one of the two models above??


    Just Bob

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by Bob Just
    Ny fear is that if I store ItemQuanities, GallonsOfGas, TotalKWH, etc in one field that it will be confusing as hell pretty soon when I start doing data analysis.

    Again, it is the same reason why you store FirstName, LastName, City, State, FavoriteColor, MaidenName, Gender, etc in seperate fields, right?!
    Wrong.
    Firstname, Lastname, etc, are all separate attributes and each record may require several, if not all attributes.
    If you store ItemQuantities, GallonsOfGas, and TotalKWH in a single column "Quantity", you are not going to run the rist that a single record will need to store both Gallons Of Gas and Total KWH.
    Why are you continuing to post these long threads when you are obviously not listening to the answers that people are giving you?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •