Software and Hardware Inventory Database

[BC2015]

First-time setting up a complex (or what I think is complex) database, and really could use some help. The idea is to be able to create an inventory of Computers, Peripherals, Software, and Software Licenses, (and where each is installed/located), along with the Purchase Order information for each item, and the Vendor for each P.O.

Here's how I sort of see it breaking down, but I'm confused as to how to setup the tables and relationships:

 

One Computer can have many Purchase Orders associated with it.

One Computer can have many Vendors/Manufacturers/Sales Reps associated with it.

One Computer can have many pieces of Software installed on it

One Computer may have many Peripherals

One Computer can have many Change Log entries associated with it.

 

One piece of Software (software title) can be installed on many Computers

One piece of Software can have many Licenses associated with it

One piece of Software can have many Purchase Orders associated with it.

One piece of Software can have many Vendors/Manufacturers/Sales Reps associated with it.

One piece of Software can have many Change Log entries associated with it.

 

One License can be installed on only one Computer at a time

One License can have only one piece of Software associated with it.

One License will have only one Purchase Order associated with it.

One License can only have one Vendor/Manufacturer/Sales Rep associated with it.

One License can have many Change Log entries associated with it.

 

One Peripheral can only be installed on one Computer at a time

One Peripheral can only have one Purchase Order associated with it.

One Peripheral can only have one Vendor/Manufacturer/Sales Rep associated with it.

One Peripheral can have many Change Log entries associated with it.

 

One Purchase Order can have many Licenses, Peripherals, and/or Computers associated with it.

One Purchase Order can have only one Vendor/Manufacturer/Sales Rep associated with it.

One Purchase Order can have many Change Log entries associated with it.

 

One Vendor/Manufacturer/Sales Rep can have many Purchase Orders associated with them.

One Vendor/Manufacturer/Sales Rep can have many Computers associated with them.

One Vendor/Manufacturer/Sales Rep can have many Peripherals associated with them.

One Vendor/Manufacturer/Sales Rep can have many pieces of Software associated with them.

One Vendor/Manufacturer/Sales Rep can have many Licenses associated with them.

One Vendor/Manufacturer/Sales Rep can have many Change Log entries associated with them.

 

Change log is: A manually-created list of notes for any changes, upgrades, problems, maintenance events, etc. performed for the entire department. Needs to be filterable/sortable by Date, Computer, Peripheral, Software, License, P.O., Vendor, and/or type of event. Event types would be from a manually-created value list.

Would be great if I could have a layout specifically for entering these events. Also, not sure how I would go about entering events if one event affects multiple computers or peripherals at the same time. I might have to just make duplicate entries (one for each piece of equipment) to link an event to each piece of equipment it affects?

 

I've begun setting up tables for:

- Computers

- Software

- Licenses

- Peripherals

- Installations (join table joining everything?)

- Vendors/Manufacturers/Sales Reps

- Purchase Orders

- Change Log (not sure what fields I need in this) 

 

I'm also trying to figure out the Primary Keys and Foreign Keys, how the relationships should work, and how I can create a layout for let's say Computers that will show me:

- Computer info (computer name, location, hardware specs from the Computers table)

- P.O. number for Computer (from the P.O. table)

- List of installed licenses (including software name/version, license number, vendor, and P.O. associated with that license) (portal to the Licenses table, filtered by ComputerID?)

- LIst of installed peripherals (including the type of peripheral, manufacturer, name/serial number, vendor/P.O., date of install) (portal to the Peripherals table, filtered by ComputerID?)

- Change Log list for that computer, sortable by date and/or type of change. (portal to the Change Log table, filtered by ComputerID?)

 

And a layout for Software that will show me:

- Software name/Version

- Type of Software (standalone software, software plugin, etc.)

- List of Licenses associated with this Software (including software name/version, license number, vendor, and P.O. associated with that license) (portal to the Licenses table, filtered by SoftwareID?)

 

And a layout for Vendors/Manufacturers/Sales Reps that will show me:

- Vendor Contact info (sales rep name, phone, email, website, etc.)

- List of P.O.s associated with this Vendor, sortable by date, and expandable to see the individual items within that P.O. (such as license numbers and where those licenses are installed, or peripherals and where those peripherals are installed)

 

If anyone has any advice, I sure would love the help. Thanks.

[comment]

This may a bit too much to take on at once.

