learning new patterns

[cellio]

Coming to the world of SQL databases from the world of object-oriented programming is...different. I'm starting to realize why some idioms are different, and I'm sure there are tons more that I haven't noticed yet and am probably getting wrong. But that's what learning experiences are for.

Consider, for example, a system where you have authors with associated publications. If I were designing a system to track that in, say, Java, I would define an Author class and a Publication class, with bidirectional links (Author would have a collection of Publications; Publication would have a collection of Authors (because sometimes authors collaborate)). But in a database table design you don't do that; you define a Persons table that has columns for some unique ID, name, and anything else about the person, and you have a Publications table that has columns for things about the publication like a (book) unique ID, title, publisher, genre, etc, and also the unique ID from the Persons table for the author -- and I'm not sure if multiple authors means multiple rows in the Publications table or if there's some way to do collections. But the point is that a Person doesn't know about its publications -- when you want that you'll do a JOIN between the two tables and then you'll have what you need. Connections between flavors of data are external to the data. This makes sense, but it's going to take a little getting used to.

(Y'all who are way ahead of me on this should please feel free to point out any errors in the above and save me mis-learning some things. Thanks.)

Comments

[cellio]

This is actually in an analytics context (I recently heard somebody recommend denormaliing data to solve some problem!), which I failed to specify. I don;t yet have a sense of how often data is normalized in the kinds of problem spaces we're in.

It is just as weird coming from the other direction :)

I imagine so. :-)

[siderea]

I recently heard somebody recommend denormalizing data to solve some problem!

Just to make clear: denormalization is the necessary and normal output of any interesting query. Denormalization is fine in output.

Storing data denormalized... denormalizing is the devil you make deals with, see.

ETA: Wait, what do you mean by "denormalization"? In RDBMSs, it means things like "multiple authors means multiple rows in the Publications table". Your result set of "books and their authors" will look like:

The C Programming Language                        | Kernighan
The C Programming Language                        | Ritchie
Structure and Interpretation of Computer Programs | Abelson 
Structure and Interpretation of Computer Programs | Sussman

But you don't ever store it that way, except in some sort of cache, because data hygiene gets hard/impossible.

[cellio]

I thought the comment was about storing rather than returning data, but I may have misunderstood. I'll have to check my notes.

[zare-k.livejournal.com]

"I recently heard somebody recommend denormaliing data to solve some problem!"

Generally motivated by performance concerns. There is some cost to joining a bunch of entities back together for aggregation. Although, you will occasionally hear a blanket "joins are bad!" which is just silly... especially with current RDBMSes that have very advanced query optimizers.