![[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
The way I deal with it practically is that I have a little mental decision tree with about three branches based on the type, multivaluedness, and reciprocity of the object reference field, and then each leaf on the tree maps to some archetypical RDB structure that represents that reference type. It doesn't actually take that long to build this up.
goldsquare's comment that relationships in RDBland can be dynamic queries is worth emphasizing. If in objectland you have a Patient who has a list of Prescription, the Patient has a List<Prescription> multivalue objref and the Prescription has a "patient" objref. In RDBland this manifests itself as a "patient_id" foreign key column on the prescription table and nothing in the patient table. Physically, only prescriptions reference patients. Conceptually, a patient is linked to its prescriptions by the query SELECT * FROM prescription WHERE patient_id = [the patient id], which, because this is an RDB and you probably indexed that column, is reasonably fast.