Multiplicity, Change and MV
Date: 10 Apr 2006 09:24:17 -0700
Message-ID: <1144686256.966117.179590_at_e56g2000cwe.googlegroups.com>
Change bothers me. Especially in database schema, and specifically when we want to accomodate change in the cardinalities of the relationships we are modelling. Below I've setup a contrived example of lecturer-course, which a school initially deems to be a highly constrained 1-1 relationship, with (ID) serving as a surrogate primary key for the sake of clarity.
Courses = { (name:French), (name:English), (name:German) } Lecturers = { (id:1, name:Tom, teaches:French), (id:2, name:Bob, teaches:English) }
i.e.:
id name teaches
1 Tom French
2 Bob English
Courses = { (name:French), (name:English), (name:German) }
Lecturers = { (id:1, name:Tom), (id:2, name:Bob) }
Teaching = {
(teacher_id:1, course:French),
(teacher_id:1, course:German),
(teacher_id:2, course:English),
}
Apologies for this basic (and contrived) example, but hopefully its sufficient to show that change will have knock on effects to the queries of extant applications which interact with the database.
This is what I'd like to focus on here - I am interested in exploring how one might reduce the dependency between query-form and db-structure. I find it jarring that a change in multiplicity has introduced a new relation - perhaps it should have existed in the first place even with the 1-m relationship? Either way, going against my instincts, below I'm considering MV approaches, of which I can determine two:
MV - Set/List value approach (Pick?):
Courses = { (name:French), (name:English), (name:German) } Lecturers = {
(id:1, name:Tom, teaches: {French, German} ),
(id:2, name:Bob, teaches: English )
}
Immediately I've lost the added functionality of having a teaching relation - but that's not a current concern because *at the moment* I'm focusing on preserving query form. A greater concern is that a set (or list if one prefers) has now materialized where previously none existed. I am currently unclear how to tally this with the underlying predicate logic that is being encoded - there is also the added complications of how to interpret equality comparisons for concepts such as WHERE clauses given the introduction of aggregates.
MV - Multiple-attribute approach:
Courses = { (name:French), (name:English), (name:German) } Lecturers = {
(id:1, name:Tom, teaches:French, teaches:English),
(id:2, name:Bob, teaches:French, teaches:German)
}
Here I do not have the concern of sets/lists appearing from the ether, but I have lost the guaranteed regularity of the relation (that all tuples are of the same cardinality) - however I am currently unsure as to whether this is actually a concern outside of tabular visualization.
Further a tuple is obviously no longer a function mapping given the field name repetiton, yet it is still a mathematical relation. More of a concern is that with the loss of functional mapping it necessarily loses the concept of functional dependency - although I believe that I can still state conditional/biconditional dependencies.
In the area I work in, changes in cardinality and table schema are rife. Scientific data, especially the way we want to contextualize and record it, is changing constantly (this is one of the reasons that the hideous EAV model got so much funding). Perhaps I just have to accept structural and hence query change via the RM, but perhaps there is a solution that will suit my theoretic demands as well as being practically effective?
I am interested in the validity, or the lack thereof, of any of these comments. Received on Mon Apr 10 2006 - 18:24:17 CEST