3 ms·

You'd be surprised. I used to work on a product where the lead developer made sure foreign keys were NOT enabled on production. They were only "allowed" in dev.

You'd be surprised. I used to work on a product where the lead developer made sure foreign keys were NOT enabled on production. They were only "allowed" in dev. Some teams have a strict "no foreign keys" rule.

arthurcolle 5y ago
That is the dumbest thing I've ever heard
- icedchai 5y ago
  I agree. This same guy also had other interesting database practices. I eventually quit because it was so bad.
tatersolid 5y ago
Does this mental giant know that modern databases use foreign keys to optimize queries, sometimes even eliding JOIN operations entirely if the schema, foreign keys, and other indexes make it safe to do so? I’ve seen certain large-table queries improve by 10x when foreign keys were added.
- icedchai 5y ago
  Hah. In his defense, this was MySQL 5.x... He also refused to allow "datetime" or "timestamp" datatypes anywhere. All timestamps were stored as bigints.
  - evanelias 5y ago
    These are actually very common restrictions at many of the companies with the largest/busiest MySQL installations in the world, typically OLTP workloads with extreme query rates. Avoiding FKs enables sharding, reduces write locking time, and greatly simplifies online schema changes. These are essential requirements at extreme scale. (and in response to GP's point, at least in MySQL, FK constraints strictly harm performance; although the underlying index improves performance, you can have that without the FK constraint) As for bigints for time values: storing UTC unix timestamps in a bigint avoids some issues with unexpected timezone conversions and DST conversions, as well as (in older mysql versions) unwanted auto-update behavior for the first timestamp column in a table. This one tends to be more of a "reduce support burden on the database team" type of requirement -- the risk of datetime or timestamp issues goes up as the number of product engineers massively outnumbers the db engineers, or once you have product engineers in many different timezones, data centers in many timezones, etc. Of course, there are major trade-offs with these decisions. And they may or may not make sense for your former company's size and situation. But in any case these restrictions are not particularly unusual for MySQL.
    - icedchai 5y ago
      Totally... This is all valid. This MySQL install was over 10 years ago! At a previous company (about 15 years ago) we used FKs with MySQL and they did cause issues doing all the things you described.

arthurcolle 5y ago

That is the dumbest thing I've ever heard

icedchai 5y ago
I agree. This same guy also had other interesting database practices. I eventually quit because it was so bad.

tatersolid 5y ago

Does this mental giant know that modern databases use foreign keys to optimize queries, sometimes even eliding JOIN operations entirely if the schema, foreign keys, and other indexes make it safe to do so? I’ve seen certain large-table queries improve by 10x when foreign keys were added.

icedchai 5y ago
Hah. In his defense, this was MySQL 5.x... He also refused to allow "datetime" or "timestamp" datatypes anywhere. All timestamps were stored as bigints.
- evanelias 5y ago
  These are actually very common restrictions at many of the companies with the largest/busiest MySQL installations in the world, typically OLTP workloads with extreme query rates. Avoiding FKs enables sharding, reduces write locking time, and greatly simplifies online schema changes. These are essential requirements at extreme scale. (and in response to GP's point, at least in MySQL, FK constraints strictly harm performance; although the underlying index improves performance, you can have that without the FK constraint) As for bigints for time values: storing UTC unix timestamps in a bigint avoids some issues with unexpected timezone conversions and DST conversions, as well as (in older mysql versions) unwanted auto-update behavior for the first timestamp column in a table. This one tends to be more of a "reduce support burden on the database team" type of requirement -- the risk of datetime or timestamp issues goes up as the number of product engineers massively outnumbers the db engineers, or once you have product engineers in many different timezones, data centers in many timezones, etc. Of course, there are major trade-offs with these decisions. And they may or may not make sense for your former company's size and situation. But in any case these restrictions are not particularly unusual for MySQL.
  - icedchai 5y ago
    Totally... This is all valid. This MySQL install was over 10 years ago! At a previous company (about 15 years ago) we used FKs with MySQL and they did cause issues doing all the things you described.