3 ms·

System design 101: keep business logic into the layer above database layer rather than relying on specific db system to implement them. In this way to design an

System design 101: keep business logic into the layer above database layer rather than relying on specific db system to implement them. In this way to design an system, there shouldn't have any different between using MySQL of using NoSQL, their role is just storage engine. So, you don't need to follow the relational database practice, like for example, foreign key, constrains, normalization anymore.

abraae 10y ago
Trying to avoid using foreign key constraints in a relational database is not "system design 101", its an instant fail. When I cast my eye over a table with foreign key constraints, I am 100% certain that every single row conforms to those constraints, and always will. By contrast, when the same table does not have constraints, but instead relies on some business logic layer to enforce them, then I have to consider whether there might be corrupt rows put in there by: - bad business logic code - bad import scripts - some contractor who used to work for us 5 years ago and briefly uses his php script to push up some data
- stevesun21 10y ago
  Sadly, you remind me the DBA-is-everything system design style. In modern system designs, a system need more than just a database system to store business states, and to encapsulate business logic into higher layer is not just have flexibility also have scalability. Take sometime to think about the following three scenarios: Scenario 1: what if a system need to migrate to different database system, then the whole business logic are need to totally re-implemented with the destination system DSL. Scenario 2: if system need more just one storage system to persist business states, for example, I use db to store image metadata and use s3 to store the image? I don't believe the foreign key constrains will still works. Scenario 3: if we have system need to process business state in asynchronously, for example, use message queue. Also think about how to do unit tests (this is also how we keep the business logic correct) how to do CI/CD. System design is more than just a ERD design.
  - abraae 10y ago
    I think you'll find that its not just DBAs who like to have the database enforce integrity when it can. > Scenario 1: what if a system need to migrate to different database system, then the whole business logic are need to totally re-implemented with the destination system DSL. If it takes you more than a few minutes to rewrite a foreign key constraint to work on a different target database then you're doing something wrong. > Scenario 2: if system need more just one storage system to persist business states, for example, I use db to store image metadata and use s3 to store the image? I don't believe the foreign key constrains will still works. You're right, it doesn't - you can't use a foreign key constraint there. That doesn't mean you abandon them altogether, just use them where they work - in your database. > Scenario 3: if we have system need to process business state in asynchronously, for example, use message queue. Then use a message queue. Still no reason to give up on the declarative integrity checking of FK constraints inside your database. No idea what you;re talking about wrt unit tests - perhaps you're saying you want to allow bad data into your database so that you can run your tests? In which case here's a better idea - use FK constraints to never let it in in the first place, and don't bother testing, since the DB vendor already did it for you.

abraae 10y ago

Trying to avoid using foreign key constraints in a relational database is not "system design 101", its an instant fail. When I cast my eye over a table with foreign key constraints, I am 100% certain that every single row conforms to those constraints, and always will. By contrast, when the same table does not have constraints, but instead relies on some business logic layer to enforce them, then I have to consider whether there might be corrupt rows put in there by: - bad business logic code - bad import scripts - some contractor who used to work for us 5 years ago and briefly uses his php script to push up some data

stevesun21 10y ago
Sadly, you remind me the DBA-is-everything system design style. In modern system designs, a system need more than just a database system to store business states, and to encapsulate business logic into higher layer is not just have flexibility also have scalability. Take sometime to think about the following three scenarios: Scenario 1: what if a system need to migrate to different database system, then the whole business logic are need to totally re-implemented with the destination system DSL. Scenario 2: if system need more just one storage system to persist business states, for example, I use db to store image metadata and use s3 to store the image? I don't believe the foreign key constrains will still works. Scenario 3: if we have system need to process business state in asynchronously, for example, use message queue. Also think about how to do unit tests (this is also how we keep the business logic correct) how to do CI/CD. System design is more than just a ERD design.
- abraae 10y ago
  I think you'll find that its not just DBAs who like to have the database enforce integrity when it can. > Scenario 1: what if a system need to migrate to different database system, then the whole business logic are need to totally re-implemented with the destination system DSL. If it takes you more than a few minutes to rewrite a foreign key constraint to work on a different target database then you're doing something wrong. > Scenario 2: if system need more just one storage system to persist business states, for example, I use db to store image metadata and use s3 to store the image? I don't believe the foreign key constrains will still works. You're right, it doesn't - you can't use a foreign key constraint there. That doesn't mean you abandon them altogether, just use them where they work - in your database. > Scenario 3: if we have system need to process business state in asynchronously, for example, use message queue. Then use a message queue. Still no reason to give up on the declarative integrity checking of FK constraints inside your database. No idea what you;re talking about wrt unit tests - perhaps you're saying you want to allow bad data into your database so that you can run your tests? In which case here's a better idea - use FK constraints to never let it in in the first place, and don't bother testing, since the DB vendor already did it for you.