News for hackers. Stuff that matters.
Re:SQL is 43 years old – Here’s why we still use it today by jackfoxy on Tuesday May 02, 2017 @12:27PM (#14247800)
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/ , 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.)

Re:SQL is 43 years old – Here’s why we still use it today by scriptkiddy on Tuesday May 02, 2017 @01:17PM (#14248332)
> 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.

Re:SQL is 43 years old – Here’s why we still use it today by jackfoxy on Tuesday May 02, 2017 @04:28PM (#14250267)
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.
Re:SQL is 43 years old – Here’s why we still use it today by gnaritas on Tuesday May 02, 2017 @05:20PM (#14250738)
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.

Re:SQL is 43 years old – Here’s why we still use it today by amilevin on Monday May 08, 2017 @02:33PM (#14294282)
Hi Gnaritas,

I would love to have a discussion with you on the topic, but to get started I think we'll need a little more solid ground than the over-generalized, empty claims you made: "There are no proper natural keys" / "Natural keys are a bad solution" / "The correct solution is.." / "Natural keys suck" etc. Can you provide viable/scientific/quantitative/reproducible/logical, either practical or theoretical arguments to support these claims? If you can, I would be happy to discuss those with you in detail.

BTW - If you need to change your database schema when your business rules change, the root problem is your data model. It means your database schema models the business rules, instead of modeling the data universe.

Before we start the discussion, I would like to suggest that you read the following two short articles. I think it will give us a more solid common terminology.

http://www.informationweek.com/software/information-manageme...?

http://www.informationweek.com/software/information-manageme...?

Cheers,

Ami

Re:SQL is 43 years old – Here’s why we still use it today by brantam on Tuesday May 09, 2017 @05:14AM (#14298628)
> the correct solution is to use a constraint to enforce the uniqueness

That would of course be correct for a natural key. It seems that you too recommend using natural keys and I agree with you on that point. Natural keys are required so that a database can represent the real world accurately and enforce essential business rules. We all rely on websites to enforce the uniqueness of user names; we all expect our bank to enforce the uniqueness of account numbers.

Those who live in an ivory tower where data quality doesn't matter don't like natural keys; those who write applications that have to work in the real world know how foolish it is to leave out natural keys. Those older and wiser heads will have seen and dealt with the serious practical consequences of shoddy databases where natural keys weren't implemented. I think that is the problem being referred to in this thread.

Re:SQL is 43 years old – Here’s why we still use it today by gnaritas on Tuesday May 02, 2017 @01:41PM (#14248619)
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.

Re:SQL is 43 years old – Here’s why we still use it today by pjungwir on Tuesday May 02, 2017 @03:36PM (#14249808)
> 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

I would love to read what you come up with!

Re:SQL is 43 years old – Here’s why we still use it today by nickpeterson on Thursday May 11, 2017 @12:52AM (#14313903)
I would argue that [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).

Re:SQL is 43 years old – Here’s why we still use it today by jackfoxy on Tuesday May 02, 2017 @04:30PM (#14250286)
Yes, I would agree, temporal databases is an interesting topic.
Re:SQL is 43 years old – Here’s why we still use it today by cheez on Tuesday May 02, 2017 @01:15PM (#14248320)
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.
Re:SQL is 43 years old – Here’s why we still use it today by gnaritas on Tuesday May 02, 2017 @05:58PM (#14251009)
That is the correct solution, a surrogate key.
Re:SQL is 43 years old – Here’s why we still use it today by cheez on Tuesday May 02, 2017 @07:45PM (#14251781)
Good to know, thanks!
Re:SQL is 43 years old – Here’s why we still use it today by sobani on Wednesday May 03, 2017 @04:57AM (#14254063)
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

Re:SQL is 43 years old – Here’s why we still use it today by yawaramin on Sunday May 07, 2017 @02:13PM (#14286671)
A composite key made up of foreign keys in a junction table.
Re:SQL is 43 years old – Here’s why we still use it today by irishsultan on Wednesday May 03, 2017 @07:58AM (#14254931)
How often does the birthday of people change? (Then again, how likely is it to be unique?)
Re:SQL is 43 years old – Here’s why we still use it today by sobani on Wednesday May 03, 2017 @08:45AM (#14255163)
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".

Re:SQL is 43 years old – Here’s why we still use it today by ASipos on Tuesday May 02, 2017 @01:09PM (#14248253)
I studied SQL as the lab portion of a database design course. The sequel to that course was wholly dedicated to PL/SQL.
Re:SQL is 43 years old – Here’s why we still use it today by neuromantik8086 on Tuesday May 02, 2017 @01:21PM (#14248392)
And the sequel to that was postgrad?
Re:SQL is 43 years old – Here’s why we still use it today by combatentropy on Wednesday May 03, 2017 @02:44AM (#14253552)
The sequel to the SQL? ;)
Re:SQL is 43 years old – Here’s why we still use it today by oblio on Wednesday May 03, 2017 @06:58AM (#14254589)
Hey, some of us pronounce it S-Q-L :p