4 ms·

There just is no substitute for SQL. Some thoughts on what has given it a bad name: 1) The pervasive use of artificial keys. USE NATURAL KEYS. Unfortunately pr

There just is no substitute for SQL. Some thoughts on what has given it a bad name: 1) The pervasive use of artificial keys. USE NATURAL KEYS. Unfortunately probably 99% of real-world databases were designed with artificial keys. I wish I could point to some literature on this topic. It is very rare and I only came to learn about this from a DBA who is well-versed in designing with natural keys. I'm trying to get him to publish more on this topic. 2) ORMs. This is just a bad practice. Their use in part derives from the awful schemas designed with artificial keys, requiring another layer of complexity to get a more intuitive model of the data. Fortunately for me over the past 3 years I've been doing almost all my application I/O with F#'s SQL Type Provider, SqlClient, http://fsprojects.github.io/FSharp.Data.SqlClient/ http://fsprojects.github.io/FSharp.Data.SqlClient/ , which strongly types native query results, functions, and SPROCS. Just does not work if you need to construct dynamic SQL. I've been trying to goad the author into also providing meta data retrieval. That would be the icing on the cake. 3) SQL does not seem to be a required topic for undergrads. There are really no unsolved problems (of note), so it's not interesting to academics. 4) Most app programmers don't get much practice writing difficult queries or tuning problem queries, so that one time every 9 months when you do something hard, it is hard. (And again, often compounded by the complexity introduced by artificial keys.)

sobani 9y ago
Can you give 1 example of a good natural key? Note I will disqualify anything that has a reasonable chance of changing, like the primary email address of an account, a persons name, a persons day of birth or the 'public id' of a bank account
- yawaramin 9y ago
  A composite key made up of foreign keys in a junction table.
- irishsultan 9y ago
  How often does the birthday of people change? (Then again, how likely is it to be unique?)
  - sobani 9y ago
    The closest example I can give is that I work on a system where the birthday can be partially unknown. This mostly affect refugees. So the person either has to guess his own age, or only knows the year, or only the year and month. Then again, someone might make a mistake. "What do you mean 09-11 is not November 9th, you silly American." Now that I think of it, I guess the best argument against natural keys is "typos".
ASipos 9y ago
I studied SQL as the lab portion of a database design course. The sequel to that course was wholly dedicated to PL/SQL.
- neuromantik8086 9y ago
  And the sequel to that was postgrad?
- combatentropy 9y ago
  The sequel to the SQL? ;)
  - oblio 9y ago
    Hey, some of us pronounce it S-Q-L :p
cheez 9y ago
Natural keys can be composite which makes it a pain to manage cross-table relationships. I typically have a unique constraint on the actual composite key and a auto inc id.
- gnaritas 9y ago
  That is the correct solution, a surrogate key.
  - cheez 9y ago
    Good to know, thanks!
scriptkiddy 9y ago
> The pervasive use of artificial keys. USE NATURAL KEYS. Unfortunately probably 99% of real-world databases were designed with artificial keys. I wish I could point to some literature on this topic. It is very rare and I only came to learn about this from a DBA who is well-versed in designing with natural keys. I'm trying to get him to publish more on this topic. While I really enjoy the concept of Natural Keys, I just see so few places where they are applicable. If I was designing a banking Db schema, I could see using an account number as a primary key as long as I am not exposing my Db to anything but internal systems. However, if I'm designing a social networking platform, what can I use as a natural primary key for a user object? I can't use a name because not all names are unique and they can change. I can't use an email because they can change and I feel like Email addresses can get pretty large(more space and slower lookups). I could maybe use a username if I enforce that a username can never change and must be unique. But, we then run into the same issues as emails where a username can be fairly long and thus cause slower lookups. I also don't like the idea of using strings as primary keys either because I would need to take into account implementation details like string encoding (utf8, utf16, utf32, ascii, latin-1) and make sure to encode/decode on every lookup/insert. So, I can see some use cases where natural primary keys make sense. However, I believe that for most use cases, artificial keys are a better choice. Integers don't take up a lot of space, it's easy to enforce uniqueness, they are a natural sequence, and they rarely, if ever need to change[1]. [1] In fact, I would make the argument that if you're altering primary keys at all, you're doing something wrong.
- jackfoxy 9y ago
  Properly identifying the natural keys requires more up front thinking than slapping on an identity or guid column. Also it ends up with a different set of tables at the end of the day. So looking at your current schema and saying natural keys don't work here is probably true. It's a big topic, and like I said, not enough literature, but if you search sql natural key you can get started.
  - gnaritas 9y ago
    There are no proper natural keys, natural keys are a bad solution to the real world problem of running and managing an application. The correct solution is to use a constraint to enforce the uniqueness of your supposedly "natural key", and use an artificial key to actually join and code against because quite frankly natural keys suck and are commonly multi-column keys that require updating and joining against all of which make them poor choices for programming against. Surrogate keys don't break your app and code when business rules change or you finally figure out your natural key isn't so natural and has exceptions that forces you to make it not a key. If you think natural keys are a good solution, you've not lived outside of the database where real $$ is on the line. Those who live in a SQL ivory tower where breaking changes don't matter like natural keys; those who actually write applications that use databases and have to deal with the constant churn and change of business rules know how foolish natural keys are and being pragmatic and understanding how to correctly use a pointer to avoid the need to cascade updates and make the schema immune to business rule changes have long ago chosen surrogate keys which are the vastly superior engineering solution.
