I would not recommend doing this - not at all. There is NOTHING WRONG with actually having to have a data model.

There is something wrong with this sort of generic code.

I read the first paragraph and just frankly "stopped" (well, read the last one, you could use ansi outer join syntax, but DON'T - just say NO)

You will end up hating this model, it won't perform, it won't scale, it'll be like pulling teeth getting your data back out.

Just a short while ago, I was consulting with a customer - bad, serious bad, performance from their database. Step one - describe the model to me. They say "well, there are these four tables..." I know what is coming next and pull up chapter one from Effective Oracle by Design on the screen, namely this section:


<quote>
Don’t Use Generic Data Models


Frequently, I see applications built on a generic data model for “maximum flexibility” or applications built in ways that prohibit performance. Many times, these are one and the same thing! For example, it is well known you can represent any object in a database using just four tables:
Create table objects ( oid int primary key, name varchar2(255) );
Create table attributes
( attrId int primary key, attrName varchar2(255),
datatype varchar2(25) );
Create table object_Attributes
( oid int, attrId int, value varchar2(4000),
primary key(oid,attrId) );
Create table Links ( oid1 int, oid2 int,
primary key (oid1, oid2) );

IT WAS AT THIS POINT THEY SAID - hey, you already know our model, this is great - even the table names are the same!

I then pointed out the TITLE of the section...

That’s it. No more CREATE TABLE for me! I can fill the ATTRIBUTES table with rows like this:
insert into attributes values ( 1, 'DATE_OF_BIRTH', 'DATE' );
insert into attributes values ( 2, 'FIRST_NAME', 'STRING' );
insert into attributes values ( 3, 'LAST_NAME', 'STRING' );
commit;
And now I’m ready to create a PERSON record:
insert into objects values ( 1, 'PERSON' );
insert into object_Attributes values( 1, 1, '15-mar-1965' );
insert into object_Attributes values( 1, 2, 'Thomas' );
insert into object_Attributes values( 1, 3, 'Kyte' );
commit;
insert into objects values ( 2, 'PERSON' );
insert into object_Attributes values( 2, 1, '21-oct-1968' );
insert into object_Attributes values( 2, 2, 'John' );
insert into object_Attributes values( 2, 3, 'Smith' );
commit;
And since I’m good at SQL, I can even query this record to get the FIRST_NAME and LAST_NAME of all PERSON records:
ops$tkyte@ORA920> select
max( decode(attrName, 'FIRST_NAME', value, null )) first_name,
2 max( decode( attrName, 'LAST_NAME', value, null ) ) last_name
3 from objects, object_attributes, attributes
4 where attributes.attrName in ( 'FIRST_NAME', 'LAST_NAME' )
5 and object_attributes.attrId = attributes.attrId
6 and object_attributes.oid = objects.oid
7 and objects.name = 'PERSON'
8 group by objects.oid
9 /

FIRST_NAME LAST_NAME
-------------------- --------------------
Thomas Kyte
John Smith
Looks great, right? I don’t need to create tables anymore, because I can add columns at the drop of a hat (with an insert into the ATTRIBUTES table). The developers can do whatever they want, and the DBA can’t stop them. This is ultimate flexibility. I’ve seen people try to build entire systems based on this model.
But how does this model perform? Miserably, terribly, and horribly. A simple select first_name, last_name from person query is transformed into a three-table join with aggregates and all. Furthermore, if the attributes are NULLABLE—that is, there might not be a row in OBJECT_ATTRIBUTES for some attributes—you may need to use an outer join instead of just joining, which might remove more optimal query plans from consideration.
Writing queries with this model might look straightforward. For example, if I wanted to get everyone who was born in March or has the last name of Smith, I could simply take the query to get the FIRST_NAME and LAST_NAME of all PERSON records and wrap an inline view around it:
ops$tkyte@ORA920> select *
2 from (
3 select
max(decode(attrName, 'FIRST_NAME', value, null)) first_name,
4 max(decode(attrName, 'LAST_NAME', value, null)) last_name,
5 max(decode(attrName, 'DATE_OF_BIRTH', value, null))
date_of_birth
6 from objects, object_attributes, attributes
7 where attributes.attrName in ( 'FIRST_NAME',
'LAST_NAME', 'DATE_OF_BIRTH' )
8 and object_attributes.attrId = attributes.attrId
9 and object_attributes.oid = objects.oid
10 and objects.name = 'PERSON'
11 group by objects.oid
12 )
13 where last_name = 'Smith'
14 or date_of_birth like '%-mar-%'
15 /

FIRST_NAME LAST_NAME DATE_OF_BIRTH
-------------------- -------------------- --------------------
Thomas Kyte 15-mar-1965
John Smith 21-oct-1968
So, it looks easy to query, but think about the performance! If you had a couple thousand OBJECT records and a couple tens of thousands of OBJECT_ATTRIBUTES, Oracle would need to process the entire inner group by query first and then apply the WHERE clause.
This is not a made-up data model, one that I crafted just to make a point. This is an actual data model that I’ve seen people try to use. Their goal is ultimate flexibility. They don’t know what OBJECTS they need, and they don’t know what ATTRIBUTES they will have. Well, that is what the database was written for in the first place: Oracle implemented this thing called SQL to define OBJECTS and ATTRIBUTES and lets you use SQL to query them.
</quote>