1

I got into a problem designing a database where I have a table, where every field needs to be validated by a human.

for example:

  • name
  • number
  • ID card number

Each one of these fields need to be validated (kind of a checkbox).

which one is the correct approach here? have boolean fields in the same tablet or a different table with each field as boolean?

4
  • 2
    For something to be considered "correct" (or not), it has to have some criteria of correctness. What are your criteria?
    – mustaccio
    Sep 24, 2020 at 0:04
  • "have boolean fields in the same tablet or a different table with each field as boolean?" This also depend a lot on how the data will be used later, and what kind of queries you will have. Also depends if your set of attributes is fixed or may change in the future. Sep 24, 2020 at 15:17
  • Do you validate all attributes for a row once, or can name be verified whilst number is not? Sep 25, 2020 at 5:07
  • @Lennart some fields could not be verified at the same time.
    – exsnake
    Sep 25, 2020 at 8:08

1 Answer 1

3

If the data has to be persisted between collection and verification, and each value can be accepted or rejected individually then there has to be a _verified column to match each _value column.

These verification columns are information about the thing to which they refer as much as the name and ID are so belong in the same logical entity type. Whether they go in the same physical table is a separate matter. If split this would be considered a vertical partitioning of the logical entity. There are good reasons to do that and good reasons not to.

If split the design and queries are more complicated. Likely response time will be fractionally better, though, since each table will be narrower. Both these can be swamped or mitigated by many other implementation details such as views or covering indexes.

Generally I'd keep a single table until proven otherwise.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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