When x licenses are associated with y discs (join table?)

November 9, 200817 yr

Situation: Orders are placed for software licenses and software discs, and from those orders inventory records are generated. There might be 30 licenses and 2 corresponding (pieces of media, like CD-ROM) discs placed as an order. Each disc has a physical location (room) it is kept in. When someone wants to install an application from disc, she finds a license record (e.g., "FileMaker Pro 9"), and sees where a corresponding disk is located.

A license doesn't technically own a disc, nor vice versa, but they have an association, i.e., a FMPro9 disc is associated with all the FMPro9 licenses, and vice versa, but is not associated with a "FileMaker Pro 8.5" license. I've scratched out the narrative, and then shown a proposed join table. The fact that I need to link one record in one table with another in the other is where I'm drawing a blank. Thanks for any suggestions. Ken

An order has 0, 1, or many licenses

A order has 0, 1, or many discs

A license has 0, 1, or many discs

A disc has (is used by) 0, 1, or many licenses

order -++---o< license

order -++---o< disc

license -++---o< license -- disc (join)

disc -++---o< license -- disc (join)

-------

Here's another spin, but I'm not sure this is correct either (possibly a circular design?)

order -++---o< license

order -++---o< disc

softwareVersion -++---o< license

softwareVersion -++---o< disc

... where softwareVersion would have a record such as "FileMaker Pro" "9", and then all the children records (any licenses and/or discs of that version) would appear.

OK, a third idea. What if we make the disc table have a field which is quantity, enter the quantity of the discs there, and then just have each license record

license -+o---o+-- disc

A one-to-one relationship. I realize that whenever additional discs are acquired (by purchase, or more likely, through shrink wrap purchase), the quantity of the single disc record representing a software app+version would probably have to be incremented manually, but this might be the most straight-forward design.