4 ms·

Check constraints. But DB constraints seems like the wrong level to fix the problem. If it happens often that an non-pregnant person is registered as pregnant,

by olavk 14y ago

Check constraints. But DB constraints seems like the wrong level to fix the problem. If it happens often that an non-pregnant person is registered as pregnant, then it might happen with women as well as men. Perhaps even more often! This can't be fixed at the DB level. My guess is that it is some kind of UI issue.

gaius 14y ago
Yes it can. Remember that in any non-trivial system, there is no "UI". There are web apps (there may be dozens of these alone), desktop client-server apps, terminals, interfaces to other systems (which in turn may be many protocols, file formats, etc). There may be 30+ years between the oldest and the newest app, and hundreds or thousands of developers working in everything from COBOL to C++ to Python involved throughout that time. All talking to the same database. You want the same rules to apply to all of them, so have the DB enforce them, job done. The webdev world, where there is 1 app in 1 language written by 1 team talking to 1 DB and that's as complex as that bit of the system architecture will ever get, doesn't have any techniques that can cope with this kind of environment.
- olavk 14y ago
  Do you mean that this kind of error can be prevented at the DB level? It is only possible in very trivial cases, like you know a man can't be pregnant. In the DB you can only ensure that data is internally consistent, not that the data entered corresponds to the real world facts, which is really the issue here.
  - gaius 14y ago
    Right, no DB can enforce checks for things that could be true but aren't (e.g. a trigger can't prevent you from wrongly entering an adult woman as pregnant, she might even look pregnant to the eye and not be). But a DB can prevent you doing things that don't make sense (e.g. age must be a positive number, men can't be pregnant, etc etc).

4 ms·

Check constraints. But DB constraints seems like the wrong level to fix the problem. If it happens often that an non-pregnant person is registered as pregnant,

by olavk 14y ago

gaius 14y ago
Yes it can. Remember that in any non-trivial system, there is no "UI". There are web apps (there may be dozens of these alone), desktop client-server apps, terminals, interfaces to other systems (which in turn may be many protocols, file formats, etc). There may be 30+ years between the oldest and the newest app, and hundreds or thousands of developers working in everything from COBOL to C++ to Python involved throughout that time. All talking to the same database. You want the same rules to apply to all of them, so have the DB enforce them, job done. The webdev world, where there is 1 app in 1 language written by 1 team talking to 1 DB and that's as complex as that bit of the system architecture will ever get, doesn't have any techniques that can cope with this kind of environment.
- olavk 14y ago
  Do you mean that this kind of error can be prevented at the DB level? It is only possible in very trivial cases, like you know a man can't be pregnant. In the DB you can only ensure that data is internally consistent, not that the data entered corresponds to the real world facts, which is really the issue here.
  - gaius 14y ago
    Right, no DB can enforce checks for things that could be true but aren't (e.g. a trigger can't prevent you from wrongly entering an adult woman as pregnant, she might even look pregnant to the eye and not be). But a DB can prevent you doing things that don't make sense (e.g. age must be a positive number, men can't be pregnant, etc etc).