Subject: database assignment: hints - Part II



in the last post, i sort of backed into the technique for identifying
Attributes by introducing inheritance and the idea of modeling complex
relationships by throwing another entity in the middle.   it's easier
to generate flexible, robust systems that way.   the older technique
is to list all the attributes you'd normally expect a given entity to
have, and frankly, that doesn't work very well.

the problem is that at some point, you have to model your
relationships with attributes.   if you don't choose attributes which
support the relationships you want, it can be difficult or impossible
to make your model do what you want.   worse yet, the inventory
technique doesn't give you any way to locate the problem in your
model.. all the attributes you've chosen will be valid, they're just
not *useful*.

if you model the relationships first, then create entities which
support them, most of those intermediate entities end up being
attributes of the things they connect.   the "relationship entity"
tells you that the entities on each side need to know how to find each
other.   sometimes you'll want to put an explicit entity between two
others, and sometimes you'll collapse the relationship entity into
attributes of the entities it connects.


for instance, with the relationship:

    [ instructor ]
        |
        |---[ course list ]
                |
                |---[ course ]


should you have a separate table for course lists or not?

if you want to do complicated things with your teaching assignments,
like pass one instructor's assignments off to another for a vacation,
it will probably be best to use a separate table.. that way you can
add attributes like duration to your teaching assignments:

    TEACHING ASSIGNMENTS
    
    instructor A, course 1, start date, end date
    instructor A, course 2, start date, end date
    instructor A, course 3, start date, end date
    instructor B, course 4, start date, end date
    instructor B, course 5, start date, end date
    instructor C, course 6, start date, end date


and when finding either an instructor's current list of courses, or
who's teaching a specific class today, it would be easiest just to
query the assignments table.


OTOH, if your teaching assignments are highly stable and don't need
that kind of flexibility (famous last words), you can collapse the 
information into attributes of the INSTRUCTORS and COURSES tables:

    INSTRUCTORS
    
    instructor A, office, course 1; course 2; course 3
    instructor B, office, course 4; course 5
    instructor C, office, course 6


    COURSES
    
    course 1, instructor A, location, schedule
    course 2, instructor A, location, schedule
    course 3, instructor A, location, schedule
    course 4, instructor B, location, schedule
    course 5, instructor B, location, schedule
    course 6, instructor C, location, schedule


either way is equally valid, depending on your circumstances.


building your model with entities to represent relationships makes it
easier to migrate from one implementation to another.   you know what
the relationship is, and you know how it's been implemented, so you
know what has to be changed to make the new version of the
relationship work.   

most of the frustration in database development comes from people
wanting to add new features to some relationship.   if your
relationship is already modeled with an entity, you have the
flexibility to add new attributes as you need them.   if you haven't
developed your model of the system that completely, you'll find that a
lot of stuff has been hardcoded in, and that you don't know where to
put the new features.

a further advantage of knowing how your system can flex is that it
allows you to build your system piecewise.   you can start off by
modeling a relationship with attributes, make sure the basic idea
works, then migrate over to a new table and start implementing all the
flashy stuff.   not only are you confident that the fundamentals are
stable, you have something that works, and can be delivered if it
really has to be.   far too many projects never deliver anything,
because the developers tried to do the hardest (aka: most interesting)
part first.

the trick of making systems that actually work is to move in small
steps, but to know how to get from the current step to the next most
complicated one.