A comprehensive IT Asset Management (ITAM) database schema for tracking hardware, software, and equipment across an organization. Includes asset categorization, employee assignment tracking with check-in/check-out workflows, maintenance and repair logging, and vendor management. Suitable for IT departments, facilities management, or any organization that needs to track physical and digital assets throughout their lifecycle.
assignments is a history, not a current-holder column. Each row has an assigned_date and a nullable returned_date, so an open assignment is one where the return is still null — and a laptop that passed through four people leaves four rows rather than one field overwritten four times.
The asset carries both an asset_tag and a serial_number because they come from different places. The serial is the manufacturer's and identifies the hardware; the tag is the organisation's own sticker and survives a motherboard replacement. Searching by either has to work, which is why both are columns.
depreciation_years sits on asset_categories rather than on the asset. Depreciation is an accounting policy about a class of equipment — laptops over three years, servers over five — so the schedule is defined once and every asset in the category inherits it.
A current_holder column on the asset would be one read and would answer nothing else: who had it when it was damaged, how long it sat unassigned, whether it was returned at all. The assignments table makes those queries trivial. The price is that finding the current holder is a filter on a null returned_date rather than a direct read, and nothing stops two open assignments for one asset unless a constraint is added.
Storing only what an asset cost to buy understates what it costs to keep. Each maintenance row carries a cost and a performed_at, so the question a replacement decision actually depends on — are we spending more to repair this than to replace it — is a sum rather than an impression. It also makes a vendor's reliability measurable from rows.
The people are in the HR system, not here, and duplicating an employee table would create records that are never updated when someone leaves. The honest cost is that nothing enforces the reference — an assignment can name someone who no longer exists, and listing everything a departing employee holds depends on matching an identifier this schema does not own.
Putting the schedule on each asset would allow per-item policies and guarantee inconsistency, since the same model bought twice would depreciate differently. Keeping it on the category means a policy change applies to the whole class at once. The limitation is that assets bought under an older policy are revalued by the change, so a finance system needs the schedule frozen per asset at purchase.