Need advice on product specs table

[Sam]

Hi,

I'm trying to figure the best way to store product specs data e.g.
dimensions, weight, power requirements, etc.

Because the database will contain a wide variety of products and in time,
there will be new specification categories, I'm inclined to have a table that
defines specification categories and another where SpecIDs and values are
saved for each ProductID.

The problem I have with this approach is that I won't be able to take
advantage of data types in SQL Server. I'd hate to keep dimension or weight
information as varchar.

The other approach I can think of is to add these spec categories as columns
to my product table but then I'll end up with too many columns and every time
there's a new spec category, I have to make table design changes.

Which is the best way to go?
--
Thanks,

Sam

[Sam]

Here's an interesting article about the EAV model. It definitely discouraged
me from using the EAV model.
http://weblogs.sqlteam.com/davidm/articles/12117.aspx

I'd still like to get some opinions though.
--
Thanks,

Sam

[Dan Rawlings]

This really depends on the level of product variation. I've seen the EAV
model successfully deployed as well as the bucket method.
BaseProduct B1 B2 B3 B4...
I've also seen the EAV model implemented with sophisticated middle tier
constraint builders.

The article raises some interesting questions. I've always felt the front
end should validate data entry as soon as possible and the backend should
maintain referential integrity only. Placing complex business rules on the
backend may get tedious as varied products are added and new options are
added.

Don't underestimate the desire of sales and data entry to break rules. Like,
this one customer can order chairs with casters not in the system.

Although it doesn't seem you need to implement an indented bill of materials
model, searching on that subject may provide some additional ideas.