Re: Multiplicity, Change and MV

JOG wrote:
> 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
> ---------------
>
> Now, if multiplicity occurs due to a change in teaching practices, the
> db-structure obviously has to change in order to accomodate this. For
> example, below the teacher-course relationship has evolved into a
> many-many relationship, so producing a new relation, with a primary key
> of (teacher_id, course).
>
> 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:
>
[snip]

This difficulty has been long recognized as a consequence of the EAR approach to database design. Since Chen's first paper, it's been apparent that neither "entity" nor "relationship" were fundamental concepts. (The fact that new "entities" could spring out of many-to-many "relationships" is an obvious clue.)

The most resilient designs are those which accurately represent data, not relationships. In that vein, I've found Halpern's ORM approach to be helpful -- although his goal of discovering objects is, I feel, slightly off the mark. Received on Wed Apr 12 2006 - 12:07:26 CEST