Perhaps you should consider a central table of Assets (computers, peripherals, licenses - in short, anything you purchase), then use either sub-tables or self-joins (either directly or through join tables) to link them up. That would eliminate the problem of having to link PurchaseOrders and ChangeLog separately to each type of asset.

 

 

Edited April 20, 2016 by comment

[BC2015]

Thanks for the reply!

The self-join looks like a good idea.

So, one massive table with a ton of fields for the Assets table, and one separate table for the ChangeLog, related to the Assets table by the AssetID that the record pertains to?
 

[comment]

If I follow this correctly, you would have these tables:

• Assets;
• PurchaseOrders;
• Software;
• Installations (a join table between Assets (as Computers), Assets (as Licenses) and Software);
• ChangeLog;
• ChangeLogItems (a join table between ChangeLog and Assets)*

(*) This assumes a change log entry can affect more than one asset item.

I am not sure you need "a ton of fields" for the Assets table.

 

[BC2015]

Oh, ok. I was misunderstanding. I thought you were saying I needed to flatten the entire database into one table for some reason. Disregard. What you're saying now makes a lot more sense!

Yes, the ChangeLog entry could definitely affect more than one asset. Which table would ChangeLog records go into? ChangeLogItems or ChangeLog?

I'll play around right now and update with a screenshot of what I end up with.

 

Edited April 20, 2016 by BC2015

[comment]

20 minutes ago, BC2015 said:

Which table would ChangeLog records go into? ChangeLogItems or ChangeLog?

Well, it depends on what exactly a "change log" is. In general, the ChangeLog table would record the details of a specific change, such as date and description. Then the ChangeLogItems table would have a related record for every asset affected by the change. So this table would have ChangeLogID, AssetID - and any fields that apply to a specific asset/change combination (e.g. the ID of the tech that implemented this change to this asset).

[BC2015]

That makes sense. Thanks so much for your help. I'm going to work on this today.

[BC2015]

How's this? 

[comment]

I would have expected something more like this:

 

[BC2015]

So... Like this?

 

 

Edited April 21, 2016 by BC2015
swapped image for correct image

[comment]

That could work - but you have no way to associate a peripheral or a license to a purchase order or to a change log. This is why I suggested using occurrences of the master Assets table instead of dedicated tables for these sub-types of assets.

 

 

Edited April 21, 2016 by comment

[BC2015]

Oh! Now I get it!

So, something like this?

 

[comment]

Close. I think you only need one primary key field in the Assets table, and use it for all the relationships. So:

Assets::AssetID = Installations::ComputerID

and:

Installations::LicenseID = AssetsLicenses::AssetID

Similarly:

Assets::AssetID = AssetsPeripherals::ComputerID

---

There is another way you could look at this, which might be simpler:

