Asset Management and Relationships

[stephen.belyea]

Hey everyone - I've been to this newsgroup before, and really
appreciate how helpful everyone is. Sorry to bother ya'll again!

My latest project for the town I work for is taking stock of all the
software we have on hand and creating a database to keep track of it,
as well as which PC we have software installed on. I've been trying to
put together a sensible database structure over the past few days -
here's what I have so far.

tblSoftwareManufacturer
SMid (autoid, pk)
SoftwareManufacturer (text)

tblSoftwareName
SNid (autoid, pk)
SMid (number, fk to tblSoftwareManufacturer)
SoftwareName (text)
SoftwareDescription (memo)

tblSoftwareVersion
SVid (autoid, pk)
SNid (number, fk to tblSoftwareName)

tblLicenseKey
LKid (autoid, pk)
SVid (number, fk to tblSoftware Version)
LicenseKey (text)
LicenseType (text)
NumSeats (number)
DateofPurchase (date)
Vendor (text)
InvoiceNum (text)
InvoiceDate (date)
PurchasePrice (currency)
MiscNotes (memo)

tblPC
PCid (autoid, pk)
LKid (number, fk to tblLicenseKey)
PC (text)
PrimaryUser (text)

My reasoning is this: We have a software manufacturer [Microsoft] who
creates software which is given a name [Office] which is given a
version [2000 Professional]. The town has purchased x number of
licenses for that version of software, and each license key is tied to
a computer with a primary user (each user in our office has a
dedicated computer). Some of the license keys we purchased enable us
to activate up to 5 users (for example, a Small Business Server
license allows you to activate 5 computers under the one license).

In my mind, the tables work like this:
tblSoftwareManufacturer 1:M tblSoftwareName 1:M tblSoftwareVersion 1:M
tblLicenseKey 1:M tblPC.

Does this seem like a feasible framework for a simple asset management
database? I'm still relatively new at access, and would greatly
appreciate any advice or help.

Thanks in advance!

 

[John W. Vinson]

My reasoning is this: We have a software manufacturer [Microsoft] who
creates software which is given a name [Office] which is given a
version [2000 Professional]. The town has purchased x number of
licenses for that version of software, and each license key is tied to
a computer with a primary user (each user in our office has a
dedicated computer). Some of the license keys we purchased enable us
to activate up to 5 users (for example, a Small Business Server
license allows you to activate 5 computers under the one license).

The problem with your current design is that your tblPC has only one LKID -
and you will surely have multiple programs installed on each PC! You have a
Many (PCs) to Many (licenses) relationship; you need one more table indicating
what software is installed on each machine.

John W. Vinson [MVP]

 

[Steve]

Nice job on the tables!

Some suggestions ---

Consider adding a table for users:
TblPCUser
PCUserID
PCUserFirstName
PCUserLatName

Consider changing TblPC to:
TblPC
PCID
PCMfgr
PCModel
PCSerialNum
PCUserID

Most likely a PC has more than one software package. Consider adding a
separate table for licenses:
TblPCLicense
PCLicenseID
PCID
LKID

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[stephen.belyea]

My reasoning is this: We have a software manufacturer [Microsoft] who
creates software which is given a name [Office] which is given a
version [2000 Professional]. The town has purchased x number of
licenses for that version of software, and each license key is tied to
a computer with a primary user (each user in our office has a
dedicated computer). Some of the license keys we purchased enable us
to activate up to 5 users (for example, a Small Business Server
license allows you to activate 5 computers under the one license).

The problem with your current design is that your tblPC has only one LKID -
and you will surely have multiple programs installed on each PC! You have a
Many (PCs) to Many (licenses) relationship; you need one more table indicating
what software is installed on each machine.

John W. Vinson [MVP]

In order to solve this, I guess I would have tblPC and tblLicenseKey
with relationships into a table which has the pk from both tblPC and
tblLicenseKey? I'm going to fiddle around with it today and see if I
can make it work - thanks for everyone's help so far