3 ms·

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 presenc

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.
  - jstimpfle 8y ago
    I think these kinds of constraints are much easier implemented as code in a business logic layer. Conventional database schemas with their limited expressiveness are probably not well suited here.
    - magicalhippo 8y ago
      Sure, but that wasn't really the issue. The issue was how to store such data in an optimal way.

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.
- jstimpfle 8y ago
  I think these kinds of constraints are much easier implemented as code in a business logic layer. Conventional database schemas with their limited expressiveness are probably not well suited here.
  - magicalhippo 8y ago
    Sure, but that wasn't really the issue. The issue was how to store such data in an optimal way.