Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Jeffrey.

Asked: November 02, 2017 - 4:13 am UTC

Last updated: November 10, 2017 - 5:04 pm UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hi AskTom team,

I'd like your ideas about the data model design and/or Oracle features that I could take advantage of to achieve the design goals described below.

Background:

I'm in the early stages of designing a data model for a brand new APEX application. The purpose of the application is to allow a diverse group of users to create, maintain and report budgets for their school. I'm planning to use VPD to allow each school to have their own private area where they can manage their budgets each year.

Requirement:

Each school may have one or many users submitting budgets for their departments; in some cases the school has a Finance Committee which wants to "snapshot" the budget and mark it as an approved "Preliminary" budget; after further changes and adjustments, later in the year they might "snapshot" it again and mark it as the approved "Operational" budget. Some schools have a different process, with different stages - so I believe a generic snapshot/versioning data model is needed. They want to be able to report on any snapshot of the budget (although I suspect edits will only be required on the "latest" or current version - i.e. no branching). They would also like to report deltas between snapshots.

Each school has one or more campuses, departments, and year levels. Each department may deliver one or more subjects. Each school can maintain one or more budgets, each comprising a collection of budget lines. The departments, subjects and year levels are maintained separately for each budget as they frequently change from year to year.

Note: The LiveSQL link is to a sample schema to give an idea of what I'm describing; it's not the final product and will probably change a lot as I learn more about the client's requirements.

Alternatives:

Option 0. Push back - advise the client "YAGNI" and don't support versioning. This will probably not be accepted, or they'll find workarounds (e.g. creating budgets named "v1", "v2", "v2 final", etc.) and then ask for system changes later on to add versioning features in afterwards anyway...

Option 1. Multiple "versions" of a budget are created as a new row in the budgets table, with all the child data copied across. Database does not have any explicit concept of a version - this meaning is assigned by users based on the budget name (e.g. 'Budget v1', 'Budget v2').

Option 2. Add a "snapshot" table as a child of budgets. Add an ascending "version_id" column to the budgets table and all child tables. The version_id is incremented whenever any record is inserted, updated or deleted; a snapshot is queried by finding all records where version_id <= the snapshot's version_id. No records are ever deleted (instead, implement a "soft delete" via a status column). Somewhat similar to the idea proposed here: https://stackoverflow.com/a/12981862/103295

Option 3. Add a "snapshot" table as a child of budgets. Add snapshot_id to all child tables include budgets, departments, budget_lines, etc.. Extend the VPD policy to only show one snapshot at a time - allow the school user to pick which snapshot they wish to view. A snapshot is merely a complete copy of another snapshot. One challenge will be creating a report of deltas between the current snapshot and another snapshot if the other snapshots are hidden by the VPD policy.

Option x. something else?

Challenges:

* Application complexity - e.g. avoid complex queries and DML logic if possible; prefer simple logic for version control, i.e. creating snapshots and rolling back to an earlier snapshot should be relatively simple DML operations

* Conceptual model complexity - e.g. prefer a concept as presented in the UI that can be easily understood and used by a wide range of users; most users will be effectively "new to the job" due to staff turnover; hide complexity as much as possible

* Query performance - e.g. when querying the current version, a snapshot, or comparing two snapshots to calculate a delta

Thanks very much.

with LiveSQL Test Case:

and Chris said...

So my understanding of this is:

- Each school has an annual budget
- Each department/campus/year/... prepares their budget
- This is then sent for approval
- After this point each department/campus/year/... may make further changes to the budget. But you need to report on the current status the budget for each area
- The cycle can repeat several times over a year

While the versioning model in option 2 enables this, I think it'll make your SQL far too complex.

Option 1 would work, but doesn't feel right ;) One department could make 100+ edits, others just a couple or none. So you're duplicating a lot of data. Or maybe they all make lots of edits, but at different times. Either way you end up duplicating lots of data.

So I'm leaning towards option 3. Create a snapshot table. When a user approves/snapshots a budget, you copy all the current budget lines to this. Add a surrogate key and capture the snapshot date so you can compare versions. If you need to capture comments the committee makes about budgets you could store them here.

Of course, this doesn't help if you need to compare versions of in-progress of a budget. If you do need this facility, add Flashback Data Archive to the working table. This enables you to get the state of the budget across all lines at a time in the past with flashback query:

select * from buget_lines as of timestamp ...


Others welcome to add their thoughts and suggestions on how to do this.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

Jeffrey Kemp, November 10, 2017 - 5:40 pm UTC

Agreed - of the options originally listed #3 is the most likely to work out.

I might end up making the budget lines a child of a snapshot record, itself a child of budgets; that way everything is in a "snapshot". Mark one snapshot as the "live" version, ala OWM.

BTW I'm currently investigating Oracle Workspace Manager as a potential solution (or just as an extra feature of the system), in particular due to its capabilities for generating a Diff report between snapshots. Its other features such as savepoints, multiple workspaces and merging would be a bonus.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.