14

I'm trying to create a database that contains a list of equipment. All of the equipment will have certain common attributes (such as manufacturer, model #, serial #, etc.), then there are other attributes that are specific to a certain piece of equipment (ie, a modem will have an access #, whereas a solar panel will have an output capacity). I'm not sure how to represent these changing attributes with good database design principles, I've tried searching the web, but I'm not entirely sure what to search for.

I've come up with the following possible solutions and my initial thoughts on them:

  1. Have one big table with every possible attribute and just put null where it's not applicable. Obviously this has some flaws.

  2. Have a separate table for each equipment type. This seems like it might be a nightmare to use, if I want to print a list of all the equipment, how do I know which tables to lookup?

  3. Have a table with the common attributes, and other tables for each equipment type accessed with a foreign key to store the extra attributes. I could probably make this work, but it would be cumbersome and just doesn't feel like a very good solution.

  4. An entity-attribute-value type model. Just doesn't seem like a very good fit for what I want to do.

I don't have a lot of experience with databases so I'm learning as I go here, any links relating to this problem or "must read" articles on database design would be appreciated. Thanks!

EDIT: First off, I found out that I needed to Google "Inheritance mapping", that might help anyone else that has a similar question. To solve the problem I ended up using a hybrid of #2 and #3. It was actually pretty easy, works well, and solves the problem of adding additional equipment types without the complexity of EAV. Thanks for all the comments and suggestions!

neurotik
  • 1,390
  • 11
  • 15
  • 1
    chek the answers in the following post: http://stackoverflow.com/questions/870808/entity-attribute-value-database-vs-strict-relational-model-ecommerce-question –  Jul 21 '12 at 04:11
  • 1
    This post contradicts some other articles I read saying EAV should be avoided, thoughts anyone? – neurotik Jul 21 '12 at 04:37

4 Answers4

5

Options 1, 2, and 3 share one very serious flaw: you have to modify the underlying table schema when someone dreams up a new attribute. In the case of Option 1 the problem is compounded by the possibility that a new equipment type will be introduced. How sure are you that the set of attributes is fixed for all time? How happy will you be to take outages or tell the client that no, you can't have a new attribute?

If you are very likely to do queries off common attributes, you might try a hybrid of 3 and 4, with a dash of 2 thrown in splitting on attribute type rather than equipment type, which seems much more volatile. Option 4, if I understand correctly, is a normal form version of option 1 which solves all its inherent problems (sparseness and brittleness). 

INVENTORY( id*, model, manufacturer, serial )
ATTRIBUTE( id*, name, type, description )
INVENTORY_FACT_STRING( inv_id*, attr_id*, value )
INVENTORY_FACT_NUMBER( inv_id*, attr_id*, value )
INVENTORY_FACT_LIST_STRING( inv_id*, attr_id*, ordinal*, value )

etc.

Judge Mental
  • 5,209
  • 17
  • 22
  • New equipment being introduced is a certainty, someone wanting to add an attribute ex post facto is not likely, but possible. Outages are not a problem, but saying that new attributes can't be added is out of the question as new equipment needs to be able to be added and I can't say for certain that the current attributes will cover all future cases. – neurotik Jul 21 '12 at 04:27
  • This sounds a lot like EAV. Do not be afraid! – Judge Mental Jul 21 '12 at 04:46
  • Wow, overwhelming support of EAV from everyone here. Not what I expected, amazing how a few negative articles at the beginning of your research can really throw you off track! I'll give it a go. Thanks everyone! – neurotik Jul 21 '12 at 05:12
3

Alternatives 1, 2, and 3 are outlined by Martin Fowler in one of his books, and on his website.

Single Table Inheritance (option 1)

Concrete Table Inheritance (option 2, sort of)

Class Table inheritance (option 3)

My preference is option 3. Each one has its place in the general scheme of things.

EAV accomodates adding new attributes on the fly very well. But when it comes time to turn the data into useful information, an EAV database can be a nightmare.

I have a longer answer, which I will post on demand.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
1

I think that you faced a regular database normalization. You need tables like:

Items -> Id, Name, Model, Brand Id
Brands -> Id, Name
Attribute Names -> id, name
Attribute Mappings -> Id, Names Id, Items Id, Attribute Description

In case if there is more than one Attribue, list then in Attribute Tables and associate with Product Id etc. Try to come up with 3rd normalized form

Database Normalization

Andrew
  • 7,619
  • 13
  • 63
  • 117
0

It's hard problem to solve for any SQL database. There is no great answer for MySQL.

1) Works and you can add some views for important equipment types. It reduces the number joins and allows queries and indexes on each field.

2) You can use a union all query in view. PostgreSQL and Informix have table inheritance.

3) This is frequently an implementation choice. Again, you can use views for the joins.

4) PostgreSQL, Informix, Oracle, IBM DB2 and MS SQL Server have an XML data type support to implement the value pairs.

At higher level you could develop a meta model of the equipment in XML. Then you can use this model to generate schema SQL queries and CRUD code.

Tim Child
  • 2,994
  • 1
  • 26
  • 25