3 ms·

Not critique, but some additional perspectives for those less well versed in designing databases. It's also important to realise that almost every time a dupli

Not critique, but some additional perspectives for those less well versed in designing databases. It's also important to realise that almost every time a duplicate key on an assumed unique key would trigger a constraint violation error, absence of this constraint would lead to some non sensical effects elsewhere in the system if ignored. Barcodes are a typical example also for this, as unless the entire dataflow from warehouse to cash register is equipped to handle duplicate EAN codes, all kinds of nonsense results could be the result, potentially with significant economic risk if duplicates are allowed to enter into the system unchecked. Everytime a constraint is removed from something that 'obviously' is/needs to be unique, the next step should be to add validation, duplicate and/or alias handling code everywhere that column is touched. Especially important at input and output. Good database design is really hard, if at all possible, have other people try to come up with ways to break your scheme.

dahauns 9y ago
Your last sentence can't be emphasized enough. It's funny that while I fully agree with your whole post, I still lost count of the times a unique constraint that needed to be removed made for much more of a headache than if it hadn't been there from the beginning - because it turned out to never have been neccessary, but you still had to care for the uniqueness assumption in all associated code. That's why I wouldn't recommend the authors rule of thumb: The rule of thumb is to add a key constraint when a column is unique for the values at hand and will remain so in reasonable scenarios. I'd consider that a premature optimization. Add a uniqueness constraint if and only if there is a clearly defined need for it, because assessing reasonable scenarios quickly descends into a lesson about hubris.
- brlewis 9y ago
  I still lost count of the times a unique constraint that needed to be removed made for much more of a headache than if it hadn't been there from the beginning - because it turned out to never have been neccessary, but you still had to care for the uniqueness assumption in all associated code. Where you work do people never make uniqueness assumptions in application code unless there's an explicit unique constraint in the database? That's impressive. Are you hiring?
  - dahauns 9y ago
    Where did I say that they never do that? It's just an observation based on experience (two decades in different companies of all sizes btw.) about the most blatant cases of this kind: the constraint was just set based on that rule of thumb, and since it was there in the schema, it was taken as gospel and any code throughout all the layers written with it in mind and shortcuts taken accordingly, all while there never was any requirement for it nor was the uniqueness actually assured (cf. those posts about names) and the breaking only a matter of time. Mabye I've become jaded, but I've come to see those as being among the most annoying and unnecessarily time-consuming classes of issues.

dahauns 9y ago

Your last sentence can't be emphasized enough. It's funny that while I fully agree with your whole post, I still lost count of the times a unique constraint that needed to be removed made for much more of a headache than if it hadn't been there from the beginning - because it turned out to never have been neccessary, but you still had to care for the uniqueness assumption in all associated code. That's why I wouldn't recommend the authors rule of thumb: The rule of thumb is to add a key constraint when a column is unique for the values at hand and will remain so in reasonable scenarios. I'd consider that a premature optimization. Add a uniqueness constraint if and only if there is a clearly defined need for it, because assessing reasonable scenarios quickly descends into a lesson about hubris.

brlewis 9y ago
I still lost count of the times a unique constraint that needed to be removed made for much more of a headache than if it hadn't been there from the beginning - because it turned out to never have been neccessary, but you still had to care for the uniqueness assumption in all associated code. Where you work do people never make uniqueness assumptions in application code unless there's an explicit unique constraint in the database? That's impressive. Are you hiring?
- dahauns 9y ago
  Where did I say that they never do that? It's just an observation based on experience (two decades in different companies of all sizes btw.) about the most blatant cases of this kind: the constraint was just set based on that rule of thumb, and since it was there in the schema, it was taken as gospel and any code throughout all the layers written with it in mind and shortcuts taken accordingly, all while there never was any requirement for it nor was the uniqueness actually assured (cf. those posts about names) and the breaking only a matter of time. Mabye I've become jaded, but I've come to see those as being among the most annoying and unnecessarily time-consuming classes of issues.