General Database development & design help!

[rsmonkey]

Hi,

Right at the moment i'm baisically designing and building a software licensing management system fro my boss at the moment but I have had a snag, the face that its 3pm on a friday is not helping with my concentration either.

Ok so my problem is that i've got 3 tables (tblUser_Informantion, tblSoftware_Information & tblHardware_Information) all of which are related via a 4th table (tblReference) which holds the primary keys of each of the other 3 tables mentioned.
For all intents and purpose's the primary keys are named according to their tables i.e. (User_ID, Software_ID, Hardware_ID, Reference_ID etc...)

On my main form a User can select via a cmbo-box or search for a Person via Surname or search via Machine Asset number. Upon selection this fills a number of pre determinate box's below that containing you typical standard information. However under this is a subform showing what software the user has installed on the machine and on an alternate tab it shows the software license's purchased and assigned to that machine. So far so good.

However I want to create a button in which the user can add software for that particular person in the fields. The user will select the software from a listbox filtered by combo boxes filtering by Software Vendor etc... My problem is that when the user has selected this software title either via double click or an update button i want it to update the tblReference table. So baisically i need this button to grab the User_ID, Software_ID and Hardware_ID and place it in the tblReference table as a new record.

Hopefully i have explained this well enough for people to understand below is a jpg of my current main screen so you can hopefully get a jist of what im trying to say and do. However im open to suggestions if people feel they know a much better way of achieving my goal.. as i sed its friday afternoon and im feeling particulary slow so you'll have to forgive me!

Any help is much appreciated.

 

[KeithG]

So you want to add a record to the subform?

 

[rsmonkey]

No i want to add a record to the tblReference table. baisically i want to pull the primary keys of all 3 tables i.e. User_ID, Software_ID and Hardware_ID and place them into the tblReference table according to their respective information. So the selected hardware & user from the main form id's will be placed into the tblReference as well as the selected software's id through the use of a single button! .........

 

[pono1]

May I offer another point of view on the design side of things that may or may not apply to your situation, depending on your requirements?

One person can use many computers.
One computer can be used by many people.

-> TblPerson
-> TblComputer
-> Tbl_Person_Computer (junction table)

I can't tell for certain if your application reflects this relationship. Yet it looks like your program handles the following...

One computer can have many software programs.
One software program can be installed on many computers.

-> TblComputer (the same table as above)
-> TblSoftwareProgram
-> Tbl_Computer_SoftwareProgram (another junction table)

A software license is abstract and it may not match up to what's actually installed on PCs. Software licenses live in their own little -- sometimes make-believe -- world. To reflect this, consider creating another table.

-> TblSoftwareLicense

When it comes to organizations checking compliance, licenses are typically compared in bulk against the group's real world installations. That is, your company might be licensed to use 30 copies of Word but there are, in fact, 40 machines running copies today. Tomorrow, after a visit from a technician who uninstalls a bunch of copies, there might be 25 machines running Word.

Given the schema above, to compare the licenses your organization holds to the actual software installed, you might create a simple report-subreport. Roughly...

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Software Licenses (drawn from your SoftwareLicense table)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
30 Adobe XYZ
10 Macromedia 123
60 Word 2000

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Actual Software Installations
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
10 Adobe XYZ
20 Macromedia 123
72 Word 2000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Regards,
Tim

 

[rsmonkey]

Hi Tim
thanks for your reply, i have reviewed and taken into consideration what you've said, firstly each piece of hardware is assigned to a specific user or simply assigned as a hotdesk if its a public-company portal machine. As you suggested I already have a tblSoftwareLicensing table due to the exact difficiencies you mentioned aboe (software naming convention s etc...) thus instead of having a Tbl_Person_Computer & Tbl_Computer_SoftwareProgram as 2 junction tables i have combined these into 1 junction table i.e. tblReference. Thus becomes my problem when a user wants to add a specific piece of software to a specific machine(hardware) i want it to update this tblReference with the user's, software's and hardware's id's.. so i need to know how to do this in conjunction with the form's design!

any help would be much appreciated.

cheers