Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: best way to change this model

    We have a parent table that contains a "UNIT_COST" field. Currently the data model expects to find only one parent record (based on the primary key "CODE"). Our customer would like a change to the model where the unit_cost could change depending upon the date on which the item was purchased (ie they know that on Sept 1 their costs are going up, so on that date automatically start using the new UNIT_COST). The date determination would be based on a start_date and end_date that could be added to the table.

    So, now that the CODE field would no longer uniquely identify the record, what's the best way to update this model. We thought of 3 possibilities:

    1. create a PENDING table of future UNIT_COST information, and set up a scheduled job which updates this parent table with the new UNIT_COST on the date it comes into effect - no start_date or end_date necessary, but we have to keep track of the scheduled job.
    2. Set up the table with a unique primary key based on a SEQUENCE.
    3. Make the primary key of the table CODE and UNIT_COST.

    Is there another option that we could weigh development time against? Or does anyone have a strong opinion based on prior experience as to which way to lean?

    Thanks,
    Chuck

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool



    -- OR --
    Make the primary key of the table CODE+EFFECTIVE_DATE.

    PS: You may need previous unit_costs which correspond to past scheduled jobs (for costing purposes) and/or historical purposes.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The best way would be to have a costs table with the following structure

    code
    effective_start_date
    effective_end_date
    unit_cost

    Total Flexability, little code changes and cost history
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    That's what it likely will have. Are you suggesting a primary key structure like LKBrwn_DBA, or one of the one's I had in the post above? Or is that decision really one to make, as there's really no way to win here at this point.

    -Chuck

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Leave the parent record with a primary key of CODE (one row per parent) and have a primary code of CODE and EFFECTIVE_START_DATE on the new table.

    The current cost will NO longer be in the parent record, ONLY in the new costs table.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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