2

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.

Community
  • 1
  • 1
Michael Hollywood
  • 269
  • 4
  • 15
  • 1
    I'm not sure I understand your narrative - could you provide some example data of what you'd expect to see in each table (hopefully under both layouts that are being considered - showing the same data modelled each way). – Damien_The_Unbeliever Jun 06 '12 at 13:30
  • I don't see the problem. What you are proposing (as I understand it) is basically an EAV data nodel, except for the flags coming from a special enumeration-like domain (the ReviewPosistions table) – wildplasser Jun 06 '12 at 14:22
  • @Damien_The_Unbeliver: I can do up some example data if that helps. Take me a few mins. – Michael Hollywood Jun 06 '12 at 14:27
  • @wildpasser. it is not so much a problem but that I am getting push back to store the reviews on the actual entities which seems a bad idea. I did a fair bit of searching but found no definitive answer thus I asked and it appears the overwhelming opinion is to keep them seperate. – Michael Hollywood Jun 06 '12 at 14:27

3 Answers3

3

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.

Tony Hopkinson
  • 19,528
  • 3
  • 29
  • 38
2

normalization is always preferable to premature optimization.

Randy
  • 15,788
  • 1
  • 33
  • 51
1

One reason why I like the reviews table separate is that you can hold changes you may not want to display yet (as it hasn't been reviewed and approved) and still maintain the old dat until the new is approved. I don't know if your situation requires that.

To make future programming simpler for when you want to display the changes, you can write a view that shows the old and new data.

HLGEM
  • 88,902
  • 11
  • 105
  • 171