row-driven tables 1

[Mitch38]

A group at work claims that the familiar relational database table design of a row being an occurrence with attributes defined in each column does not work as well in applications as its reverse. For example, to add a column, location, to TableA which now has columns ID and Name means changing the table structure and redeploying the executable forms; but if TableA is flipped into TableB which has columns ID, Attribute and Value then this is faster and cheaper as we're just adding rows and not changing structure, etc. I see lots of problems here with data types and integrity; any other problems?

 

[Turkbear]

No sure what exactly you would place as data in the attribute and values columns - can you give examples...

Say I want to store information about parts in inventory..
I want:

part_nbr number(8),part_name varchar2(45),qty_on_hand number(8)

For each of 5000 parts I use..
(Eventually, I want to link each to its supplier in a separate table, but we'll do that later)

How would this table look under that new design idea?

 

[sem]

I thoroughly recommend you to avoid such key-value structures, because of many reasons:
1. You need complex validation even for easiest cases of null/not null, datatype,check.
2. No indexing by any value available.
3. Security issues (everybody may see all rows or otherwise you should create a view for each specific case)
3. Total performance (each time you need 1 "object" you need a number of fetches).
4. Application partitioning (you can not extract some self sufficient part as a separate application as all the data is contained in single table).
5. Routine maintenance: you can not switch some part of application (subsystem) offline for administrative tasks.

We use third-party application that implemented some kind of object model using this approach, and scaling it is real headache. When it was purchased, its main table contained some thousand of rows and the whole application looked like very fast and flexible. Now it contains some millions - and we need a really big box to serve this monster, as all the users work with the same table. When 3 years ago our managers decided to bought cluster to move this application to OPS toimprove performance (God bless that "professionals"!), we came home at 4AM: single node worked much faster because of multiple "hard pings", but how might they explain to big boss what was $***K paid for?

The only place you may use this approach is implementing small lists of values,but even for this case you may hit some problems.

Regards, Dima

 

[HereistheAnswer]

the relational database beauty will not be there. obtaining parent-child relationship is tough. It's totally not recommended to use such design. however, if you need to add columns in future, any time, then only u can use such design. for example, you want to keep one xml data in a table, each xml line will be a row. the table structure may like:
tag_name
tag_id
parent_tag
child_tag_id
item
attribute1_name
attribute1_value
attribute2_name
attribute2_value

now it may happen that, your xml contains more than 2 attributes. in that case, you can not fit the 3rd attribute in this structure. for this, you can use one attribute table with following structure:
tag_id
attribute_name
attribute_value
so that u can handle the situation where more that 2 attributes come.

 

[Mitch38]

Dima: thanks, I totally agree & appreciate your articulate validation.
Turkbear: here's a "normal" parts table:
id nbr name qty
1 10000001 widget 24
2 10000002 doohickey 189
and here's a row-driven parts table:
id attribute value
1 nbr 10000001
1 name widget
1 qty 24
2 nbr 10000002
2 name doohickey
2 qty 189
The size (and redundancy) in the row-driven table is much greater than for its normal relational equivalent. For every row in the latter there are (#columns - 1) rows in the former. Also, the data type for the "value" column has to be char which means there's a lot of background transforming of date or integer or float fields into text.