how to allow orphan recordsets in many-to-many bridge tables

Question

hello, I have a problem that I can't seem to solve. I have table: Software and table: PC

they both have many to many relationship between each other, that is, one PC can have many Software and One Software can have many PC's the link table is: soft-pc

The table soft-pc also has licensing information like, product keys.

now the problem is, when a software is deleted from a PC the record is deleted from the soft-pc table. Now I want to be able to un-associate software from PC and still have them in soft-pc table.

Is that possible?

Answer 1

You will need to remove cascading deletes from the relationship, after that, two possibilities occur to me:

1. Add a deleted date to soft PC
2. Move the PC ID to another column - this will only work if the unique key is not PCID + Soft ID.

I would be inclined to go with #1.

EDIT re additional post

If you wish, you can have a junction table with all possible PC-Soft combinations and a code to indicate whether or not the software is installed, uninstalled, never-to be-installed etc, and a date at which the status occurred. There is a lot to be said for this approach.

Answer 2

Now I want to be able to un-associate software from PC and still have them in soft-pc table.

This is the core of the problem. At the moment, the relation predicate for the soft-pc table - that is, what it means for there to be a particular record in this table - is that the software in question is on the PC in question. If you now want to say that it's possible for there to be a record in this table when there isn't that software on that PC, you are going to have to decide what the new relation predicate is. Which is to say:

What does it mean for there to be a record in the soft-pc table?