4 ms·

This is not really true of any mature system. There are many reasons to design a schema in normal form, including performance, maintainability and space optimis

by mianos 8y ago

This is not really true of any mature system. There are many reasons to design a schema in normal form, including performance, maintainability and space optimisation. Database design outside start-ups is a fairly mature area and normal form is well proven good practice.

marcosdumay 8y ago
I will call bullshit on that. I have never seen a database on the 6th normal form on the real world, and I don't believe any exist outside of a lab. Anything beyond 3rd normal form brings very sporadic gains to maintainability and space optimization. The usual case is that you lose those. About performance, I can imagine there exist situations where you'll gain some by going further than the 3rd normal form, but I don't believe anybody sees that as a normal situation.
klodolph 8y ago
Yes, these are good reasons to use normal form. And it's also true that normal form is proven good practice. But in some cases, performance is important enough that adding a denormalized table or two makes sense. This isn't really "proven good practice" in any sense, but it's something that should be evaluated on a case by case basis when there's a compelling reason for it. It's kind of like the dream of having a sufficiently smart compiler. In theory, a relational database could operate in normal form and give you all the performance you need. In practice, they don't always do that.
magicalhippo 8y ago
In our application we have a form that has at least 50 fields which are individually optional. This is in addition to the required fields, and currently it's all one table. Are you saying it would be better for performance and space to have all of those optional fields in separate tables, and that it wouldn't be a huge PITA to write queries against for say reporting and while debugging?
- jstimpfle 8y ago
  Are these 50 fields "logically" relevant? In other words, do you do significant joining at these fields? Or in business logic, do you switch much on the presence of these fields? I'll assume "no" since they're so many of them and they're individually optional fields. Maybe most of them are strings and you could simply use empty strings. Or whatever, it doesn't matter. I'm not saying these cases are trivial, but they're leaf concerns and not really relevant from a "schema" point of view. But if the answer is really "yes", then I'll say that's at least a maintainability problem (bad coherence).
  - magicalhippo 8y ago
    They're fields in a digitized form, essentially. Multiple name, address etc sections representing people or companies, for example, as well as domain specific fields which shouldn't be used, are optional or are required depending on the value of a previous field. Most are strings of various lengths, but also plenty of numeric (weight etc) fields as well as dates. Expected arrival date for example, which is optional in most cases but required in some. Our UI would have to join in all the fields, as it displays them all in one window, and our customers would absolutely hate to split filling them up into multiple windows. Also, once the form is sent and approved by officials, the data has to be saved without change for 10 years, like saving the paper version of the form for 10 years in a filing cabinet. It also needs to be easily accessible for the users up to 3 years after approval in case one needs to make a correction, which is essentially done using a special copy of the form. I'm no db expert by any stretch, I've learned by doing. So not gonna claim this couldn't have been handled better.
    - magicalhippo 8y ago
      To expand slightly. Take one of the name and address sets. If the company has a valid EORI number, one should _only_ fill in the EORI number in the organization number field (filling in name etc would lead to error). Otherwise one should supply name, address, country etc. Depending on the value of another field, the name of a contact person is required, otherwise it's optional.

by mianos 8y ago

marcosdumay 8y ago

I will call bullshit on that. I have never seen a database on the 6th normal form on the real world, and I don't believe any exist outside of a lab. Anything beyond 3rd normal form brings very sporadic gains to maintainability and space optimization. The usual case is that you lose those. About performance, I can imagine there exist situations where you'll gain some by going further than the 3rd normal form, but I don't believe anybody sees that as a normal situation.

klodolph 8y ago

Yes, these are good reasons to use normal form. And it's also true that normal form is proven good practice. But in some cases, performance is important enough that adding a denormalized table or two makes sense. This isn't really "proven good practice" in any sense, but it's something that should be evaluated on a case by case basis when there's a compelling reason for it. It's kind of like the dream of having a sufficiently smart compiler. In theory, a relational database could operate in normal form and give you all the performance you need. In practice, they don't always do that.

magicalhippo 8y ago

In our application we have a form that has at least 50 fields which are individually optional. This is in addition to the required fields, and currently it's all one table. Are you saying it would be better for performance and space to have all of those optional fields in separate tables, and that it wouldn't be a huge PITA to write queries against for say reporting and while debugging?

jstimpfle 8y ago
Are these 50 fields "logically" relevant? In other words, do you do significant joining at these fields? Or in business logic, do you switch much on the presence of these fields? I'll assume "no" since they're so many of them and they're individually optional fields. Maybe most of them are strings and you could simply use empty strings. Or whatever, it doesn't matter. I'm not saying these cases are trivial, but they're leaf concerns and not really relevant from a "schema" point of view. But if the answer is really "yes", then I'll say that's at least a maintainability problem (bad coherence).
- magicalhippo 8y ago
  They're fields in a digitized form, essentially. Multiple name, address etc sections representing people or companies, for example, as well as domain specific fields which shouldn't be used, are optional or are required depending on the value of a previous field. Most are strings of various lengths, but also plenty of numeric (weight etc) fields as well as dates. Expected arrival date for example, which is optional in most cases but required in some. Our UI would have to join in all the fields, as it displays them all in one window, and our customers would absolutely hate to split filling them up into multiple windows. Also, once the form is sent and approved by officials, the data has to be saved without change for 10 years, like saving the paper version of the form for 10 years in a filing cabinet. It also needs to be easily accessible for the users up to 3 years after approval in case one needs to make a correction, which is essentially done using a special copy of the form. I'm no db expert by any stretch, I've learned by doing. So not gonna claim this couldn't have been handled better.
  - magicalhippo 8y ago
    To expand slightly. Take one of the name and address sets. If the company has a valid EORI number, one should _only_ fill in the EORI number in the organization number field (filling in name etc would lead to error). Otherwise one should supply name, address, country etc. Depending on the value of another field, the name of a contact person is required, otherwise it's optional.