Skip to main content
added 24 characters in body
Source Link
J.D.
  • 39.5k
  • 12
  • 60
  • 134

Views are great tools for the job of unifying, transforming, and presenting the data to the application layer, and even help with data maintenance later on, in some cases, such as when your table structure needs to change. Since a view can act as a layer between the application and the database tables, there's less risk for the application when changing the structure of those tables. There's nothing inherently wrong with using them from a performance perspective, and JOIN performance (by the surrogate keys) should not be an issue with a properly architected and indexed database.

Views are great tools for the job of unifying, transforming, and presenting the data to the application layer, and even help with data maintenance later on, in some cases, such as when your table structure needs to change. Since a view can act as a layer between the application and the database tables, there's less risk for the application when changing the structure of those tables. There's nothing inherently wrong with using them from a performance perspective, and JOIN performance should not be an issue with a properly architected and indexed database.

Views are great tools for the job of unifying, transforming, and presenting the data to the application layer, and even help with data maintenance later on, in some cases, such as when your table structure needs to change. Since a view can act as a layer between the application and the database tables, there's less risk for the application when changing the structure of those tables. There's nothing inherently wrong with using them from a performance perspective, and JOIN performance (by the surrogate keys) should not be an issue with a properly architected and indexed database.

added 527 characters in body
Source Link
J.D.
  • 39.5k
  • 12
  • 60
  • 134

why surrogate keys can't have mutable string labels that propagate through all uses as an FK?

This goes back to data accuracy, primarily speaking. Nothing stops you from doing it, it's just not best practice because of the added risk against data accuracy, and makes data management harder and less performant when you need to update the value. You run the risk of lock escalation if it's a common value in the foreign keyed table, causing potentially longer wait times and blocking for read queries against that table.


why not deal with a changing country name by creating a new row for the new state + a column for years of existence?

Some people implement this design but more so because their business rules and use cases depend on historical data tracking. But for a regular transactional database with standard use cases, it inflates your data and still doesn't solve the aforementioned foreign key references where you'd have to update them with the changes too or inflate those tables as well. Even if I had the use cases to maintain historical data, I'd personally store the transactional history in a separate historical table from the active records.

why surrogate keys can't have mutable string labels that propagate through all uses as an FK

This goes back to data accuracy, primarily speaking. Nothing stops you from doing it, it's just not best practice because of the added risk against data accuracy, and makes data management harder and less performant when you need to update the value. You run the risk of lock escalation if it's a common value in the foreign keyed table, causing potentially longer wait times and blocking for read queries against that table.

why surrogate keys can't have mutable string labels that propagate through all uses as an FK?

This goes back to data accuracy, primarily speaking. Nothing stops you from doing it, it's just not best practice because of the added risk against data accuracy, and makes data management harder and less performant when you need to update the value. You run the risk of lock escalation if it's a common value in the foreign keyed table, causing potentially longer wait times and blocking for read queries against that table.


why not deal with a changing country name by creating a new row for the new state + a column for years of existence?

Some people implement this design but more so because their business rules and use cases depend on historical data tracking. But for a regular transactional database with standard use cases, it inflates your data and still doesn't solve the aforementioned foreign key references where you'd have to update them with the changes too or inflate those tables as well. Even if I had the use cases to maintain historical data, I'd personally store the transactional history in a separate historical table from the active records.

added 527 characters in body
Source Link
J.D.
  • 39.5k
  • 12
  • 60
  • 134

why surrogate keys can't have mutable string labels that propagate through all uses as an FK

This goes back to data accuracy, primarily speaking. Nothing stops you from doing it, it's just not best practice because of the added risk against data accuracy, and makes data management harder and less performant when you need to update the value. You run the risk of lock escalation if it's a common value in the foreign keyed table, causing potentially longer wait times and blocking for read queries against that table.


why surrogate keys can't have mutable string labels that propagate through all uses as an FK

This goes back to data accuracy, primarily speaking. Nothing stops you from doing it, it's just not best practice because of the added risk against data accuracy, and makes data management harder and less performant when you need to update the value. You run the risk of lock escalation if it's a common value in the foreign keyed table, causing potentially longer wait times and blocking for read queries against that table.

added 879 characters in body
Source Link
J.D.
  • 39.5k
  • 12
  • 60
  • 134
Loading
added 215 characters in body
Source Link
J.D.
  • 39.5k
  • 12
  • 60
  • 134
Loading
added 11 characters in body
Source Link
J.D.
  • 39.5k
  • 12
  • 60
  • 134
Loading
Source Link
J.D.
  • 39.5k
  • 12
  • 60
  • 134
Loading