I've been working on this more on and off between many other things, so sorry for the delay. Thank you all for the great advice. I made some changes and included recommendation about user and equipment statuses.

Summary of database goals:

I want the inventory database to have a trail from the moment we order a piece of equipment to the moment it leaves our possession. For example the order number and date, along with digital copy of the PO and shipping invoice; as well as who the equipment was assigned to along the way. I also want to keep keep track replacement parts (RMA’s) on any equipment in the same manner. This will show repair history on equipment. Or if whole equipment was RMA’d not just parts.

One of the most important goals, is to never delete equipment records; rather simply hide the equipment. So when equipment is retired or scrapped, I would like it to have the date it was scrapped and which company/organization picked up the equipment. I was originally thinking of moving those records to a “scrapped” table, but I think simply having a status of scrapped on the equipment record will suffice? That as well as audit tables. This is one of things I find missing from every inventory database for IT. When the equipment is scrapped or replaced due to repairs, it is just deleted and there is no trail or record afterwards (it might get tracked in a Excel document somewhere). When it is time for an annual physical audit you end up trying to remember if that serial you can’t find was scrapped or RMA’d and replaced. This will still occur with a good database, but hopefully a lot less.

One of the more ambitious goals (since this is my first database) is to have audit tables for each table so I can have a history of who made the change and when. For right now I just want to focus on getting the core database working.

Ideally this type of database would probably be linked to a HR database and maybe something like Active Directory to pull employee info; instead of recreating that info in a separate table/database. However, I do not have access to HR data would prefer to keep it that way 😉 Therefore, some of the tables such as the Users table are not as comprehensive as they could be. That it is ok with me. My primary goal is to have enough information about the user who entered equipment into the system, who was assigned what equipment and when, who removed equipment and when to provide a good audit trail. Same goes with attributes in the equipment table - I know there are more fields that can be added, but I just need enough unique data to provide good clues to track down equipment (i.e. MAC, static IP’s, Host name, serial, model, asset tag, etc.).

Here is a link to the latest ERD which is also attached.

I'm hoping I have enough of a solid design to start actually building this database. I'm sure it will change some more as I start doing so.

😀