Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Relational Design Flaw

    Access '97 SR-2
    I made an assumption at design time and of course now it's come back to bite me. <img src=/S/blush.gif border=0 alt=blush width=15 height=15> We sell approximately 30 products. Details of these products, their core pricing and option pricing exist in tblTerms. Since the beginning of time we have sold over 14,000 products (these are actually contracts that can remain in effect for up to 3 years) and details for each contract (customer information) is stored in tblContracts, linked to tblTerms. The assumption: product pricing would never increase. Well, never say never. We are now facing incremental price increases for future product sales. 5% immediately, another 5% in two months and 2% in four months. I have an awful lot of forms and reports that rely on complex calculation of revenue based on tblTerms. Obviously if I change the pricing in tblTerms it will throw off pricing for past sales. Any idea how I might get myself out of this mess? <img src=/S/frown.gif border=0 alt=frown width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Relational Design Flaw

    What you do depends on the exact circumstances. Are these price increases across the board, or are they specific amounts for particular products. If across the board, you could create a table that holds the increase percentage and a start date, but this is a somewhat quick and dirty fix since the calculations would always have to look at the base rate and then calculate incrementally.

    A better option would be to create a new table that has a TermID, product ID, a price and a start date for each product, with the TermID, productID and start date forming a unique key. Then create a query that returns the current prices for today's date and add that to the query that now returns the hard coded prices for each product. Since you would start by creating the existing prices in the new table, you can test the results using both methods. If they work the same way, then you should be able to make the rest of the mods you need to the code to use this new approach. That way, you maintain a price history and could go back and get the "current" prices for an earlier period for report or comparison.
    Charlotte

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Relational Design Flaw

    An alternative would be to create another table (let's call it tblContractTerms). At the time a specific contract is signed, then the tblContractTerms information is automatically created by copying the relevant info from tblTerms. So, you always have the terms at which a contract was signed, and don't have to rely a TermEffectiveDate.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relational Design Flaw

    Thank you Charlotte and Mark. A new table it shall be. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> I'll soon be moving to O2K and and an extensive re-design (long overdue). If building from scratch would you suggest these methods or is there a more effective means of handling this situation? So far I've managed to avoid using calculated fields to store these values, but it is tempting <img src=/S/alien.gif border=0 alt=alien width=14 height=15>

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Relational Design Flaw

    You stay out of trouble longer if you create some sort of one-to-many relationship, regardless of the details. It isn't hard to create a query to give you the most current information, and that's my usual method of handling stuff like that.
    Charlotte

  6. #6
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thank You

    Yep I agree, and I envision a very streamlined re-write now that I'm older and wiser and having hung out in the Lounge. If I've learned anything since diving in and teaching myself Access programming it's this; think ahead and design for every possible permutation. Most importantly, don't believe the end user when they state categorically, we'll never encounter that situation. <img src=/S/sarcasm.gif border=0 alt=sarcasm width=15 height=15> They mean well, but let's face it, it's our fault when the playing field changes. <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>

  7. #7
    ravey
    Guest

    Re: Relational Design Flaw

    A suggestion (for the early design stages of ANY relational database) is to closely examine what all of your entities really are.
    You tell us "Details of these products, their core pricing and option pricing exist in tblTerms." What entity does this table represent? A product and its attributes--at a point in time? Then, if you need another entity that represents contracts, in another table, what are the attributes of that entity? Its price, perhaps, and THAT WON'T CHANGE, I should think.
    That might lead you to create a field in the CONTRACTS table to store the price. This is not redundant data, because the table for future contracts will change price and perhaps other attributes, over time, while the price in an executed contract presumably will not change. You can easily insert the price value in the Contracts table FROM the Products table at the time you create a new Contract record.

  8. #8
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relational Design Flaw

    I'm going back and forth on this one. At the moment, I tend to agree with you. The contract price remains static unless there is an adjustment made (in which case I have a field to record same). It would certainly make reports, forms and their underlying queries much easier to design. I'll have to give this more thought.

  9. #9
    New Lounger
    Join Date
    May 2002
    Location
    California
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relational Design Flaw

    It's nearly impossible for someone who doesn't understand the operations or organization to advise on data structure. My strong advice is just that you attempt to define (in writing often helps clarify matters) exactly what entity is represented by each data table. I have taught database courses at community college level for going on 10 years and done some consulting work, and in my experience the biggest problems designers have is clearly defining the entities. Once you have well understood entity definitions, the other issues fall into place with surprising clarity. Without such definitions, designers tend to muddle through a murky mist, trying to figure out how all the data should be organized.

    Example (hypothetical): "tblProduct: Each record in this table represents the current description of a product, its part number, name, cost and price. tblContract: Each record in this table represents the terms of a specific contract with a specific customer, including quantities of products defined in tblProduct, date of contract, date of delivery, terms of payment, and itemized contract prices." That may not be accurate for your situation, but it should illustrate how specific you need to be. Once you have such definitions, you must review all fields in each table to make sure that they represent characteristics of the entity defined and nothing more. Thus, if it is true in your case that tblProduct (or whatever yours is named) indeed represents the current description of a product (implying that some characteristics may change over time), then you will see that you need to store the contract price in tblContract, where it won't change; on the other hand, if your circumstance is that the price is static, then you may not need to store the price in tblContract, as it will always be available from tblProduct. But of course you said "unless ...", so you have to assess which approach fits your situation better.

    Hope this helps.

Posting Permissions

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