gnaritas 9y ago
1) Natural keys suck in the real world, artificial keys have come to dominate for a real reason that you either understand or suffer for ignoring. Natural keys are bad practice. 2) ORM's are good practice, they reduce massive amounts of duplicated code in applications and massively lessen development time which generally dwarfs the cost of execution time. If you don't use an ORM you are pissing money down the drain, most businesses are reluctant to do that. Using an ORM doesn't mean not using SQL when appropriate, it means using the ORM in the 90% of cases where it works best. Complex SQL belongs in views, which are then trivially mapped with the ORM giving you the best of both worlds. If you're doing complex queries with the ORM, the ORM isn't the problem, you are. 3) Should be. 4) Nor should they, programmers are expensive, paying them to do what a library can do better is a waste of money and time. You need a guy or two who knows SQL well enough to tune query and do indexing, that's your db guy.
pjungwir 9y ago
> There are really no unsolved problems (of note), so it's not interesting to academics. For anyone who likes SQL and is looking for a research topic, read about temporal databases and suggest some ways to handle DDL changes over time (e.g. adding a new NOT NULL column, or changing a relationship from one-to-many to many-to-many). Here is your starting bibliography: Richard Snodgrass, Developing Time-Oriented Database Applications in SQL. Hugh Darwen & C.J. Date, "An Overview and Analysis of Proposals Based on the TSQL2 Approach". Krishna Kulkarni & Jan-Eike Michels, "Temporal features in SQL:2011". Tom Johnston, Bitemporal Data Magnus Hagander, "A TARDIS for Your ORM": https://www.youtube.com/watch?v=TRgni5q0YM8 https://www.youtube.com/watch?v=TRgni5q0YM8 I would love to read what you come up with!
- nickpeterson 9y ago
  I would argue that [http://www.AnchorModeling.com http://www.AnchorModeling.com] is a pretty good take on it. I think it shares some genealogy with Fact-Based Modeling techniques (FCO-IM?). It has a really decent story for evolving the schema over time. I've read Darwen/Date, Snodgrass, and Johnston's (two books) on the subject. Johnston's seems the best practical choice, but they patented the ideas :/ That said, one could probably implement Johnston's model without violating patents if you ignore explicitly modeling the episode structure and just follow the theory. Date and Darwen make decent points about wanting to use the relational model rather than some baked-in concept, but ultimately they do almost no legwork on practically putting any of their notions to use. This seems to be par for the course on Date books. Weis and Johnston handle the problem more directly. They also tackle a harder problem overall (BiTemporal, vs just Temporal). Also Johnston is just easier to read than Date. (Side note, I feel like a blog where I just read chapters of Date writings and condense the content basic points would end in most of Date's arguments fitting in 50 pages. That said, Weis and Johnston still punted on schema evolution. Anchor Modeling sort of starts at supporting an evolving schema and moves outward from there. In Anchor modeling there isn't the traditional Temporal/Bi-temporal notion, but rather positors than have varying degrees of certainty about posits. There is basically a concurrence of facts that can be retracted or changed over time. Useful for modeling varying degrees of certainty or alternate perspectives on data. The downside of Anchor Modeling is that the datamodel is basically 6th normal form with a bunch of table valued functions, triggers, and views to aid in making it palatable for devs. Johnston sort of acknowledges this style in one of his books but I believe argues against the concept of an entities information being spread throughout various tables. In his mind, a table is a type, that is made of attributes (columns). Anchor modeling is more along the lines of 'Attributes are types', and you can relate them to form larger types. I wish there was more work on the ORM side supporting some of these concepts (Schema evolution and temporality).

sobani 9y ago

Can you give 1 example of a good natural key? Note I will disqualify anything that has a reasonable chance of changing, like the primary email address of an account, a persons name, a persons day of birth or the 'public id' of a bank account

yawaramin 9y ago
A composite key made up of foreign keys in a junction table.
irishsultan 9y ago
How often does the birthday of people change? (Then again, how likely is it to be unique?)
- sobani 9y ago
  The closest example I can give is that I work on a system where the birthday can be partially unknown. This mostly affect refugees. So the person either has to guess his own age, or only knows the year, or only the year and month. Then again, someone might make a mistake. "What do you mean 09-11 is not November 9th, you silly American." Now that I think of it, I guess the best argument against natural keys is "typos".

ASipos 9y ago

I studied SQL as the lab portion of a database design course. The sequel to that course was wholly dedicated to PL/SQL.

neuromantik8086 9y ago
And the sequel to that was postgrad?
combatentropy 9y ago
The sequel to the SQL? ;)
- oblio 9y ago
  Hey, some of us pronounce it S-Q-L :p

