0

I'm designing a database for an accounting system. In the system there are companies, and each company has many contracts.

Each contract gives a company access to products, data types, or both. Clients use the different products to access different data types.

The initial contract may give the company Products A and B with access to data types X and Y. A second contract may come along and give the company access to data type Z within Product A. And so on.

It is important for the users of this system to be able to move through the contracts and see the state of the company's products and data type access, as of different contracts.

I am trying to think of a good approach to the architecture, and I've come up with two options:

  1. Store the latest state of a company's products and data types in a products and data_types table. These would belong directly to the company. Each new contract would update these tables appropriately. So, querying the current state of a company's access would be a simple SQL query.

    Then, in a separate table, I could store the contracts, and each contract would have many contract_items. This is where I could record which product or data type the contract is adding, updating or removing.

    I could then write the logic in my software layer to recreate historical snapshots, using this contract_items table. I would simply pull all the items for a company up to the selected contract, then use the items to build up the state of the company as of that contract.

  2. Store a snapshot of the company as of each contract. I am actually having trouble thinking through how I could do this in the database layer. One way would be to create a new rows in the companies, products and data_types tables for each contract.

    This would make querying the state of the company as of any particular contract a simple SQL query. I would just select out of those three tables where contract_id equaled the current selected id. However, it seems like a lot of data duplication. Perhaps this is not a problem.

Any thoughts on this? Are there best practices in this area, or something I'm missing? Thanks in advance.

Improve this question

1 Answer 1

Reset to default
1

Versioning. Not a new problem - used thousands of times per day by developers using any version control system.

Mark any item with 2 fields - ValidFromNo and ValidToNo. The last can be 0.

Keep a table versions that has the version numbers. ValidFromNo is from which version number an item is valid, ValidToNo - which can be null - is the last valid version. Null marks "Currently in use" so you do not have to update all valid items on every new versin.

Simple like that.

Improve this answer
5
  • Very interesting. In this case, what if a product was valid from contracts 0 to 1, then dropped in contract 2, then valid from 3 to 5? I would have three different product rows?
    – Sam Selikoff
    Commented Oct 28, 2013 at 16:23
  • Yes. In this case you likely would have a version nr. as part of the other key and uuse the version fields for the db side versioning. if you need to track different product versions you need multiple lines anyway to keep the different data. the version from and to no allow me to make sql queries for example for db version 2233 filtering out all data that was not valid at this point.
    – TomTom
    Commented Oct 28, 2013 at 16:59
  • Note that this means 2 versions - a "data version" (1,2,3) and a database version nr. You basically have to serialize updates to maintain the table of database versions, like a version control system (which you are). Document/Row versions are used by the logic to separate data versions for a row.
    – TomTom
    Commented Oct 29, 2013 at 8:45
  • Hmm, any good examples you could point me to?
    – Sam Selikoff
    Commented Oct 29, 2013 at 13:30
  • Not really. We spend a month sorting taht out in a major data warehouse project that had the requirement that any report had to be reproducable. So we ended up with this - and we had a serial processing script that generated new database vesion numbers. Reports were marked with the database version ;) Worked - and works.... and imho the only way to do it. You can also nicely mix non-versioned data by simply not having those fields on those other tables... But no real examlpes.
    – TomTom
    Commented Oct 29, 2013 at 14:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.