Many records for a 'thing', vs. 1 w/ a join table

[Bob7]

Let me eleborate. I'm working on a db design, and so far my ERD has 19 tables, 10 of them join tables. The reason is I initially have lots of many-to-many relationships, like coordinators being 'in charge of' one or more courses at any given point in time. If the courses are thought of as being only ones offered only during any given academic quarter (e.g., Spring 2006), then one coordinator has zero, 1, or many courses. But if the course is thought of as a course instance, meaning course X has a record (in a 'course' table) for every academic quarter it is offered, then the relationship is many to many. Based on the latter scenario, I sketched out a join table which is 'course instance'. The other approach might be to leave out the join table, and create multiple course records (for each instance of each course) in the course table. I'm leaning toward using the join table.

My entire (current ERD) is full of this kind of situation, so whatever route I choose may affect the entire design. Any thoughts on this appreciated.

Bob

[T-Square]

Bob--

Adding join tables increases the flexibility of the system, but introduces complexity that you as developer have to contend with.

The factors to consider here are: how static are your courses, how many courses are you talking about, and how much information does each course record contain?

If you're talking about thousands of courses that don't change much from term to term, with lots of information for each course, then I'd go with the join table approach. If, however, you're talking about a few hundred courses which can change from term to term, I'd go without the join file.

In fact, that's what I have done in the past. My experience is that no course truly stays exactly the same from term to term, and for most situations, the number of courses doesn't warrant the extra work.

If there is a lot of similar information from one term to the bext, you could always add a "Duplicate this Course" button on your layout that would copy most of the data from one record to another (I would prefer this to using the Duplicate Record feature in FM so that a course would not accidentally end up in last semester).

HTH,

David

[Ender]

If you're talking about thousands of courses that don't change much from term to term, with lots of information for each course, then I'd go with the join table approach. If, however, you're talking about a few hundred courses which can change from term to term, I'd go without the join file.

I'm not sure what the distinction is here. Hopefully you can clarify.

Bob, I would say join tables are a necessary and useful part of database design. For each relationship between entities on your ERD, you should ask the question (to yourself or your clients,) "For each one of these, could there be more than one of those?" Do this for each direction on the relationship. If the answer for each direction is "Yes", then you probably need a join table. As an example, take a relationship between Course and Student:

"For each Course, could there be more than one Student?" This one is obviously "Yes".

"For each Student, could there be more than one course?" This would be "Yes" if you want to remember multple years, quarters, of classes for each Student.

For a tougher one, look at the relationship between Course and Teacher.

"For each Course, could there be more than one Teacher?" Even if a class is only tought by one Teacher at a time, this could still be "Yes", if there could be different Teachers for different years, or quarters.

"For each Teacher, could there be more than one Course?" This is "Yes" if a Teacher teaches more than one Course in the same quarter, or if a Teacher could teach different Courses in different years or quarters.

Then again, some of this will change if you decide to use a separate table for Course Sections.

If you want to post your ERD, we may be able to offer suggestions, or catch any potential problems. Of course, you and your clients are really the ones who understand the requirements best.

[Bob7]

Great ideas, thanks. I've posted the current ERD:

ERD Link

I'll attempt a quick and dirty explanation (short on time now). A resource center has employees who work with coordinators (they are teachers too, btw) to bring in new resource assets (audio files, etc.) to be used by students. Courses and coordinators are linked with departments. We list the content of each resource asset member. We tie the resource assets to course sections (bc not all course sections necessarily use the same asset as the course). Each course instance occurs during an academic quarter, e.g., "Spring 2006". Assets are derived from a "Publisher" (whomever made the source resource asset).

Bob

Edited April 11, 2006 by Guest

[T-Square]

Ender--

I think I misunderstood Bob's original question. I thought Bob was asking about a relatively simple course registration database, but I see now that he was not.

What I was getting at was that normalizing data structures can yield many benefits, but that there are times when the added complexity of the normalized structure doesn't yield sufficient benefit. If Bob's course descriptions mostly change from term to term, then the whole point of the separate Courses entity becomes moot, since you would end up creating a new Course instance for each term. There would be little re-use of the course data.

I was imagining a course catalog database where there were courses that were offered in different terms with different teachers, times, and locations. I see from Bob's ERD that he's doing way more than that.

In a simpler course registration database, I could see flattening the structure and storing each course offering for each semester in a separate record. Each course would then incorporate attributes that set it in time and place, rather than separating that into another table. Obviously, identifying the teachers and students would require join tables in any case.

I hope that clarifies it.

David

[Ender]

Ho Bob,

I'm afraid I don't know enough about what's all involved in this resource management business to say much about your ERD. When I think of managing assets, I pretty much just want to know where it is, where it was, and where where we got it. Your system seems to track a lot more.

One thing that stands out is the Asset field in the join table, "LLCEmployee|LangDept_Instance." I wonder if it's necessary since Assets are already being tracked through the other join tables.

I also wonder about the "LangDept|Course_Instance" join table. I mean, could a Course really belong to more than one LangDept?

Anyway, hope that helps.

[Bobfmf]

One thing that stands out is the Asset field in the join table, "LLCEmployee|LangDept_Instance." I wonder if it's necessary since Assets are already being tracked through the other join tables.

These employees have very short employment terms, say 1 year. The join table to to be able to see which employee was associated with which department at any given point in time. IO think your hunch is right, that it may not be directly involved with tracking assets, but it is something desired to be tracked.

I also wonder about the "LangDept|Course_Instance" join table. I mean, could a Course really belong to more than one LangDept?

As it turns out, that actually is the case. Not very common, but it does occur. Good catch!

Yes, very much so! It's very useful to have the design looked at and considered by others.

Bob