[Landanan]

Hi guys!
Hope someone can help me out here.
What I'm trying to do is to create a db for elementary school management system, and got stuck in designing a relationship.

Here is what I have:
table Students, which stores the basic information about the students (name, last name, date of birth, etc. Stud_ID as primary key)
another table is Relatives, which stores the info about students' moms, dads, uncles or any other guardians. ATM has Rel_ID as PK.

What I need:
Basically, a relationship between these tables
The information about relatives is only needed while a given student is present in school, and when he/she leaves the school, I no longer need to keep the info about guardians. So it's obviously a weak relationship with "on delete cascade" thingie.

Here is the part that doesn't let me sleep at night:
Two or more students may be brothers or sisters, hence they have common relatives. No point in entering the same info in the Relatives table several times, so it looks like I need a third table for many-to-many relationship (a given student may have several relatives, and each relative may have several "kids"). So let's call this third table Student_Relatives, which contains two fields: Stud_ID and Rel_ID, both as foreign keys.

But if I do it that way, I'll lose the "weak" part of the relationship, cuz even when I drop all records from Student_Relatives table, the records in Relatives tables will still be present, and I don't need them.

It makes sense to create a trigger for any changes in the Student_Relatives table, which drops all records from Relatives whose Rel_IDs are not present in Student_Relatives. But since I'm using a simple Access 2007 database (no connection to the SQL server), I can't create triggers.

I can also make a query that looks something like this:

DELETE FROM Relatives R WHERE R.Rel_ID NOT IN (SELECT SR.Rel_ID FROM Student_Relatives SR)

and run it every now and then. But this approach looks really messy
Especially if the database is going to be used by some secretary chick who can only click buttons.

Any help would be greatly appreciated. Maybe I need to change the whole design of the tables, so I can make this kind of "weak many-to-many relationship", or it is possible to achieve triggering in Access 2007 through the use of macros, or anything else...
Share your thoughts, fellas

[blindman]

First, I would question why you are deletings student records rather than just flagging them as "Graduated" or "Dropout", or whatever.

But if you have to delete the students, then in the absence of triggers your best bet is the nightly process to remove relative entries no longer referenced by a student.
Messy? Maybe a little, but I've seen MUCH worse.