Having instance-like behaviour in databases

Question

Hey there everyone,

Sorry for the bad title, but I have no idea how to put this in short. The Problem is the following:

I have a generic item that represents a group, lets call it Car. Now this Car has attributes, that range within certain limits, lets say for example speed is between 0 and 180 for a usual Car. Imagine some more attributes with ranges here, for example Color is between 0 and 255 whatever that value might stand for.

So in my table GenericItems I have:

ID    Name

 1    Car

And in my Attributes I have:

ID   Name   Min_Value    Max Value
 1   Speed     0            180
 2   Color     0            255

The relation between Car and Attributes is thus 1:n.

Now I start having very specific instances of my Car for example a FordMustang, A FerrariF40, and a DodgeViper. These are specific instances and now I want to give them specific values for their attributes.

So in my table SpecificItem I have:

ID    Name           GenericItem_ID

 1    FordMustang         1
 2    DodgeViper          1
 3    FerrariF40          1

Now I need a third table SpecificAttributes2SpecificItems, to match attributes to SpecificItems:

ID    SpecificItem_ID   Attribute_ID   Value

 1         1                1           120        ;Ford Mustang goes 120 only
 2         1                2           123        ;Ford Mustang is red
 3         2                1           150        ;Dodge Viper goes 150
 4         2                2           255        ;Dodge Viper is white
 5         3                1           180        ;FerrariF40 goes 180
 6         3                2             0        ;FerrariF40 is black

The problem with this design is, as you can see, that I am basically always copying over all rows of attributes, and I feel like this is bad design, inconsistent etc. How can I achieve this logic in a correct, normalized way?

I want to be able to have multiple generic items, with multiple attributes with min/max values as interval, that can be "instantiated" with specific values

Answer 1

The easiest way to use inheritance in database models is to use an ORM tool. For Python there is SQLAlchemy, Django and others.

Now you should wonder whether e.g. a Ford Mustang is a kind of Car, or an instance of Car. In the former case, you should create a ford_mustang table defining the ford_mustang attributes. The ford_mustang table should then also have a foreign key to the car table, where the generic attributes of each FordMustang are specified. In the latter case, each kind of car is just a row in the Car table. Either way, each attribute should be represented in a single column.

Validation of the attributes is typically done in the business logic of the application.

Answer 2

It looks like you're trying to replicate Entity Atribute Value as a design, which leads to a lot of ugly tables (well, usually it is one single table for everything).

http://en.wikipedia.org/wiki/Entity-attribute-value%5Fmodel http://ycmi.med.yale.edu/nadkarni/Introduction%20to%20EAV%20systems.htm

Discussing EAV tends to lead to "religious wars" as there are very few good places to use it (many folks say there are zero good places) and there are other folks who think that since it is so very flexible, it should be used everywhere. If I can find the reference I'm looking for, I'll add it to this.

Answer 3

There is a school of thought which holds that any attempt to build an EAV model in an RDBMS constitutes "bad design" but we won't go there. Ooops, looks like somebody else already has done.

I'm not certain what worries you. SpecificAttributes2SpecificItems is an intersection table (the clue is in the name). Necessarily it includes links to the Attributes and the SpecificItems. How could it not?

You probably need to have a MinVal and a MaxVal on SpecificAttributes2SpecificItems, as certain items will have a more limited range than that permitted by the GenericItems. For instance, everybody knows that Ferraris should only be available in red.

Answer 4

Couple of ideas:

First, you should consider making your "genericgroups" table an "attribute" rather than something hovering above the rest of the data.

Second, you may have an easier time having each attribute table actually holding the attributes of the items, not simply the idea of the attributes. If you want to have a range, consider either an enum type (for item names) or simply an integer with a set maximum (so the value of the color_value column can't be above 255). This way you would end up with something more like:

  Item Table
   ID    Name 

    1    FordMustang 
    2    DodgeViper  
    3    FerrariF40

   ItemType Table:

   ItemID     Type
     1         Car
     2         Car
     3         Car


   ItemColor Table:

   ItemID     ColorID
     1         123
     2         255
     3           0

   MaxSpeed Table

   ItemID     MaxSpeedID

     1        120
     2        150
     3        180