![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
Entry tags:
learning new patterns
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.)
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.)
no subject
Like
Storage for analytic scenarios can be a different matter... data may be denormalized back for aggregation. Looks like I will be learning more about this soon myself.
"Coming to the world of SQL databases from the world of object-oriented programming is...different."
It is just as weird coming from the other direction :)
no subject
It is just as weird coming from the other direction :)
I imagine so. :-)
no subject
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:
But you don't ever store it that way, except in some sort of cache, because data hygiene gets hard/impossible.
no subject
no subject
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.
no subject
no subject
no subject
I was sufficiently intrigued by the sheer diversity of terms for these things, that I held a poll on it. "Join table" was the clear winner, but "map", "link(|ing|y])", and "(x|cross|)ref(|erence])" all had multiple endorsements.
no subject
no subject