Hello!


I am developing a database system that will allow users to store any
object whatsoever in a database.

Usually a database has predefined tables and fields.

What I need, is a table that basically has any field.

So I want to be able to say,

INSERT INTO objects (type, name) VALUES ('person', 'John')
INSERT INTO objects (type, name, num_employees) VALUES ('company',
'Apple', '10.000')
INSERT INTO objects (type, name, color) VALUES ('fruit', 'banana',
'yellow')

And that without defining the table columns. I just want to be able
to *use* those columns, they can default to varchar.

That's the kind of flexibility I need. Any ideas in this direction?
Anything is appreciated. Someone must have tried this before.



Thanks,
Bob

herbasher <robmnl (AT) gmail (DOT) com> wrote:

Oh, yes. It has been done before. It is called
Entity-Attribute-Value, and it is a very BAD thing.

Why not just use a shoebox?

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

On May 10, 5:10 pm, herbasher <rob... (AT) gmail (DOT) com> wrote:
Usually this involves some form of 'Value Attribute' mechanism and
some form of 'self defining data', 'data defining lookup', 'domain
value lookup', 'flexdata columns' and most recently 'anydata
datatype'. (Most of these are easily found using Google.)

I encourage you to proceed and welcome you to the hordes of us who
have attempted this. Learning from other people's experiences has
never stopped the new developers who aren't initally stuck with
administering or maintaining the system. \

Did it myself years ago. Stopped using the technique when I had the
opportunity to maintain both the software and data after it went into
production.

Everyone wants the flexibility - for input.
Reporting can only be accomplished by using a new program written by
you - using report writers fails (no data dictionary).
Statistics can only be accomplished by using a new program written by
you - using analysis tools fails (no data dictionary).
Maintenance can only be accomplished by using a new program written by
you - using DBA tools fails (no data dictionary).
Performance after a while sucks as the optimizers have no idea how to
extract the data in a timely fashion (no data dictionary).

No one (in their right mind) wants the administrative burden.
Invariably, after about 1 year's worth of data, the designers/
developers get called in to fix a minor data discrepancy. Two months
(and many, many hours/dollars) later, everyone wakes up. Since the
system invariably is 'self documenting' the designers/developers
become the only people who are able to unravel the data ... job
security that requires a person to be on call 24x365 sucks.


One question ... since you are disabling virtually all the benefits of
a database, why incur the overhead of the database? Flat files rule!

--
Hans Forbrich (mailto: Fuzzy.GreyBeard_at_gmail.com)
*** Feel free to correct me when I'm wrong!
*** Top posting [replies] guarantees I won't respond.

Every 2-3 months a newbie re-discovers the EAV design fallacy! I
found an old "cut & paste". Someone like you posted this:

CREATE TABLE EAV -- no key declared
(key_col VARCHAR (10) NULL,
attrib_value VARCHAR (50) NULL);

INSERT INTO EAV VALUES ('LOCATION', 'Bedroom');
INSERT INTO EAV VALUES ('LOCATION', 'Dining Room');
INSERT INTO EAV VALUES ('LOCATION', 'Bathroom');
INSERT INTO EAV VALUES ('LOCATION', 'courtyard');
INSERT INTO EAV VALUES ('EVENT', 'verbal aggression');
INSERT INTO EAV VALUES ('EVENT', 'peer');
INSERT INTO EAV VALUES ('EVENT', 'bad behavior');
INSERT INTO EAV VALUES ('EVENT', 'other');

CREATE TABLE EAV_DATA -note lack of constraints, defaults, DRI
(id INTEGER IDENTITY (1, 1) NOT NULL,
bts_id INTEGER NULL,
key_col VARCHAR (10) NULL,
attrib_value VARCHAR (50) NULL );

INSERT INTO EAV_DATA VALUES (1, 'LOCATION', 'Bedroom');
INSERT INTO EAV_DATA VALUES (1, 'EVENT', 'other');
INSERT INTO EAV_DATA VALUES (1, 'EVENT', 'bad behavior');
INSERT INTO EAV_DATA VALUES (2, 'LOCATION', 'Bedroom');
INSERT INTO EAV_DATA VALUES (2, 'EVENT', 'other');
INSERT INTO EAV_DATA VALUES (2, 'EVENT', 'verbal aggression');
INSERT INTO EAV_DATA VALUES (3, 'LOCATION', 'courtyard');
INSERT INTO EAV_DATA VALUES (3, 'EVENT', 'other');
INSERT INTO EAV_DATA VALUES (3, 'EVENT', 'peer');

Ideally, the result set of the query would be Location Event count
(headings if possible)

Bedroom verbal aggression 1
Bedroom peer 0
Bedroom bad behavior 0
Bedroom other 2
Dining Room verbal aggression 0
Dining Room peer 0
Dining Room bad behavior 0
Dining Room other 0
Bathroom verbal aggression 0
Bathroom peer 0
Bathroom bad behavior 0
Bathroom other 0
courtyard verbal aggression 0
courtyard peer 1
courtyard bad behavior 0
courtyard other 1

Also, if possible, another query would return this result set. (I
think I know how to do this one.)

Location Event count
Bedroom verbal aggression 1
Bedroom other 2
courtyard peer 1
courtyard other 1

Here is an answer From: Thomas Coleman