cheez 9y ago

Natural keys can be composite which makes it a pain to manage cross-table relationships. I typically have a unique constraint on the actual composite key and a auto inc id.

gnaritas 9y ago
That is the correct solution, a surrogate key.
- cheez 9y ago
  Good to know, thanks!

scriptkiddy 9y ago

> The pervasive use of artificial keys. USE NATURAL KEYS. Unfortunately probably 99% of real-world databases were designed with artificial keys. I wish I could point to some literature on this topic. It is very rare and I only came to learn about this from a DBA who is well-versed in designing with natural keys. I'm trying to get him to publish more on this topic. While I really enjoy the concept of Natural Keys, I just see so few places where they are applicable. If I was designing a banking Db schema, I could see using an account number as a primary key as long as I am not exposing my Db to anything but internal systems. However, if I'm designing a social networking platform, what can I use as a natural primary key for a user object? I can't use a name because not all names are unique and they can change. I can't use an email because they can change and I feel like Email addresses can get pretty large(more space and slower lookups). I could maybe use a username if I enforce that a username can never change and must be unique. But, we then run into the same issues as emails where a username can be fairly long and thus cause slower lookups. I also don't like the idea of using strings as primary keys either because I would need to take into account implementation details like string encoding (utf8, utf16, utf32, ascii, latin-1) and make sure to encode/decode on every lookup/insert. So, I can see some use cases where natural primary keys make sense. However, I believe that for most use cases, artificial keys are a better choice. Integers don't take up a lot of space, it's easy to enforce uniqueness, they are a natural sequence, and they rarely, if ever need to change[1]. [1] In fact, I would make the argument that if you're altering primary keys at all, you're doing something wrong.

jackfoxy 9y ago
Properly identifying the natural keys requires more up front thinking than slapping on an identity or guid column. Also it ends up with a different set of tables at the end of the day. So looking at your current schema and saying natural keys don't work here is probably true. It's a big topic, and like I said, not enough literature, but if you search sql natural key you can get started.
- gnaritas 9y ago
  There are no proper natural keys, natural keys are a bad solution to the real world problem of running and managing an application. The correct solution is to use a constraint to enforce the uniqueness of your supposedly "natural key", and use an artificial key to actually join and code against because quite frankly natural keys suck and are commonly multi-column keys that require updating and joining against all of which make them poor choices for programming against. Surrogate keys don't break your app and code when business rules change or you finally figure out your natural key isn't so natural and has exceptions that forces you to make it not a key. If you think natural keys are a good solution, you've not lived outside of the database where real $$ is on the line. Those who live in a SQL ivory tower where breaking changes don't matter like natural keys; those who actually write applications that use databases and have to deal with the constant churn and change of business rules know how foolish natural keys are and being pragmatic and understanding how to correctly use a pointer to avoid the need to cascade updates and make the schema immune to business rule changes have long ago chosen surrogate keys which are the vastly superior engineering solution.

