How would you model data variables variance on common scheme? SQL

Answer 1

I've been doing this in Oracle.

I had the following tables:

t_class (id RAW(16), parent RAW(16)) -- holds class hierachy.
t_property (class RAW(16), property VARCHAR) -- holds class members.
t_declaration (id RAW(16), class RAW(16)) -- hold GUIDs and types of all class instances
t_instance (id RAW(16), class RAW(16), property VARCHAR2(100), textvalue VARCHAR2(200), intvalue INT, doublevalue DOUBLE, datevalue DATE) -- holds 'common' properties

t_class1 (id RAW(16), amount DOUBLE, source RAW(16), destination RAW(16)) -- holds 'fast' properties for class1.
t_class2 (id RAW(16), comment VARCHAR2(200)) -- holds 'fast' properties for class2
--- etc.

RAW(16) is where Oracle holds GUIDs

If you want to select all properties for an object, you issue:

SELECT  i.*
FROM    (
        SELECT  id 
        FROM    t_class
        START WITH
                id = (SELECT class FROM t_declaration WHERE id = :object_id)
        CONNECT BY
                parent = PRIOR id
        ) c
JOIN    property p
ON      p.class = c.id
LEFT JOIN
        t_instance i
ON      i.id = :object_id
        AND i.class = p.class
        AND i.property = p.property

t_property hold stuff you normally don't search on (like, text descriptions etc.)

Fast properties are in fact normal tables you have in the database, to make the queries efficient. They hold values only for the instances of a certain class or its descendants. This is to avoid extra joins.

You don't have to use fast tables and limit all your data to these four tables.

For you task it will look like this (I'll use strings in square brackets instead of GUID's for the sake of brevity):

t_class

id             parent

[ClassItem]    [ClassUnknown]
[ClassMonitor] [ClassItem]
[ClassLCD]     [ClassMonitor]

t_property

class          property

[ClassItem]    price
[ClassItem]    vendor
[ClassItem]    model
[ClassMonitor] size
[ClassLCD]     matrixType

t_declaration

id             class
[1]            [ClassLCD] -- Iiyama ProLite E1700

t_instance  -- I'll put all values into one column, disregarding type (INT, VARCHAR etc)

id             class           property         value

[1]            [ClassItem]     price            $300
[1]            [ClassItem]     vendor           Iiyama
[1]            [ClassItem]     model            ProLite E1700s
[1]            [ClassMonitor]  size             17
[1]            [ClassLCD]      matrixType       TFT

If you need some complex query that searches on, say, size AND matrixType, you may remove them from property and instance and create another table:

t_lcd (id RAW(16), size INT, matrixType VARCHAR2(200))

id             size            matrixType

[1]            17              TFT

and use it to join with other properties instead of t_declaration in the query above.

But this model is viable even without the fast tables.

Answer 2

How many product types do you expect? Do they each have their own application logic?

You can do a generalized model called the "entity attribute value" model, but it has a LOT of pitfalls when you're trying to deal with specific properties of a product. Simple search queries turn into real nightmares at times. The basic idea is that you have a table that holds the product ID, property name (or ID into a properties table), and the value. You can also add in tables to hold templates for each product type. So one set of tables would tell you for any given product what properties it can have (possibly along with valid value ranges) and another set of tables would tell you for any individual product what the values are.

I would caution strongly against using this model though, since it seems like a really slick idea until you have to actually implement it.

If you number of product types is reasonably limited, I'd go with your second solution - one main product table with base attributes and then additional tables for each specific type of product.

Answer 3

There is a name for this pattern. It's called "generalization specialization".

If you search on "generalization specialization modeling" you'll get some articles on how to do this. Some of these articles lean towards relational modeling and SQL, while others lean towards object modeling.