Trouble with one-to-many and many-to-many relationships

[Hewitt Mac]

I've created a database (example attached) with a primary key (Contact ID) and several foreign keys (Adobe ID, Quark ID, etc.). In all but the Hardware table (one user with several computers), the relationships are one to many (More than one user might be using the same license - site licenses for example).

I've created portals in each of the layouts, but I'm trying to figure out if there is a way to bring up a particular Contact and assign the foreign key to them. Each table also has Contact ID as a foreign key - and the only way I can think to make this work is by creating a relationship between the primary Contact ID and the foreign Contact ID. If I do that, however, I think I destroy the many-to-one relationships already in existence.

I know this is confusing, but any help would be greatly appreciated.

Mac_Inventory.zip

[John Mark Osborne]

You don't seem to have provided the account and password to access your file.

[Hewitt Mac]

I've enabled 'Guest' login. Let me know if you still have trouble.

[Kent Searight]

I think you should reconsider your approach. Just having a single table for all of your licenses will simplify things greatly. You don't need (nor should you have) a separate table for each software in your solution.

[T-Square]

Kent is right. Rethink your structure. Look at the structure I added in to your file--

Hardware

Licenses

Assignments

Each License record identifies the software and its attributes (Description, Serial Number, etc.). Each Assignment joins a license with a piece of hardware. This structure allows many-to-many links, which is what you are describing. Add whatever fields you need, but keep in mind which object that field relates to.

I included a LicenseNotes table to demonstrate how you might allow users to enter an unlimited number of notes to a license. This is more efficient in the long run than, say, a single text field to hold the same type of info.

Using this structure, you can eliminate all the separate Application-specific tables, as well as all the fields in the Contacts ... table that deal with applications.

I didn't build the layouts, but you should be able to, based on the structure. One big challenge (I imagine) will be how to manage the number of assignments that your system allows to be made for a given license. I would look at storing a max count in Licenses, and testing the count of Assignments linked to the current license against that.

HTH,

David

Mac_Inventory_ExampleNew_Struct.fp7.zip

[Ender]

Hey Guys,

I totally agree that the structure should be normalized. My suggestion is similar to David's, but I'd add a Software Package table as a parent to the License. This can then be used to consolidate some of the information that's common to all licenses of a particular program. See attached.

Hewitt, one of the reasons this structure is better is that most software licenses are going to have the same kinds of things tracked about them. This means they really should be kept in the same table, and this in turn makes it a whole lot easier to assign licenses to a Computer and a whole lot easier to generate a combined report showing all licenses and where they are.