9 ms·

I agree with your colleague, and I insist on pushing my car everywhere because I fear gas as it is flammable. In other words, the world is full of idiots; and

by pif 4y ago

I agree with your colleague, and I insist on pushing my car everywhere because I fear gas as it is flammable. In other words, the world is full of idiots; and any time I start forgetting about it, I read something like your post and I get a wake-up call. What does R stand for in RDBMS is you don't use foreign keys and joins? Please, keep using your FKs, stay safe and don't mingle too much with idiots.

mkeedlinger 4y ago
I agree that using foreign key constraints is the right choice, but the tone of your comment comes off as very condescending and dismissive, and I don't like it.
autarch 4y ago
The "R" stands for "relations", as in "relations", which is a mathematical concept. SQL calls a "relation" a "table". The "relational" is RDBMS has nothing to do with relationships. But I still agree that OP's colleague is an idiot.
- goto11 4y ago
  To be really pedantic, tables are relations but a join between two tables are also a relation. Base tables, queries and views are all relations and therefore interchangeable in relational algebra.
- hu3 4y ago
  That might have been how it started (https://www.ibm.com/ibm/history/ibm100/us/en/icons/reldb/ https://www.ibm.com/ibm/history/ibm100/us/en/icons/reldb/). But it's definitely not what it means for the great majority of contemporary contexts. Relations in modern RDBMS are usually aliases to foreign keys unless otherwise specified.
wahnfrieden 4y ago
postgres has a shitload of useful features that are unrelated to relations
jonnycomputer 4y ago
Mathematically a relation is a set of tuples; which is exactly what a table is.
jongjong 4y ago
I think the author is talking about 'foreign key constraints' - You could have foreign keys without enforcing a constraint. Personally, I don't use foreign key constraints because: 1. It makes schema migrations and other data-management operations more difficult. 2. On insertion, the database needs to perform an additional check to verify that the record exists at the foreign key; this carries a performance cost; IMO, this is something which should be enforced at the application layer anyway. 3. It makes it more difficult to scale the database later because you can't separate tables onto 2 different hosts if one table references another using a foreign key. BTW, about #3, the same argument can be made against using table joins. Once you start using foreign keys or table joins, you will be forced to run those two tables on the same host in the foreseeable future; it's very difficult, error-prone and time consuming to migrate away from such architecture if you have a lot of data in a live environment. Personally I prefer to design all my tables and front end applications to not rely on foreign keys or table joins. There is a good reason why databases which are focused on scalability (like MongoDB) do not support foreign keys or joins (or at least they try to avoid them). I prefer to assemble data on the front end as much as possible because it allows my REST API calls to be granular; each one only refers to a single kind of resource; this helps to simplify caching and real-time updates; it also uses fewer resources on the server side and I find that it makes the front-end code more maintainable. Also, I like to design my front ends to mirror the natural separation of resources within the database. When the user wants to open up a related record, they need to click on a link (the foreign key ID/UUID is used to construct the link to the related resource); this loads up the other record as a separate step. This creates a very smooth (and fast) user experience - I also like it because this approach does not overload the user with information; collections of items don't show much details, on the other hand, individual resources may show a lot of detail. The real reason people use joins is because they want to pack a lot of details onto the user's screen when they are looking at a list view... Sometimes the reason why they want to do that is because they didn't design their tables correctly; maybe the tables which they use to generate list views don't contain enough columns/detail to be useful on their own so they feel forced to do joins. I find that drawing ER diagrams helps a lot with that. It's very important to get the cardinality of relationships between the different tables exactly right. Also, I find it very helpful to represent any many-to-many relation between two tables as a distinct table.
Spivak 4y ago
Eh, there are a lot of people who don't like using FK constraints, calling them all idiots is just bad faith and ignores the reasons they did it. Just because you can enforce a constraint at a specific layer doesn't mean you have to. DB people love shoving all sorts of application logic into the DB and there are good arguments to do it as well as downsides. App people sometimes prefer to do everything in the app and just let the DB be a dumb data store and there are good arguments for that too. But it depends isn't a hot take.
- FlyingSnake 4y ago
  If you're using an RDBMS and not using FK or other relational constraints, how do you plan to maintain referential integrity?
  - bdavisx 4y ago
    I'm not arguing one way or the other wrt to FK, I generally use them. You can maintain the integrity through code though.
- pif 4y ago
  > DB people love shoving all sorts of application logic I agree that application logic goes into the application, but data integrity is NOT application logic.
  - Spivak 4y ago
    Sure it is! As a thought experiment consider evil dba whose job it is to crash your application or make it do wrong things just by manipulating the data in the DB but following the constraints. Totally trivial, right? So data integrity is at all times the responsibility of both the app and the db. And the set of constraints you can enforce with the app will always be a superset of what can be enforced by the db. And for some cases (usually when the db is private to the application) it's easier to build that logic into the app and for other cases (multiple apps sharing a db) it's easier to build that logic into the db.

by pif 4y ago

mkeedlinger 4y ago

I agree that using foreign key constraints is the right choice, but the tone of your comment comes off as very condescending and dismissive, and I don't like it.

autarch 4y ago

The "R" stands for "relations", as in "relations", which is a mathematical concept. SQL calls a "relation" a "table". The "relational" is RDBMS has nothing to do with relationships. But I still agree that OP's colleague is an idiot.

goto11 4y ago
To be really pedantic, tables are relations but a join between two tables are also a relation. Base tables, queries and views are all relations and therefore interchangeable in relational algebra.
hu3 4y ago
That might have been how it started (https://www.ibm.com/ibm/history/ibm100/us/en/icons/reldb/ https://www.ibm.com/ibm/history/ibm100/us/en/icons/reldb/). But it's definitely not what it means for the great majority of contemporary contexts. Relations in modern RDBMS are usually aliases to foreign keys unless otherwise specified.

wahnfrieden 4y ago

postgres has a shitload of useful features that are unrelated to relations

jonnycomputer 4y ago

Mathematically a relation is a set of tuples; which is exactly what a table is.

jongjong 4y ago

I think the author is talking about 'foreign key constraints' - You could have foreign keys without enforcing a constraint. Personally, I don't use foreign key constraints because: 1. It makes schema migrations and other data-management operations more difficult. 2. On insertion, the database needs to perform an additional check to verify that the record exists at the foreign key; this carries a performance cost; IMO, this is something which should be enforced at the application layer anyway. 3. It makes it more difficult to scale the database later because you can't separate tables onto 2 different hosts if one table references another using a foreign key. BTW, about #3, the same argument can be made against using table joins. Once you start using foreign keys or table joins, you will be forced to run those two tables on the same host in the foreseeable future; it's very difficult, error-prone and time consuming to migrate away from such architecture if you have a lot of data in a live environment. Personally I prefer to design all my tables and front end applications to not rely on foreign keys or table joins. There is a good reason why databases which are focused on scalability (like MongoDB) do not support foreign keys or joins (or at least they try to avoid them). I prefer to assemble data on the front end as much as possible because it allows my REST API calls to be granular; each one only refers to a single kind of resource; this helps to simplify caching and real-time updates; it also uses fewer resources on the server side and I find that it makes the front-end code more maintainable. Also, I like to design my front ends to mirror the natural separation of resources within the database. When the user wants to open up a related record, they need to click on a link (the foreign key ID/UUID is used to construct the link to the related resource); this loads up the other record as a separate step. This creates a very smooth (and fast) user experience - I also like it because this approach does not overload the user with information; collections of items don't show much details, on the other hand, individual resources may show a lot of detail. The real reason people use joins is because they want to pack a lot of details onto the user's screen when they are looking at a list view... Sometimes the reason why they want to do that is because they didn't design their tables correctly; maybe the tables which they use to generate list views don't contain enough columns/detail to be useful on their own so they feel forced to do joins. I find that drawing ER diagrams helps a lot with that. It's very important to get the cardinality of relationships between the different tables exactly right. Also, I find it very helpful to represent any many-to-many relation between two tables as a distinct table.

Spivak 4y ago

Eh, there are a lot of people who don't like using FK constraints, calling them all idiots is just bad faith and ignores the reasons they did it. Just because you can enforce a constraint at a specific layer doesn't mean you have to. DB people love shoving all sorts of application logic into the DB and there are good arguments to do it as well as downsides. App people sometimes prefer to do everything in the app and just let the DB be a dumb data store and there are good arguments for that too. But it depends isn't a hot take.

FlyingSnake 4y ago
If you're using an RDBMS and not using FK or other relational constraints, how do you plan to maintain referential integrity?
- bdavisx 4y ago
  I'm not arguing one way or the other wrt to FK, I generally use them. You can maintain the integrity through code though.
pif 4y ago
> DB people love shoving all sorts of application logic I agree that application logic goes into the application, but data integrity is NOT application logic.
- Spivak 4y ago
  Sure it is! As a thought experiment consider evil dba whose job it is to crash your application or make it do wrong things just by manipulating the data in the DB but following the constraints. Totally trivial, right? So data integrity is at all times the responsibility of both the app and the db. And the set of constraints you can enforce with the app will always be a superset of what can be enforced by the db. And for some cases (usually when the db is private to the application) it's easier to build that logic into the app and for other cases (multiple apps sharing a db) it's easier to build that logic into the db.