I'm torn between two different schemas and would love some of your thoughts.

Essentially, we have a list of items, and each item can have a bunch of extra boolean attributes, currently about 15 however this is very likely to grow in the future probably up to 50 and perhaps more, it would probably change periodically with new columns being added.

We currently have implemented a separate table, where each row belongs to an "item" in the other table. Each row in this table represents an attribute and has an "attribute name", and "flagged" columns to say if the attribute was flagged. This means for every item created, approx 15 rows in this table are created.

The other option is to create extra columns on the first table of items and store the boolean attributes there, however this could result in quite a lot of columns.

What would be the best way forward?

  • 1
    Your first choice is the way to go. A classic, I believe, one to many relationship. – Dave May 29 at 20:54
  • Both are possible. Maybe you should figure out how your queries would look in both cases so that you can gauge how efficient they will be. If you go for the many columns and you use the flags in queries, consider using a Bloom index. – Laurenz Albe May 29 at 21:11
  • @LaurenzAlbe currently, the first approach is more efficient for us. However, this table will end up with 15x-20x the number of rows of the initial table which could itself already have a lot. Having that many rows with the right indexes should be fine, however, unless I am missing something? – James Eggers May 29 at 21:18
  • I don't think so. – Laurenz Albe May 29 at 23:29
  • How often do attributes settings get flipped after the initial insert? What about concurrency issues: if someone is toggling one attribute, does that need to conflict with someone else toggling a different attribute of the same parent row? Or does it need to not conflict with that? – jjanes May 30 at 14:46

Your Answer

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

Browse other questions tagged or ask your own question.