In this model, you have a table of Locations (or Systems, if you will), to which you assign any purchased assets alike - be they a computer, a peripheral or a license. IOW, instead of a computer being a parent of a printer, they are both sibling children of a system (that doesn't exist except as a concept).

 

 

 

 

[BC2015]

Wow, that second one looks a lot simpler, and might be more what I need, actually. I'll try that out.

 

What would be the advantages of one way over another though?

Attached is a mockup of this.

Edited April 21, 2016 by BC2015

[comment]

1 hour ago, BC2015 said:

What would be the advantages of one way over another though?

Well. one advantage you saw for yourself: it is simpler.

The other thing is, it allows you to replace a computer while leaving the existing printer firmly attached to the system. The way you described it in your original post, that would take some doing.

OTOH, if you wanted your change logs to be related (directly) to a system as well, that would be more difficult.

 

1 hour ago, BC2015 said:

Attached is a mockup of this.

You have a table of SoftwareLicenses, which you should not have - since they are Assets. OTOH, you are missing a table of Software (which IIUC is no more than a gloriified value list).

 

 

Edited April 21, 2016 by comment

[BC2015]

Great points. This does look like it would work better.

This new relationships graph would allow me to assign a new computer to a room without affecting any of the other assets in that room, whereas the previous relationships would not have allowed that?

I'm not too concerned with having the change log records related to the rooms. It's more important that they be related to the assets, which happen to be located in whatever room they're located in. 

Here's another mockup...

 

 

[comment]

16 hours ago, BC2015 said:

This new relationships graph would allow me to assign a new computer to a room without affecting any of the other assets in that room, whereas the previous relationships would not have allowed that?

Correct. In the previous arrangement you would have to reassign all peripherals in that room to the new computer.

Edited May 3, 2016 by comment

[BC2015]

Makes sense. I think I'm starting to get the hang of it. Sort of.

 

Now I've got to make layouts that show the information that I want to show. Bah!

[BC2015]

Ok, I apologize, but I'm lost again.

How do I make layouts that are actually useful for looking up information?

These should be simple things to do, but I don't understand how to use the layouts to do this. I get how to physically manipulate the design of the layouts and field boxes etc., but I don't get which table should be the base of which layout for which type of information, and where the fields should be pulled from for each field displayed.

For instance, how would I create a layout that would output:

• A list of computers (and the rooms they're in)
  • For each computer, a list of each of the software titles/versions that are installed
    • For each piece of software, the license code and P.O. for that license code

 

Or a layout that would output:

 

• A list of software titles/versions
  • For each piece of software:
    • A list of the computers it's installed on
      • The license code for each installation
        • The P.O. for each license code

 

Or how can I create a layout for each room that will tell me what hardware assets are located in that room?

 

Your help is most appreciated. Thanks.

 

[comment]

Well, it depends on the purpose.

For example, you could use a layout of the Locations table, with a portal to Assets to show all the assets in the currently viewed location. If you like, you cold use several such portals, each filtered to show only some type of assets. This would be suitable for on-screen browsing.

Similarly, a portal to Assets placed on a layout of SoftwareTitles would show all installations of the currently viewed title. And if you add a field from Locations to this portal, you will also see which system it's installed on (though not the individual computer). And a field from the PurchaseOrders table in the same portal can show you the corresponding purchase order number.

In addition, you could produce a report of all (or only some) assets, using a layout of the Assets table. If you summarize such report by Location, you will see a list of all (or some) locations and their assets. If you perform a find for software licenses only and summarize them by title, you will get the report equivalent of the second example above. Again, you can place fields from related tables on the layout to show data from the corresponding parent records.

Those are just examples - the possibilities are endless. Also keep in mind that you can easily drill into more detail or zoom out by utilizing buttons that switch from List view to Form view, or do Go To Related Record[].

 

[Dimitrios Fkiaras]

Well, I am currently working to accomplish something similar,

so although I am quite novice I would like to express my thoughts.

 

I think that its very good you have a clear view of what info you wish to see.

Also I think that it is important to imagine how you would like to see all these data and with what interaction by the user.

 

For example :  Maybe you see all computers in a portal with 3 columns [Name, Hardware Serial, Building]

then you click on a computer and then you sees another portal which has the columns [SoftwareTitle, Lisence, Verion, PO, Date]

 

Its important to have an idea about the user interface and data presentation, because sometimes

you have to create new Table Occurences in the Graph  just to create the appropriate "paths" between data

to present them in the way you imagine (this is vital if you plan to use portals).

 

I don't know if I am correct but I think that every time you wish to display in the same layout lists of data that are related

you can't avoid portals.

 

Another trick I learned lately is this :  

If you want to present a portal with all the computers you can't base the layout on any of the related tables because it will show only

related records to (P.O, Locations etc). 

So you can created a table with just one record (lets call it VIEWER TABLE) and relate that table primary key to a global field in the ASSETS table.

Thus all ASSETS will be related to the one and only record in VIEWER TABLE. So when you make a layout based on VIEWER table and

a portal based on ASSETS you will see all ASSETS. By filtering the portal by the Asset Type you can get is to show only the Computers

 

Glad if I helped

Dimitris

Edited May 9, 2016 by Dimitrios Fkiaras

[BC2015]

Thanks everyone for your help. I think I've figured out how to make portals work, and that means that I have my tables talking to each other correctly, which is great!

Now comes the hard part of actually setting up layouts that do what I want them to do. 

 

Using Portals, I've now got a P.O. layout that tells me what assets are associated with any particular P.O. record (along with those assets' information like location and license code).

I've also got a Locations layout that now tells me what assets are located in any particular Location record.

This is a GREAT start!

 

I've also determined that I don't need a "Software" table because, as was mentioned previously, it would merely be a glorified Value List. I'll just create a Value List for Software and it should work fine that way (I'll create a dropdown list to select the software title/version, and be sure to maintain it separately)

 

Anyway, thanks again for all your help!

 

 

 

Edited May 12, 2016 by BC2015