Search code examples
sqldatabaseflags

Implementing Review flags in Databases; best practices

I need store some review flags that relate to some entities. Each review flag can only related to a single entity property group. For example table Parents has a ParentsStatus flag and table Children has a set of ChildrenStatus flags.

In the current design proposal I have three tables:

  • ReviewTypes: stores the flags and the properties they relate to.
  • ReviewPositions: stores the values the flags can have.
  • Reviews: stores the transaction data, the actual reviews. It is like UsersToFlags: Flags in a database rows, best practices.

The problem is I am getting push back that there is no need to have the Reviews table and it would be better to just store this actual review data on each entity. For example add an extra column to Parents to hold ParentsStatus. They feel it is a simpler solution and separating the data out is just “overkill” for out scenario.

I don’t like this idea as this means that every time we want to add a new review flag we need to update the core entity table to hold that flag.

Space is not a problem.

Do people have any strong opinions?

Edit:

This comment applies to the three answers. The consensus is the relational approach is best but I think I need to read up a little more on the EAV model as from some very basic reading Best beginner resources for understanding the EAV database model? and its related links it does not appear to be super straightforward and I don't want to dig myself a hole. Thanks to wildplasser. I'll loop back once I read up a bit more.

Solution

  • Oh yes. Their idea is simpler, until you want to enhance it. Given the scheme they are proposing what if two reviews were need per entity. What if you wanted to attach other things such as notes/annotations. Once they find out how much of an inflatable dartboard their idea is, what do you have to move to a more useful one? Not to mention you need some way of identifying status fields, with fragile rubbish like Column name ends with "_Status", or you have to hard code them somewhere.

    Doing it properly is not that much more work, it's not more complex, in fact in many ways it's simpler and it will cope with the invetible changes at far less cost.