Asset Management System ERD (With Database Structure)

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

 

 

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.

 

 

 

PART 1) USERS

COLUMN	DATA TYPE	NOTES
user_id	INTEGER	Primary key.
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

2A) ASSETS

COLUMN	DATA TYPE	NOTES
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

COLUMN	DATA TYPE	NOTES
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…

 

2C) SUPPLIERS

COLUMN	DATA TYPE	NOTES
supplier_id	INTEGER	Primary key.
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

COLUMN	DATA TYPE	NOTES
asset_id	VARCHAR	Primary key.
user_id	INTEGER	Primary key.

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.

 

 

LINKS & REFERENCES

• Entity-Relationship Diagram Symbols and Notation – Lucid Chart