Good database design, variable number of attributes

Question

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!

Answer 1

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.