I've been asked to improve the way we track our software. I've developed a solution that works pretty well but has a few holes and one of them is very large as I see it. I hope someone here might be able to help open my eyes to a new way of viewing the problem.

The trouble comes from some software needing to be assigned to a person/user and other to a device. Another hurdle is licensing. Originally when most software was a single license it was easy but now when buying a 200+ license for the same title with the same install code/serial number it gets more tricky.

In my solution I have Users, Devices, and Software. Each software record is currently designed to relate to a user. But now I need it to relate one or many Users or a Device.

I keep thinking some sort of join table is going to be needed but that’s where my brain stops working. I'm really struggling to grasp this and it for no good reason.

Any suggestions are greatly appreciated.

I've been thinking about this too. I came to the conclusion that I need to track a couple of different things.

A software install is the intersection of a particular software title and a particular piece of hardware. That is a many-to-many relationship and as such requires a join table.

The software license is a separate "thing." It is differentiated from all the other existing licenses for the same title by the purchase order number. So if a business unit purchases 20 seats for Photoshop under one PO, I need to have one record that indicates that. I expect I will need a summary field that counts up all the Photoshop seats that belong to a particular business unit, and compare that to the total number of Photoshop installs in that business unit.

My logical model doesn't care about which person or user is actually using the software. We have such high turnover here that I would go nuts trying to track that. But I can follow the relationship from title to install to hardware to user, if I need to build a pretty report with user names for a business manager.

Hope that helps.