My 1st DB (Software License Tracking) table structure

[Jeremy Gollehon]

OK, this is my first database and I've posted the table structure to get
your advice. I've done some reading, but I'm still not feeling that
confident.

We've grown from a company of 3 to 8 and tracking software licenses is
getting tougher. I want to get it under control before it gets out of hand.

As this is my first post to an Access group, please bear with the mess.
It's hard to relay the table structure and relationships easily. Any tips?

Here's what I'm working from:
Person can have many Computers.
Computer can only have one Type.
Computer can have many Applications.
Application can have many Licenses.
*Computer/Application can only have one License*
Application can have only one Publisher.
Publisher can have many Applications

The problem (at least the one that I see) is that I'm not sure this
structure represents the fact that a Computer can have many Applications,
but once an Application is associated with a Computer, that
Computer/Application can have only one License association.

I'll keep working on it and testing... Eagerly awaiting your advice.

Thanks!
-Jeremy

----------------------------------------------------------------------
tblPeople
pkPeopleID <-->> fkPeopleID on tblPeople
FirstName
LastName

tblComputers
pkComputerID <-->> pkComputerID on tbleCompApp
ComputerName
fkPeopleID <<--> pkPeopleID on tblPeople
fkComputerType <<--> pkComputerType on tblComputerType

tblComputerType
pkComputerTypeID <-->> fkCompterTypeID on tblComputers
ComputerType

tblCompApp (composite primary key)
pkComputerID <<--> pkComputerID on tblComputers
pkAppID <<--> pkAppID on tblApplicationName

tblApplicationName
pkAppID <-->> pkAppID on tblCompApp
AppName
fkLicenseID <-->> pkLicenseID on tblApplicationLicense
fkPublisherID <-->> pkPublisherID on tblPublishers

tblApplicationLicense
pkLicenseID <-->> fkLicense on tblApplicationName
License

tblPublishers
pkPublisherID <-->> fkPublisherID on tblApplicationName
PublisherName
----------------------------------------------------------------------

 

[RSGinCA]

Question: Can an application have more than one licence? I.e., can two
computers running the same application have different licences?

Rick

 

[Jeremy Gollehon]

Rick, thanks for the reply.

To answer your question, yes, two computers running the same application can
have different licenses.
In fact, two computers running the same application *have* to have different
licenses for that application.
This is the part I'm not sure my table structure inforces.

Again, thanks for the reply.

-Jeremy

 

[RSGinCA]

Your design doesn't actually connect a specific licence to an application on a
specific computer. As it is, an application on a single computer is being
connected to a pool of licences, and the licence for the application on that
computer can't be identified.

If you need to identify the specific licence for an application on a specific
computer then the fkLicenceID needs to be on the tblCompApp record. That
record (tblCompApp) represents one specific occurence of an application on one
specific computer... and it needs to have one specific licence associated with
it.

Rick

 

[John Vinson]

OK, this is my first database and I've posted the table structure to get
your advice. I've done some reading, but I'm still not feeling that
confident.

We've grown from a company of 3 to 8 and tracking software licenses is
getting tougher. I want to get it under control before it gets out of hand.

As this is my first post to an Access group, please bear with the mess.
It's hard to relay the table structure and relationships easily. Any tips?

Here's what I'm working from:
Person can have many Computers.
Computer can only have one Type.
Computer can have many Applications.
Application can have many Licenses.
*Computer/Application can only have one License*
Application can have only one Publisher.
Publisher can have many Applications

The problem (at least the one that I see) is that I'm not sure this
structure represents the fact that a Computer can have many Applications,
but once an Application is associated with a Computer, that
Computer/Application can have only one License association.

The relationship between Computer and Application is a many to many,
not a one to many: many computers will have MS Access, and each
computer will have many Applications. The license pertains - not to a
computer, or an application, but to an Installation; you need a new
table for this entity. It would be linked many-to-one to Computers and
to Applications, and the License would be a field in the Installations
table.