Looking for help from anyone here for good solutions that are probably obvious but that I can’t seem to nail down.
The problem: I need to keep track of different types of devices in a database. The issue is that devices have some attributes that are common (ie asset tag) and others that are not (only certain devices have ip addresses). I don’t want to make a table called ‘device’ in the database that has 3000 fields to it (for every possible attribute a device might have), as having all those empty fields seems like a bad design. I thought about making a table called ‘device_type’, and maybe device_type has a field called ‘attributes’ that lists the fields for X type of device, but I am not sure how to go about doing that.
Also making a table for each type of device is not an easily manageable solution, and once I implement the schema I cannot change it later easily (SOX compliance).
The solution: Please share 
Related posts:
#1 by Paul on November 16, 2007 - 6:58 pm
Quote
Just add an ‘attributes’ table of key-value pairs.
devices has_many attributes
If you want to get really clever, you separate the attribute names out from the values, and use a habtm join. This would let you find all devices with a particular attribute easily, but would probably be more complicated than its worth.
devices hatbm attr_names has_many attr_values
#2 by Bernd on November 16, 2007 - 6:59 pm
Quote
Hello,
I would suggest three tables
1. table ‘device’
2. table ‘device_attribute’
3. table which stores the primary key from table 1 and table 2 and additional information
so you can store your device-information in table 1. All necessary attributes in table 2. And in table 3 you can store the relavant combination and proper information.
What do you think about this solution?
Bernd
#3 by Jörg on November 16, 2007 - 7:07 pm
Quote
I agree with Bernd, but be aware of the fact that all the attributes would have the same SQL type then, e.g. String (well, varchar that would be).
So you can’t store any attributes as numbers or dates, as all the attribute values would be stored in the same column.
Does anyone know a solution how to use the mentioned design in combination with different attribute types?
#4 by Jon Cooper on November 16, 2007 - 7:26 pm
Quote
Surely you need to think backwards from how you will use the data? If you require specific types within the DB, then you will have to account for every type and have blank fields; there’s no avoiding it, unless you can group specific device types and have a table per type, then join on a primary key when required from a master table of device key and type.
#5 by Lee on November 16, 2007 - 7:29 pm
Quote
The solution is PostgreSQL. Use a decent database like that, and you’ll be able to use tables that inherit from other tables.
#6 by admin on November 16, 2007 - 7:49 pm
Quote
Thanks for the replies all. I am on the same page Bernd, but it sounds like I need five tables total:
Device: fk(Device_Type) – The device and common properties of all devices
Device_Type: – Type of device, links to attributes for that specific type
Device_Attribute: – Description of attribute
Device_Attribute_Value: fk(Device_Attribute), fk(Device) – Actual value
Device_Attribute_To_Type: fk(Device_Attribute), fk(Device_Type) – Map attributes to type
Does this seem correct to everyone? I am wondering about the database performance implications of this, but it does seem flexible as my data can be all varchars
#7 by Bernd on November 16, 2007 - 8:57 pm
Quote
I don’t know If I can follow your question correctly?
Do you store extra info in ‘Device_Attribute_To_Type’? If not, I think, you don’t need this table?
Do you want to classify your attributes and build devices-types or is it possible to connect attributes directly to the device?
It’s very late (I am from europe) and I hope my questions are not too bad?!
Bernd
#8 by jon warner on November 17, 2007 - 12:47 am
Quote
I’d suggest just
devices: [id | dev_name | dev_type]
device_attributes: [device_id | attr_name | attr_value]
This is probably the most straightforward way. I suggest this because it sounds like device type is really only necessary for textual purposes. A more complex and probably less useful way would be
devices: [id | type_id | dev_name]
types: [id | type_name]
type_: [id | attr1 | attr2 | ... | attrN]
…
type_: [id | attr1 | attr2 | ... | attrN]
The second solution is less flexible. Really, if there’s concern about adding or removing types, the first solution is much better. Having specific type tables is only desirable if there’s no union between types and the type choices are well defined/immutable.
Further, hardcoding the specific potential values via field names or excessive joined tables doesn’t gain a lot except for extra complexity. Effectively, you run into problems when you realize you actually want to store ‘color’ or ‘weight’ later on down the road. With the two-table method you have a flexible, non-complex, speedy join and you can handle they varying attributes via your code business logic.
Put another way, ‘type’ isn’t really a value of the device.. You really just care about the attributes of the device.
#9 by Robin on November 17, 2007 - 1:59 am
Quote
We did something similar to the Bernd suggested, except the table that contains the attributes had columns for string, number and date (the data types we deal with the most). Other columns said what data type was present in this particular row, the name of this attribute, and an ID.
Then all you need is a join table that connects the device ID to the IDs in the attributes table.
#10 by admin on November 17, 2007 - 2:21 am
Quote
I see where you guys are coming from, definitely great ideas
The reason I am looking for a device_type table is that I want to make one form, thats fields are defined through the attributes that describe said device, dependent on device type. Ie a router may have a serial number / model number, but a projector may only have a model number, which would be defined by which attributes I link to that specific ‘device type’ through that table. Reasonable?
#11 by Bernd on November 17, 2007 - 5:48 am
Quote
OK, you want to define the device_type, so the user must fill in all necessary information for this type. And you don’t want to let the user assign the attributes to the device. Am I right?
device: [id | type_id | device_name]
type: [id | type_name]
attribute [id | name | data_type]
type_attribute: [id | type_id | attribute_id]
device_attributes: [id | device_id | attribute_id | char | number | date]
This is a complex structure. And now you have five tables, as you said before
In your application you can allow to attach only the attribute to a device, which are defined in the table type_attribute, based on your device_type.
Or you can show all attributes based on the selected device
Does that meet your requirements?
Bernd
#12 by admin on November 17, 2007 - 10:35 am
Quote
Bernd, thanks for the insight, that does meet all of the requirements.
#13 by Rudd-O on November 20, 2007 - 12:15 am
Quote
You need to look into Zope and an object-based database plus the Zopé Catalog facility which allows you to catalog and query these attributes using boolean queries. Honest, if what you want isn’t easily shoehornable without data integrity reducing hacks like the ones in the rest of the comments, you should use Zope. Plus you get to XML-RPC into Zope if you need to automate tasks.