Re: New DB-design - help and documentation pointers appreciated

On 03/07/10 02:43, Rikard Bosnjakovic wrote:

> However, the table that really makes me cringe in fear of un-knowledge
> is this one: parameters. A parameter for a component can be something
> that tells the designer that it must exceed a specific amount of
> voltage. It's also got a unit. For example, the diode 1N4148 has a
> parameter Vr (reverse voltage) that equals around 40-50 V (volts)
> depending on the manufacturer. It also has another parameter, If
> (forward current), that's around 100-120 mA (milliamperes) depending
> on manufacturer. There may be parameters describing working
> temperature, maximum rated frequency, and so forth. Different kind of
> components has different kind of parameters.
>
> Parameters for the components are in no way to be fixed. Users are
> supposed to be able to enter new parameters on their own, re-use
> already entered components, and so forth. If User #1 adds a 1N4148
> with 10 parameters in his "library", another User #2 with 1N4148 may
> want to add it to his library, but the components may be from
> different manufactures and may deviate in its parameter values. (This
> deviation is often very small, but it exists).

Much as I generally loathe the approach, this is probably a case for EAV
(Entity-Attribute-Value) design. Google will tell you more.

It's really an area where the structured typed sets approach of
relational SQL databases starts to fall down a bit. You might even find
yourself better off with another database system that permits
multi-valued fields, variable row lengths, keyword/value rows, or the like.

> So, my question (I think) is: I have really no clue except that to use
> a separate table for each separate kind of parameter, but since my
> users are supposed to be able to add new parameter types, how am I
> supposed to keep track of everything?

You've hit one of those areas where SQL databases kind of suck. You'll
have to use one of the well understood workarounds like EAV and live
with their limitations, or find a database better suited to the data.

It's easy to build a typed EAV table - just add a column for each type
you might want to use, and a CHECK constraint that requires that all but
one of those columns be NULL for any given row. It's ugly, but so is EAV.

--
Craig Ringer