gnaritas 9y ago

1) Natural keys suck in the real world, artificial keys have come to dominate for a real reason that you either understand or suffer for ignoring. Natural keys are bad practice. 2) ORM's are good practice, they reduce massive amounts of duplicated code in applications and massively lessen development time which generally dwarfs the cost of execution time. If you don't use an ORM you are pissing money down the drain, most businesses are reluctant to do that. Using an ORM doesn't mean not using SQL when appropriate, it means using the ORM in the 90% of cases where it works best. Complex SQL belongs in views, which are then trivially mapped with the ORM giving you the best of both worlds. If you're doing complex queries with the ORM, the ORM isn't the problem, you are. 3) Should be. 4) Nor should they, programmers are expensive, paying them to do what a library can do better is a waste of money and time. You need a guy or two who knows SQL well enough to tune query and do indexing, that's your db guy.

pjungwir 9y ago

> There are really no unsolved problems (of note), so it's not interesting to academics. For anyone who likes SQL and is looking for a research topic, read about temporal databases and suggest some ways to handle DDL changes over time (e.g. adding a new NOT NULL column, or changing a relationship from one-to-many to many-to-many). Here is your starting bibliography: Richard Snodgrass, Developing Time-Oriented Database Applications in SQL. Hugh Darwen & C.J. Date, "An Overview and Analysis of Proposals Based on the TSQL2 Approach". Krishna Kulkarni & Jan-Eike Michels, "Temporal features in SQL:2011". Tom Johnston, Bitemporal Data Magnus Hagander, "A TARDIS for Your ORM": https://www.youtube.com/watch?v=TRgni5q0YM8 https://www.youtube.com/watch?v=TRgni5q0YM8 I would love to read what you come up with!

nickpeterson 9y ago
I would argue that [http://www.AnchorModeling.com http://www.AnchorModeling.com] is a pretty good take on it. I think it shares some genealogy with Fact-Based Modeling techniques (FCO-IM?). It has a really decent story for evolving the schema over time. I've read Darwen/Date, Snodgrass, and Johnston's (two books) on the subject. Johnston's seems the best practical choice, but they patented the ideas :/ That said, one could probably implement Johnston's model without violating patents if you ignore explicitly modeling the episode structure and just follow the theory. Date and Darwen make decent points about wanting to use the relational model rather than some baked-in concept, but ultimately they do almost no legwork on practically putting any of their notions to use. This seems to be par for the course on Date books. Weis and Johnston handle the problem more directly. They also tackle a harder problem overall (BiTemporal, vs just Temporal). Also Johnston is just easier to read than Date. (Side note, I feel like a blog where I just read chapters of Date writings and condense the content basic points would end in most of Date's arguments fitting in 50 pages. That said, Weis and Johnston still punted on schema evolution. Anchor Modeling sort of starts at supporting an evolving schema and moves outward from there. In Anchor modeling there isn't the traditional Temporal/Bi-temporal notion, but rather positors than have varying degrees of certainty about posits. There is basically a concurrence of facts that can be retracted or changed over time. Useful for modeling varying degrees of certainty or alternate perspectives on data. The downside of Anchor Modeling is that the datamodel is basically 6th normal form with a bunch of table valued functions, triggers, and views to aid in making it palatable for devs. Johnston sort of acknowledges this style in one of his books but I believe argues against the concept of an entities information being spread throughout various tables. In his mind, a table is a type, that is made of attributes (columns). Anchor modeling is more along the lines of 'Attributes are types', and you can relate them to form larger types. I wish there was more work on the ORM side supporting some of these concepts (Schema evolution and temporality).