Does anyone have any suggestions on how I can improve the design below? I think I have it all wrong. Thanks in advance!

A is the parent of B and C. Sometimes, many B rows can be associated with many C rows and vice versa. D was created as a join table between B and C. This will ensure that B and C both exists but does not guarantee that they reference the same A row.

A { a_id PK }
B { b_id PK, a_id FK }
C { c_id PK, a_id FK }
D { b_id & c_id PK }

Ex:

A
====
a1
a2

B
======
b1 a1(fk)

C
======
c1 a1(fk)
c2 a2(fk)


D
======
b1 c1 OK...b1 and c1 share same a FK (a1)
b1 c2 NO...b1 and c2 do not share same a FK (a1!=a2)

not really

i mean, you described how the current design works, so if it needs "improving" then you will have to explain the objectives that the design is supposed to achieve and why your current design isn't achieving it

r937 ... Thanks for your reply.
I believe the design will achieve the goal but I'm no design expert. I am hoping to get opinions from those with more experience on whether or not the model is acceptable given the requirements (see bottom of this post).

A real world situation would probably make more sense. I'm working on an offender management system for my employer. In our domain, we work with different clients. Some are Subjects who have one or more Charges and who may or may not be required to take one or more Classes because of a charge. Some are subjects with no charges who want to take a class. In addition, one class may be required for several of the subject's charges and many classes may be required for a single charge. The real world is crazy huh?

Here's the current model...

Subjects { subject_id PK }
Charges { charge_id PK, subject_id FK }
Classes { class_Id PK, subject_id FK }
ClassesOnCharges { class_id PK, subject_id PK }

Note: The attachment contains an image of the current model.

The model would allow our officers to enter a subject with a charge and any classes required for that charge. It would also allow us to enter a subject, without a charge, that wants to take a class. It also ensures that both Charge and Class exists in the database.

But, I believe there is a referential integrity issue with the ClassesOnCharges table because it would allow a Charge and Class to be entered that belong to different Subjects (different subject_ids). I have considered adding a subject_id field to the ClassesOnCharges table but when I saw all the relations, I felt like I had a poor design. Any thoughts ... would you consider this a poor design?

Here are a few requirements within our domain:

1) Joe Offender gets a Worthless Checks charge and is required to take a Financial Responsiblity class. The officer needs to know that Joe is taking the FR class because he was charged with WC.

2) Joe Offender is referred to us from a third-party provider to attend our MRT class. Joe doesn't have a charge but he will be attending our class. The officer needs to know that Joe is attending the MRT class and that he doesn't have a charge.

3) Joe Offender is charged with DUI and is required to take our DUI class, MRT class and more. The officer needs to be able to enter more than one required class for a single charge and track them.

4) Joe Offender receives several charges (DUI, DOR, and more) and is required to take several classes (DUI, MRT and more). The officer needs the ability to enter a series of charges and associate them with a series of classes and track them.

the problem i have with this design is as follows

a charge is unique, yes? DUI is DUI, no matter who is charged with it

however, by making charge_id the PK and subject_id the FK, this structure means that DUI can only be charged to one subject at a time?

i think the Charges table should describe only the charge in general, and then you need another, many-to-many table that relates charge and subjects, which has a composite primary key consisting of charge_id and subject_id

similar comments on CLasses

then ClassesOnCharges would have a three-column primary key -- subject_id, charge_id, class_id

Thanks for the great advice! BTW...I purchased your book and read it yesterday. I'm an msdba and found your book to be an excellent read. Great work! I would love to read a book written by you on style. Have you written one? Thanks again!

thanks for the kind words about the book


no, but i'd like to

you can be my agent for the usual fee if you land me a publishing deal

I have a friend who owns a small publishing company. We haven't had a chance to talk in a while but at one time, he had a print-on-demand machine that he used to print books. Take care...I'm sure you'll see me on here more now that I've found this forum.

Take Care

Scott W.

There are a number of issues yet unresolved in your second model. Rather than going through with them one by one, back an forth, it may be easier to suggest something and have you ask questions/discuss. This ERD implements all your identified rules (plus more); it is not complete enough to code from , but it is progressed somehwat beyond that above.

The main issues are: the process of Normalisation is missing; and the notion of events (as distinct to other events) is missing. Yes, the questionable relations in your ERD above need to be resolved, but they get resolved as an ordinary matter of course, if the normal process is followed.