1

I am looking into ways for implementing maker checker principle for various classes over our company repository. Basically the rules are as follows:

  • An entity is an object to be seen on a website (e.g. a blog post)
  • There is a creator of an entity with appropriate permissions/role (e.g. ROLE_POST_CREATOR) (in the original question context, maker)
  • There is an approver of an entity with appropriate permissions/role (e.g. ROLE_POST_APPROVER) and different than the maker of the post (in the original question context, checker)
  • The post has two statuses (DRAFTED and APPROVED) and only posts with APPROVED status are seen on the website

So far so good. Here's where it gets complicated:

  • Not only the post should be approved to be seen on the website, but also should it be approved to be removed from the website (deleted), or to be updated on the website. In other words, updating a post should not affect the post that is currently showing, only after the approval it should show the new version. Same goes for deleting (deleting a post is only a request to delete a post, only after the approval it should actually get deleted)

Modeling this -say in Java- is pretty straight forward:

class SampleClass {
    ...

    private User maker;

    private User checker;

    private Status status;
}

however persisting it into a database (especially an update) is not. Here are a couple of ideas that I could come up with:

  • For every entity, I could have two tables, say SAMPLE_CLASS and SAMPLE_CLASS_REQUESTS, basically, I would duplicate all fields from SAMPLE_CLASS to SAMPLE_CLASS_REQUESTS except for the updated field. Upon approval of _REQUESTS I would then delete it from that table and move the updated version into SAMPLE_CLASS.

This is a very feasible option, though it requires a new entity creation, say SampleClassRequest to be displayed to the checker users. This is quite a feasible option, except for creating a new table for every entity to implement maker checker is quite an overhead in a 100-something number of tables environment.

  • I could create new fields for SampleClass in SAMPLE_CLASS table that are to be updated. In this scenario, I should still generate SampleClassRequest but could escape from the overhead of generating a new table for every entity. However, this looks like a -very- bad database architecture.

How would you go for implementing it? Basically, I am looking for an example library that I could imitate the design, behaviour and architecture, or a (Java) library that already has this feature, or at least guidance/example methodology. Even a sample database design coming from a database administrator would help.

  • I don't see the point of having both SAMPLE_CLASS and SAMPLE_CLASS_REQUESTS. You'll have multiple revisions each entity (blog post or whatever) each getting their own row. The public sees the latest approved one. The approver sees the latest unapproved one. An approver field and a timestamp take care of that. – candied_orange Oct 6 '19 at 2:59
  • "I have to store what is changed in addition to metadata such as who changed it, current status etc" - maybe take a look at event sourcing; you publish an event (author, changes), then upon approval another one that makes the earlier one approved, and construct the post from the event stream, as it describes it at any given point in time (so you also have a history of changes - like in a wiki, or git, roughly speaking). – Filip Milovanović Oct 6 '19 at 10:25
  • @candied_orange Do you think inserting a new row with an approved status or updating the latest draft row's status as approved would make more sense? – Hasan Can Saral Oct 7 '19 at 9:13
2

You have two issues to solve: a) keeping track of the latest-approved and latest submitted state and b) applying that behaviour to a large number of entities.

Lets go for a) first. I suggest you use time slices. This means that you store all versions of e.g. a blog-post in the same table. Each version gets a valid-from and valid-until column, nullable. The currently published version has a valid-from in the past and a null valid-until (or something like 9999-12-31 23:59:59). When a new version is approved, you set the valid-until of the previous version to NOW() and make the approved version such that it is now valid. Querying should be starightforward; some SQL dialects have very convenient semantics for this (T-SQL temporal tables, BETWEEN if your valid_until is not nullable).

Now for having hundreds of entities behave this way:

If all your entities are very similar, you'll probably get away with single table inheritance. If your entities vary a lot, especially with relationships that need to be consistent, i'd recommend duplicating all of the columns that you need across all the tables. Thats a lot on the database side, but with Hibernate's @MappedSuperclass it's really no duplication at all in java.

  • 1
    As I mentioned in the question, modeling this in the code level is a fairly easy task. Modeling it in the database layer causes problems (such as having ~100 more tables). – Hasan Can Saral Oct 5 '19 at 17:03
  • I just gave you a solution how to model this in the database without "~100 more tables". – cmenke Oct 5 '19 at 17:11
  • I think you are missing a huge point. I have to store what is changed in addition to metadata such as who changed it, current status etc. E.g. for some entities, a blog post's body could have changed. In a same manner, if a new user is added, their last name could have changed. That is particularly why you cannot store them in a single table in the db. – Hasan Can Saral Oct 5 '19 at 17:14
  • 2
    If you want to keep track of content changes in your entities, I would recommend working with "valid from", "valid to" timestamps in your entity(ies) and keeping old states in the database. (Insert rather than updating records; only update the valid from/to timestamps on the old record.) You would have to also introduce a common "business key" for your entities so you can associate temporal slices of objects that belong together. The approach with the single relation still works with this, so no duplicating entities. SQL will get somewhat annoying due to the temporal slices. – cmenke Oct 5 '19 at 17:23
  • Do you think inserting a new row with an approved status or updating the latest draft row's status as approved would make more sense? – Hasan Can Saral Oct 7 '19 at 9:13
0

try this instead. I would use the additional status field and i'll create dropdown so that maker can select "submit" and approver can select"approve" so that the same update will be done on my database

New contributor
samson beyene is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • Thanks for the answer, but the question was more on how to model this in the database, rather than the UI. – Hasan Can Saral 10 hours ago

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.