Welcome to a sharing of an ERD of an asset management system. So you have to take on an asset management system as a project, or got this as a school assignment? Here’s a sharing of my own simple version – Read on!
TABLE OF CONTENTS
First, here is the entire ER diagram and a quick overview of the system.
ASSETS MANAGEMENT SYSTEM ERD
To keep things simple, I have grouped this system into 3 sections.
- Assets – Don’t think this needs any explanation.
- Users & Suppliers – Staff and suppliers who sold the assets.
- Assignment – Which asset is assigned to which user.
FOR REFERENCE ONLY
Before the master trolls go “this is stupid”, “this is inaccurate”, and “this is useless”. By all means, this is only a simple example for reference.
- If you want to include the departments in the company – Go ahead and add them yourself.
- An asset must have at least one history entry – When it is procured, and from which supplier. If that is not the case, change the diagram by yourself.
- If you also want to track “digital assets”, feel free to add “asset type”.
Yes, I am trying to “one size fits all” here. So if anything doesn’t make sense in your own project, go ahead and change it.
Now that we are done with the overview, let us get into more details on the entities or tables.
PART 1) USERS
|user_level||VARCHAR||Up to you to decide – “A”dmin, “M”anager, “S”taff, “U”ser, etc…|
|user_name||VARCHAR||The user’s full name.|
|user_email||VARCHAR||The user’s email. Set this to unique to prevent duplicates.|
|user_password||VARCHAR||The user’s password.|
Some people like to create multiple “user tables”. One for “admin”, one for “managers”, another for “staff”, etc… Now, this will only make sense if all of them have different datasets. Otherwise, it is better to just stick with a single “user level” field.
P.S. This is NOT a staff management system, I will not add things like “department”. Unless it really makes sense to include those, I will keep users as simple as possible.
PART 2) ASSETS
|asset_id||VARCHAR||Primary key. Take note, this is not INTEGER. Feel free to come up with your own “asset code”.|
|asset_name||VARCHAR||Name of the asset.|
|asset_status||VARCHAR||Up to you to decide. Maybe “storage”, “assigned”, “repair”, “discarded”.|
|asset_details||VARCHAR||Optional, a description of the asset.|
|asset_serial||VARCHAR||Asset serial number(s), if any.|
Now, “assets” is not just computer equipment. They can also be furniture, software, and digital products. To address the people who are planning to keep things like “maker”, “model”, and “cost”:
- From experience, people will just enter asset names like “Macbook SOME-MODEL” and “some office chair”. Nobody really cares to go down into the tiny details, it’s a pain to micro-manage.
- “Cost” is not really a smart thing to put into the assets system. Just let the financial systems deal with the value and depreciation.
- It is good to keep serial numbers, suppliers, and date of purchase – To make repairs plus warranty claims.
2B) MOVEMENT HISTORY
|asset_id||VARCHAR||Primary key, asset ID.|
|move_date||DATE TIME||Primary key, movement timestamp.|
|move_status||VARCHAR||Up to you to decide. Maybe “purchase”, “repair”, “storage”, “assign”, and “return”.|
|move_notes||VARCHAR||Notes, if any.|
|user_id||INTEGER||Foreign key, if any. The user related to this entry. For example, “John sent this smartphone to the service center”.|
|supplier_id||INTEGER||Foreign key, if any. The related supplier.|
This one should be pretty self-explanatory. Keep track of when the asset is purchased, who it is assigned to, when it is sent for repair, etc…
|supplier_name||VARCHAR||Name of the supplier.|
|supplier_email||VARCHAR||Email of the supplier.|
|supplier_tel||VARCHAR||Telephone of the supplier.|
|supplier_address||VARCHAR||Address of the supplier.|
As above, it is a good idea to keep supplier information. Be it for repairs, replacements, or just getting more of the same products.
PART 3) ASSET TO USER
Which user is currently holding onto which asset. Take note, not all assets can be “assigned” to a user, nor does it make sense:
- The “meeting room table” is held by the chairman.
- The “receptionist table” is now held by the receptionist.
So yep… Common sense.
EXTRA BITS & LINKS
That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.
LINKS & REFERENCES
- Entity-Relationship Diagram Symbols and Notation – Lucid Chart
Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!