0

I'm currently designing a database schema for a small microservice which handles products.

The service contains a simple REST API which allows users to perform basic management of products and an another API which is used to perform operations which consume these products. The maximum amount of consumes per product is limited with an explicitly defined consumables count. When this limit is reached, the product cannot be consumed anymore. Each product typically contains about 1,000 - 1,000,000 consumables, while a single operation may take 1 - 10 consumables at a time.

The frequency of the management operations is quite low, but the rate of the consuming operations is very high.

At the moment, the schema puts all product information into a single table. This table also contains information which must be audited into a snapshot based history table on each INSERT, UPDATE and DELETE operation. This is currently done with DB triggers, which know how to add rows into the history table along with the additional audit information. Each consumable use also adds a new row into consumables table with some additional information related to how it was consumed.

Here's a simplified example about how these tables are currently organized.

------------------------
| product              |
------------------------
| + id                 |
| + name               |
| + is_enabled         |
| + consumable_limit   |
| + consumable_counter |
| ...10 omitted fields |
------------------------

------------------------
| product_history      |
------------------------
| + id                 |
| + product_id         |
| + time               |
| + user_id            |
| + history_type       |
| + name               |
| + is_enabled         |
| + consumable_limit   |
| + consumable_counter |
| ...10 omitted fields |
------------------------

------------------------
| operations           |
------------------------
| + id                 |
| + product_id         |
| + consumable_idx     |
| ...10 omitted fields |
------------------------

In the above diagram, I've omitted some fields to keep things more simple. The thing that keeps me wondering here is that the consumable_counter in the product table gets rapidly updated with consuming operations, which may further flood the product_history table. Would it be better move the consumable counter into an another table where it would not trigger the a full snapshot of the product table row each time the consumable_counter is being updated? Somehow, I feel like it could be ideal as the consumable_counter is the only column in the product table which is being updated with a high rate. A full snapshot on each counter increment somehow feels a bit overkill.

Edit

The consumable_counter acts as a counter of how many times the corresponding product has been used. When it reaches the consumable_limit, the product cannot be consumed any more.

Edit

Each time a product is being consumed, a new entry will be added into the operations table. Entries in this table also act as business logic transactions containing a state which will be managed during the operation execution flow.

4
  • Is consumable_counter an inventory amount or how often something is used?
    – bbaird
    Oct 1 '20 at 18:30
  • @bbaird Added a small edit to describe how the consumable_counter works.
    – idkfa
    Oct 1 '20 at 18:44
  • Do you have a transactions table that handles each time a Product is consumed?
    – bbaird
    Oct 2 '20 at 13:13
  • @bbaird Added a tiny edit used to describe operations table relation with transactions.
    – idkfa
    Oct 4 '20 at 10:25
1

Short answer:

Yes, it is perfectly acceptable to split the audit tables into two subject areas if one attribute is updated very frequently, and the other attribute(s) are updated infrequently. But this has more to do with lowering write overhead and possibly making changes of certain attributes easier to find, not good or bad design.

Long answer:

There are reasons banks and other institutions do not store a static amount for balance/quantity. Namely:

  1. The data is easily derived from transactional data, in a high-volume environment this eliminates an additional update.
  2. Depending on the design pattern used, it is possible, if not probable the transactional data will disagree with the static value. If you are charging money for your products, or otherwise subject to audit, this is unacceptable.

See these two answers from SO which provide more insight and guidance than I can provide:

https://stackoverflow.com/a/29713230/13942986

https://stackoverflow.com/a/59465148/13942986

Additional Observations

  1. The Id on your product_history and operations tables are row pointers, not keys, and are entirely unnecessary. The primary key in both situations should be along the lines of (product_id, update_datetime) or (product_id, transaction_datetime).
  2. Context matters. Rename Id on product to product_id.
  3. Consistency matters. Use singular names, so operation, not operations.
1

I think your assessment is heading in the right direction. You do appear to be capturing too much information. Based on your description, I think you are describing a consumption event and not product history.

My recommendation is to look at splitting out the consumption attributes from the PRODUCT_HISTORY table into a new table. Without knowing the omitted columns, I am unable to provide a recommendation. Here are some thoughts:

  • if the "name" column is the product's name, that doesn't change with a consumption event and should remain in product history.

  • The consumption_limit might need to be in the product history and consumption event tables. You may need to know what the limit was when the consumption occurred and the limit may change over time, thus that fact should be recorded in the product history table.

  • A relationship between the consumption event and the operations table might be needed

Does a row in the operations table describe a collection of individual operations (and perhaps there is an operation table) or is it a singular operation? That is an important detail on figuring out the relationship with consumption events. If it is singular, I recommend you rename the table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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