Jump to content
Sign in to follow this  
panache

One to Many (nOOb)

Recommended Posts

Ok, folks, this is a real newbie question, but I need to ask it.

I'm working on a solution that will help the tech support guy (me) track our software licenses, where they're installed, etc. I want to keep track of what software I have on hand, how many seats have been ordered for each, how many machines have a particular app installed, etc.

I created one db called "software." It stores info like app name, manufacturer, license, # seats total, etc. A record is a single instance of an app (photoshop or office or acrobat, etc). I also created a db "employees" that lists employees, and what apps they have installed. A single record is an employee (Joe, Jane, etc).

Let's say, for instance, that I have a 10 seat license for Photoshop CS. There's one record for this app in "software," and the #seats field says "10." So one app (photoshop) can be installed on many computers (employees). So that would lead me to believe that every occurrence of a seat being used would be a record in. . . . employees?:

record one: John Smith, seat 1 of Photoshop CS

record two: Jane Doe, seat 2 of Photoshop CS

etc.

I'd also like to do some sort of counter that could warn me, "all seats for this app have been used" and prevent me from "giving" it to another employee.

Sorry if these questions are super simple. Everyone has to start somewhere, right?

Share this post


Link to post
Share on other sites

Panache,

If you use each seat of an application as a new record in "Employee", you will wind up with employees being listed multiple times. (i.e. once for photoshop, seat 1, once for Dreamweaver, seat 6, etc).

Here's a question: Do you need to know which users are registered for which "Seat" of an application, or just how many are currently registered? If it is the latter, I suggest the following:

In the "Software" db, find a unique identifier for each application (license# will work, but may be longer than you need). Keep that number in a "UniqueID" field. In the "Employees" db, create a field called "ApplicationIDs". Use this field to store the UniqueID numbers for all applications associated with that employee. Be sure to separate each number with a par.gif sign.

Next create a relationship between Software and Employees where "UniqueID" matches to "ApplicationIDs". Create a portal (in Software) that shows related records in "Employees" based on that relationship. This means that when you pull up an "Application" record, you can see all the employee records that have the unique ID number in the "ApplicationIDs" field.

As for the warning about "all seats have been used", I'm at home and can't be sure, but I'm guessing that it will require a "MaxSeats" field in the Application database, compared to a global Status(CurrentFoundCount) unstored calculation in the "Employees" database. I'll try to provide a more solid solution tomorrow, when I'm back at my computer that has FM on it, if nobody else has a definitive answer. smile.gif

Paul

Share this post


Link to post
Share on other sites

Paul:

Thanks for the reply. To answer your question, I would like to know which specific employee has what seat of a certain app installed. If an employee leaves or switches departments, than that seat (or install) of that app is freed up.

I've renamed "employees" to "seats" to keep things straight for myself, as I realized I'm counting installs, not employees. I did use a serial number field in both db's, and use that as the key between the two. I made a portal in "Software" that displays the names of all employees that have a certain app installed (e.g., the "photoshop cs" record in "software" displays "john smith, jane doe," etc.).

You're right in that it's now possible for an employee to appear in "seats" more than once. Is that going to be a problem (again, forgive my ignorance). I figured that in "seats" I can generate a report with an "employee name" subsummary that lists all apps installed on a given employees machine (e.g., John Smith has photoshop, dreamweaver and acrobat) and in "software" a report sorted by app that lists all employees (e.g., dreamweaver is installed to jane doe, john smith and sally jones).

Thanks again for your input. I've only been using FM for a short time. I appreciate it.

Share this post


Link to post
Share on other sites

If you change "Employee" to "Seats", you are going to be storing employee data multiple times, which will be aggravating when an employee leaves (or gets married or divorced, or joins a new cult) because you'll have to update each record in the "Seats" database individually.

It might be cleaner to create a NEW "Seats" db (I'd wait for corroboration from someone with more posts...). Use three fields: AppSerial#, Seat#, and EmployeeID#. Your relationships could then be

ApplicationID::AppSerial# from the "Application" db and

EmployeeID::EmployeeID from the "Employee" db.

To show who has all the seats for a particular license, you'd look in "Seats" for matching AppSerial#. From there you could get the EmployeeID for all users.

To show which apps an employee has, you can create a portal in "Employees" showing all matching records from "seats" that have similar EmployeeIDs.

Either way, the subsummary report should still be possible. Still, unless you have "spare time" to just play with n idea, I'd get more elegant ideas from someone with more posts. While my solutions frequently work, they are often not the most refined. wink.gif

Paul

Go to bed, it's 2a.m. EST

Share this post


Link to post
Share on other sites

I have a similar system for tracking licenses, but in my system I assign licenses to computers instead of employees. To me this makes more sense; if an employee leaves and someone else takes their place, I don't have to do anything. Also, some computers have multiple users. The only time I have to change things is when I'm installing new software or replacing a computer.

Below is an ER Diagram showing how this could be done.

license.GIF

Share this post


Link to post
Share on other sites

Ender,

I was hoping you'd have an ER diagram for this. Who is on which computer (if you need to know) could then be tracked by adding a "ComputerSerial#" field to Employees. Thanks! smile.gif

Paul

Share this post


Link to post
Share on other sites

One thing to note: Some software licenses will have one license code for each computer, others will have one license code for a volume license, and a few don't have license codes at all. It's best to take this into account and not rely on the license code as a key field.

Share this post


Link to post
Share on other sites

Everyone:

Thanks for the great suggestions.

Ender: Please explain to me the "License-Agreement" DB in your ER Diagram. I have created the following DB's:

"Computers", with the fields you suggested

"Employees"

"Software"

compSerial# is the key field between them all. Now am I to use the License Assignment DB as a sort of "front end" DB that pulls info from the three others, where 1 record = 1 assignment? Again, forgive my ignorance and I thank you for your patience. I hope this makes sense.

Share this post


Link to post
Share on other sites

License_Assignment is a back-end table. It wouldn't need many fields, just compSerial#, LicenseID and some lookup fields to show which license and which computer its referring to. You can use portals into License_Assignment from the License table or the Computer table to show and enter License Assignments.

1 record in License_Assignment is one assignment of a software license to a particular computer.

Hope that helps.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.