4 ms·

> is not obvious at all without specifying requirements _first_. If there is a requirement that an employee skill depends on the language (ie. an employee can t

> is not obvious at all without specifying requirements _first_. If there is a requirement that an employee skill depends on the language (ie. an employee can type in a particular language only) - the design with a single 3 attributes table is the right one. In the "Baseline" section I do exactly that: specify requirements first. Nowhere does it say anything about skill depending on the language, so I assumed that it implies that they are independent. "Suppose that we’re building a system that lets people find sports instructors. Each instructor has a number of skills they can teach: yoga, weightlifting, swimming, etc. Also, instructors can speak one or more languages: English, French, Italian, etc." Not sure how to improve this wording so that it was more clear that skills and languages are independent. > Multivalued attributes discussion is missing several important aspects for example referential integrity. It is not at all obvious what should happen when a language is deleted from languages table. I'm not sure what you mean here because multivalued attributes as defined in 1977 preserve referential integrity. But array-typed values do not, you're right. This is a very important consideration, thank you! I did not realize that (because I don't really think that classical enforced FKs are that crucial in modern practice.) But from a theory point of view having a list of IDs is not enforced, yes.

mkleczek 2y ago
It would take a really long time to count all the issues caused by dangling references to nonexistent rows I had to deal with in my career. So yes - classical enforced FKs are still important - even if practice became „modern” (whatever that means )
- petalmind 2y ago
  You can't enforce FKs if you just split a database into several, due to regulatory, performance/maintenance, organizational or architectural (m*croservices) reasons. Enforced PKs in modern setting basically only work in a single uhmmm "transaction domain" (in a sense of ACID). You can certainly use two-phase commits or whatever, but that would kill performance so much that nobody (I think) seriously uses that. Like, I think most modern "NoSQL" solution doesn't have enforced FKs. Even in a single transaction domain you will get performance improvements if you get rid of enforced FKs. I remember a very old podcast (maybe 2005 or even earlier), a conversation with Werner Vogels where he explained how they were fixing performance issues with Oracle that they were using at that time. For me it was an eye-opener. The story basically is that when they created an order, they allocated a new ID for an order, then inserted rows in the "order_items" table, and only at the end they inserted a row in "orders" table. All this was NOT in a transaction. If there was an error during this, they just left the dangling "order_items" rows, and cleaned them up regularly with the script. Otherwise, Amazon (the web shop) could not handle the load, and it was 20 years ago on a state-of-the-art Oracle relational database(r)(tm) with all Amazon's resources. This is when I realized that God is dead and everything is allowed.
  - mkleczek 2y ago
    This is a false alternative: The fact that there are situations when implementing referential integrity is difficult does not mean you shouldn’t do it at all. Secondly: relational model is based on logical-physical independence. Referential integrity is specified at the logical level and does not dictate any particular implementation. OTOH violating 1NF makes it impossible to even specify RI constraints. NoSQL databases do not implement RI exactly because it is not possible to even specify it due to the lack of formal data model. And that’s not their strength but one of the many weaknesses. The story about optimising Oracle performance by giving up data integrity is only an illustration of why you should not use Oracle :) Just use Postgres. There is no problem with RI in Postgres as long as you remember to have an index on FK columns. Using CTEs allows you to perform data manipulation in a single statement with constraints enforced at the very end. Similarily: giving up data integrity due to microservice architecture cargo cult is… tail wagging the dog.
    - petalmind 2y ago
      > The fact that there are situations when implementing referential integrity is difficult does not mean you shouldn’t do it at all. > Referential integrity is specified at the logical level and does not dictate any particular implementation. OTOH violating 1NF makes it impossible to even specify RI constraints. Now we're bargaining. If referential integrity does not dictate any particular implementation, then having an application-level RI and no database-level constrains should also be acceptable. Yet this is not a common FK enthusiasts position. We just had a discussion of DSQL where a lot of people were saying things like "no FKs thus no real database". Well, I dunno.

mkleczek 2y ago

It would take a really long time to count all the issues caused by dangling references to nonexistent rows I had to deal with in my career. So yes - classical enforced FKs are still important - even if practice became „modern” (whatever that means )

petalmind 2y ago
You can't enforce FKs if you just split a database into several, due to regulatory, performance/maintenance, organizational or architectural (m*croservices) reasons. Enforced PKs in modern setting basically only work in a single uhmmm "transaction domain" (in a sense of ACID). You can certainly use two-phase commits or whatever, but that would kill performance so much that nobody (I think) seriously uses that. Like, I think most modern "NoSQL" solution doesn't have enforced FKs. Even in a single transaction domain you will get performance improvements if you get rid of enforced FKs. I remember a very old podcast (maybe 2005 or even earlier), a conversation with Werner Vogels where he explained how they were fixing performance issues with Oracle that they were using at that time. For me it was an eye-opener. The story basically is that when they created an order, they allocated a new ID for an order, then inserted rows in the "order_items" table, and only at the end they inserted a row in "orders" table. All this was NOT in a transaction. If there was an error during this, they just left the dangling "order_items" rows, and cleaned them up regularly with the script. Otherwise, Amazon (the web shop) could not handle the load, and it was 20 years ago on a state-of-the-art Oracle relational database(r)(tm) with all Amazon's resources. This is when I realized that God is dead and everything is allowed.
- mkleczek 2y ago
  This is a false alternative: The fact that there are situations when implementing referential integrity is difficult does not mean you shouldn’t do it at all. Secondly: relational model is based on logical-physical independence. Referential integrity is specified at the logical level and does not dictate any particular implementation. OTOH violating 1NF makes it impossible to even specify RI constraints. NoSQL databases do not implement RI exactly because it is not possible to even specify it due to the lack of formal data model. And that’s not their strength but one of the many weaknesses. The story about optimising Oracle performance by giving up data integrity is only an illustration of why you should not use Oracle :) Just use Postgres. There is no problem with RI in Postgres as long as you remember to have an index on FK columns. Using CTEs allows you to perform data manipulation in a single statement with constraints enforced at the very end. Similarily: giving up data integrity due to microservice architecture cargo cult is… tail wagging the dog.
  - petalmind 2y ago
    > The fact that there are situations when implementing referential integrity is difficult does not mean you shouldn’t do it at all. > Referential integrity is specified at the logical level and does not dictate any particular implementation. OTOH violating 1NF makes it impossible to even specify RI constraints. Now we're bargaining. If referential integrity does not dictate any particular implementation, then having an application-level RI and no database-level constrains should also be acceptable. Yet this is not a common FK enthusiasts position. We just had a discussion of DSQL where a lot of people were saying things like "no FKs thus no real database". Well, I dunno.