Search code examples
ms-accessdatabase-normalization

IT Asset Tracking Database - Normalization Issue

this is my first time posting here, but definitely not the first time I have found answers on this site!

I am working on creating an IT asset tracking database for our growing retail chain. We have evaluated several different services, but none of them quite satisfy all our needs - so we are going the custom route. Eventually, we plan on using a SQL database and a web front-end, but for now, Access is the quickest way to get something up and running.

We are looking to track not only assets that are in our stores, but also assets being used by employees. These are the tables that I have currently (that pertain to this issue):

  • Assets (model, serial number, etc)
  • Locations (list of stores)
  • Employees (list of all employees)

In my initial database design, we were just tracking store assets. I had my Assets table and my Stores table, along with an AssetAssignment Table containing the foreign keys from both.

If I want to be able to assign an asset to either a store OR an employee, what would be the best way to go about doing this? Should I create a StoreAssignments table and an EmployeeAssignments table? If I do that, would it make it more complicated when trying to run reports/queries for all assets?

Should I create some sort of Entity table, in which every location and employee has an EntityID. I could then assign the asset to the Entity, rather than directly to an employee or store? Is something like that the right way to go - or the complete opposite direction?

I have played around with Access and SQL databases for many years, but never anything too advanced. I am definitely not a DBA!

Any suggestions you can provide would be much appreciated. I am sure I will have more questions to follow ;)

Solution

  • Well there are multiple ways to solve the problem, but a simple one is probably to create a new Table entity where you place the assignments.

    enter image description here

    The assignment table is where you add an entry for each assignment, no matter what type it is.

    So Asset Id is the foreign Key from the Asset table. Assignment Type is some why to identify if it is assigned to a Location or employee (this could be simple text, or a number) Assignment Id is then the foreign key from either Location or Employee

    If you will only have have a single asset assigned to one place at a time, then the assignment table can have the asset id as the key.

    There are other ways, which might be better or worse but there is no point make it more complex than you need to and over engineering it. A good solution is simply one that works and meets your needs.