Storing object attrs as rows instead of columns

[CerVain]

If one stores an object's attributes as rows in a table with two columns (name, value) it makes for an incredibly flexible object/relational mapping implementation where the database can support all kinds of future objects without modification. The basic idea (very simplified) would then be to have 2 tables: one OBJ table to store object handles and types and one ATTR table to store the objects' attributes and values. Each ATTR row would of course point to a single object handle in the OBJ table.

But, of course there are trade offs associated with this flexibility. One is performance, which is not a major issue in my case. E.g., I realise that no matter which object i access different threads have to work with the same tables.

But the problem I haven't found a good solution to is sorting: how would I sort all user objects in the OBJ table according to their corresponding NAME value in the ATTR table. I would like to do this sort in the DBMS, not in application memory.

Any ideas as to how this kind of SELECT would look?

When performance is not an issue I find this simple design to be most interesting to speed development...

Best regards,
Marcus

 

[jimbopalmer]

select a1.value name, a2.value this, a3.value that
from obj, attr a1, atr a2, attr a3
where obj.ID =a1.id and a1.key = 'NAME'
and obj.id = a2.id (+) and nvl(a2.key,'THIS') = 'THIS'
and obj.id = a3.id (+) and nvl(a3.key,'THAT') = 'THAT'
order by 1

is that what you want? I tried to remain child-like, all I acheived was childish.

 

[jimbopalmer]

Oops this is ANSI SQL forum not Oracle, wrong syntax, sorry I tried to remain child-like, all I acheived was childish.

 

[CerVain]

Thanks for the reply!

To clarify, the tables could contain the following:

OBJ
----
ID TYPE
1 USER
2 USER
3 ACCOUNT
4 TRANSACTION

ATTR
----
OBJID NAME VALUE
1 NAME John Smith
1 ADDRESS Street 1
1 PHONE 1234-333444
2 NAME Greg Beard
2 ADDRESS Route 1
2 PHONE 111 2343 233
3 ... ...

And so on. How do I get all USER rows from OBJ sorted according to the value of e.g the ADDRESS row for each user so that user obj 2 comes before user obj 1?

I will probably use the MySQL DBMS or maybe PostgreSQL, but I would rather this was solved in non-proprietary SLQ to work with almost any DBMS.

?

 

[DaPi]

Marcus,
I think you have just started to build your own DBMS and/or CASE Tool! When you create a table to store the relationships between the objects, I will be sure of it!
I don't mean to sound rude - just a warning that you might be re-inventing the wheel. DaPi - no silver bullet

 

[rac2]

Why, in your structure, would an object necessarily have a NAME attribute? Isnt the point that different objects have different attributes? And if every object must have a name, as I suppose they must, why not add a column to the OBJ table for the name?

And what about history? Maybe the ATTR table should have a datetime column, meaning this was the value for this attribute the last time we looked at it.

Is this a database or a hash of hashes?

select a1.value Name, a2.value Address, a3.value that
from obj, attr a2, attr a3
JOIN obj.id = a1.id and a1.NAME = 'NAME'
JOIN obj.id = a2.id and a2.NAME = 'ADDRESS'
LEFT JOIN obj.id = a3.id and a3.NAME = 'THAT'
order by 1


Assuming every object has a attribute named NAME, we only want objects with a attribute named ADDRESS, and if an object has a value for THAT then we want to see that too.