Relationships are harder than you think
Working with many-to-many relationship tables
My latest Ask the Expert article, Books and authors, many-to-many, gives a simple example of a relationship table. I've tried to stop calling it a "junction" table, a habit I learned in network (i.e. pre-relational) database design many years ago. Today, by sheer coincidence, I see that word again, in a column by Joe Celko called Hollywood Couples.
The sample code in Joe's article is hard to read, but that's clearly not his fault. That site is obviously more concerned with serving ads than making its content legible (view the HTML source, if you have a strong stomach). But reading though Joe's commentary and code examples is well worth the effort. You will find some real gems in there.
For example, suppose you had a database to track teachers, classes, rooms, and periods. What would you do to ensure that:
- a teacher is in only one room each period
- a teacher teaches only one class each period
- a room has only one class each period
- a room has only one teacher in it each period
I know what a lot of programmers would do, and it's not pretty.
Relationships may be harder than you think in real life, but database relationships are actually quite straightforward. Designed properly, they can avoid a lot of unnecessary programming.