patternMinor
Inventory database structure when inventory items have varying attributes
Viewed 0 times
varyinginventorydatabaseattributesitemsstructurewhenhave
Problem
I'm building an inventory database to store enterprise hardware information. The devices the database keeps track of range from workstations, laptops, switches, routers, mobile phones, etc. I'm using device serial numbers as the primary key. The problem I'm having is that the other attributes for these devices vary and I don't want to have fields in the inventory table that are unrelated to other devices. Below is a link to an ERD of part of the database (some FK relations are not shown). I'm trying to set it up, for example, so a device with a workstation device type can't be put into the phones table. This seems to require the use of a lot of triggers to validate the device type or class, and new tables anytime a different device with different attributes will be tracked; not to mention all of the one-to-one relationships which will make joins a nightmare (there are more one-to-one relationships not shown).
I looked into setting up attribute tables that can be mapped to serial numbers, but that would allow attributes that do not apply to a device type to be assigned to a device, e.g., someone could assign a phone number attribute to a workstation if they wanted. I found an explanation on this site that gave the following structure:
This structure would work great if the attributes were all applicable to the items I am storing. For example if the database was storing only mobile phones, the attributes could be things like touchscreen, trackpad, keyboard, 4G, 3G...whatever. In that case, they all apply to phones. My database would have attributes like hostname, circuitType, phoneNumber, which only apply to specific types of devices.
I want to set it up so only the attributes that apply to a given device type can be assigned to a device of that type. Any suggestions on how to setup this database? I'm not sure if this is a proper use of one-to-one relationships, or if there is a better way to do this. Thank you in advance for taking the time to look into this.
Her
I looked into setting up attribute tables that can be mapped to serial numbers, but that would allow attributes that do not apply to a device type to be assigned to a device, e.g., someone could assign a phone number attribute to a workstation if they wanted. I found an explanation on this site that gave the following structure:
This structure would work great if the attributes were all applicable to the items I am storing. For example if the database was storing only mobile phones, the attributes could be things like touchscreen, trackpad, keyboard, 4G, 3G...whatever. In that case, they all apply to phones. My database would have attributes like hostname, circuitType, phoneNumber, which only apply to specific types of devices.
I want to set it up so only the attributes that apply to a given device type can be assigned to a device of that type. Any suggestions on how to setup this database? I'm not sure if this is a proper use of one-to-one relationships, or if there is a better way to do this. Thank you in advance for taking the time to look into this.
Her
Solution
In your case the best approach is a variation on the Entity-Attribute-Value (EAV) model. There are lots of people who shy away from EAV because it is unhelpful in some ways and misused a lot of the time. However, EAV is a solution that works well for your specific requirements.
The variation that you want to include for your situation is to abstract the attributes one level away from your entities (i.e. your inventory items). Essentially you want to define device types which have a list of attributes. Then you define device instances which have values for each of the attributes which devices of that type are supposed to have.
Here is an ERD sketch:
This lets you control which attributes need to be filled out for a device while letting devices of different type have different lists of attributes. It also makes it easy for you to compare across devices by lining their attributes up against one-another.
The variation that you want to include for your situation is to abstract the attributes one level away from your entities (i.e. your inventory items). Essentially you want to define device types which have a list of attributes. Then you define device instances which have values for each of the attributes which devices of that type are supposed to have.
Here is an ERD sketch:
DEVICE_ATTRIBUTE contains the values for each type of generic attribute. DEVICE_TYPE defines the list of generic attributes which apply to a given type of device (these are the TYPICAL_DEVICE_ATTRIBUTEs.This lets you control which attributes need to be filled out for a device while letting devices of different type have different lists of attributes. It also makes it easy for you to compare across devices by lining their attributes up against one-another.
Context
StackExchange Database Administrators Q#15199, answer score: 8
Revisions (0)
No revisions yet.