3

I'm looking for some advice on database design.

I'm working on a small asset management utility for learning and to simplify some tasks I do in tracking IT assets. I have a table for "Asset" which represents a physical asset and then "AssetTypes" which represent the different types (e.g. Phone, Computer, Laptop, Server). Each of the assets has some common information (Name, Make, Model, Value, Purchase date) and then unique values based on the Asset Type.

What is the best way to store this extra information (e.g. PC may have CPU, RAM, and HDD; a laptop would have those three attributes, plus ScreenSize; and a monitor would just have ScreenSize)?

I see two ways to achieve, one is a separate table for each Asset Type, which means every-time I add an asset, I need to update my code. The other is to use an "Attributes" (or similar) table that has for each asset a list of the Extra columns. When you add an asset of Type X, it gives you all the additional attributes to fill in.

If I use the attributes, how should I define them and the data types they can use (e.g. Text, Int, Bool)?

I plan on using MS SQL Server as my DB, with a C#.NET front end.

Happy with any advice or thoughts.

marked as duplicate by Joel Brown, Md Haidar Ali Khan, Erik Darling sql-server Sep 14 '18 at 12:59

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • The situation you're asking about is much discussed here and on Stack Overflow. My answer to this question gives the pros and cons of using what you're calling an "Attributes" table. You can also search for the term "EAV" (Entity Attribute Value). You'll see that people have strong opinions on this subject. Look for the most nuanced opinions and make up your mind how to proceed with your design. – Joel Brown Sep 12 '18 at 22:36