SELECT Locations.locationvalue, Events.eventvalue,
(SELECT COUNT(*)
FROM (SELECT LocationData.locationvalue,
EventData.eventvalue
FROM (SELECT TD1.bts_id, TD1.value AS locationvalue
FROM eav_data AS TD1
WHERE TD1.key = 'location') AS LocationData
INNER JOIN
(SELECT TD2.bts_id, TD2.value AS eventvalue
FROM eav_data AS TD2
WHERE TD2.key = 'event'
) AS EventData
ON LocationData.bts_id = EventData.bts_id
) AS CollatedEventData
WHERE CollatedEventData.locationvalue = Locations.locationvalue
AND CollatedEventData.eventvalue = Events.eventvalue
FROM (SELECT T1.value AS locationvalue
FROM EAV AS T1
WHERE T1.key = 'location') AS Locations,
(SELECT T2.value AS eventvalue
FROM EAV AS T2
WHERE T2.key = 'event') AS Events
ORDER BY Locations.locationvalue, Events.eventvalue ,
SELECT Locations.locationvalue, Events.eventvalue
(SELECT COUNT(*)
FROM (SELECT LocationData.locationvalue,
EventData.eventvalue
FROM (SELECT TD1.bts_id, TD1.value AS locationvalue
FROM eav_data AS TD1
WHERE TD1.key = 'location') AS LocationData
INNER JOIN
(SELECT TD2.bts_id, TD2.value AS eventvalue
FROM eav_data AS TD2
WHERE TD2.key = 'event') AS EventData
ON LocationData.bts_id = EventData.bts_id)
AS CollatedEventData
WHERE CollatedEventData.locationvalue = Locations.locationvalue
AND CollatedEventData.eventvalue = Events.eventvalue)
FROM (SELECT T1.value AS locationvalue
FROM EAV AS T1
WHERE T1.key = 'location') AS Locations,
(SELECT T2.value AS eventvalue
FROM EAV AS T2
WHERE T2.key = 'event') AS Events;

Is the same thing in a proper schema as:

SELECT L.locationvalue, E.eventvalue, COUNT(*)
FROM Locations AS L, Events AS E
WHERE L.btd_id = E.btd_id
GROUP BY L.locationvalue, E.eventvalue;

The reason that I had to use so many subqueries is that those
entities are all plopped into the same table. There should be
separate tables for Locations and Events.

The column names are seriously painful. Don't use reserved words like
"key" and "value" for column names. It means that the developer *has*
surround the column name with double quotes for everything. And they
are too vague to be data element names anyway!

There is such a thing as "too" generic. There has to be some structure
or everything becomes nothing more than a couple of tables called
"things". The real key (no pun intended) is commonality. Is there a
pattern to the data that they want to store? It may not be possible to
create one structure to rule them all and in the darkness bind them.

"To be is to be something in particular; to be nothing in particular
is to be nothing." --Aristotle

All data integrity is destroyed. Any typo becomes a new attribute or
entity. Entities are found missing attributes, so all the reports are
wrong.

Try to write a single CHECK() constraint that works for all the
attributes of those 30+ entities your users created because you were
too dumb or too lazy to do your job. It can be done! You need a case
expression almost 70 WHEN clauses for a simple invoice and order
system when I tried it as an exercise.

Try to write a single DEFAULT clause for 30+ entities crammed into one
column. Impossible!

Try to set up DRI actions among the entities. If you thought the WHEN
clauses in the single CASE expression were unmaintainable, wait until
you see the "TRIGGERs from Hell" -- Too bad that they might not fit
into older SQL Server which had some size limits. Now maintain it.

For those who are interested, there are couple of links to articles I
found on the net:

Generic Design of Web-Based Clinical Databases
http://www.jmir.org/2003/4/e27*/

The EAV/CR Model of Data Representation
http://ycmi.med.yale.edu/nadka*rni/eav_CR_contents.htm

An Introduction to Entity-Attribute-Value Design for Generic
Clinical Study Data Management Systems
http://ycmi.med.yale.edu/nadka*rni/I...20*systems.htm

Data Extraction and Ad Hoc Query of an Entity- Attribute- Value
Database
http://www.pubmedcentral.nih.g*ov/ar...=pub*med&pubme...

Exploring Performance Issues for a Clinical Database Organized Using
an Entity-Attribute-Value Representation
http://www.pubmedcentral.nih.g*ov/ar...=pub*med&pubme...

herbasher wrote:
Oh yea.
Go to http://asktom.oracle.com/pls/asktom/...7400037023::NO
get fourth doc (worst practices) and read it.

Gints Plivna
http://www.gplivna.eu

"Gints Plivna" <gints.plivna (AT) gmail (DOT) com> wrote

In addtion, it may be time to revive "stupid dtatabase tricks" from
comp.databases.theory about 3 years back.

On May 11, 3:50 pm, "David Cressey" <cresse... (AT) verizon (DOT) net> wrote:

Well I know I am going against holy cows here, but gotta solve my
problem: storing any object whatsoever efficiently. I don't need any
joins.

I'll probably go with this, not EAV:

objects_columns: object_id, colname, colnumber
objects: id, object_type, col1, col2, ... col30


Thanks for all the info, especially Hans Forbich's.


Best, Rob

"herbasher" <robmnl (AT) gmail (DOT) com> wrote

Holy cow, Batman!

Why are you storing the data in a database, rather than a file?

On May 12, 10:03 am, "David Cressey" <cresse... (AT) verizon (DOT) net> wrote:
Scapegoat? Something to blame when the eventual storage overhead,
high CPU requirements and lousy performance become issues?

Or possibly because this is to be implemented in 2-tier or n-tier and
he doesn't want toget involved in networking and serialization
issues. Let the design create those for him. <g>

Either way, he's guaranteed job security.

"storing any object whatsoever efficiently" - oho - nice requirement.
Almost like build any house quickly. Or probably get me anything for
10 bucks


Khe khe How the old proverb was? Something like:

Smart people learn from other people mistakes.
Normal people learn from their mistakes.
Stupid people don't learn even from their mistakes.

Let's hope you'll learn from your mistakes

Gints Plivna
http://www.gplivna.eu