Tag/value based database...

Main Page | Report this Page

Computers Forum Index » Computer - Databases » Tag/value based database...

Page 1 of 1

Tag/value based database...

Author

Message

Fab...

Posted: Sat Jul 18, 2009 12:00 pm

Guest

Hello All,
anybody has experience with tag/value based databases ?
I mean those DB with an extensive use of tag/value pair to give attribute to
a record,es:

ID User Department
-------------------------
123 Frank ICT
456 Paul Accounting

ID Tag Value
-------------------
123 Sport Archery
123 Car Volvo
456 Car Porsche

Anybody has experience about limitation encountered like, slow performances,
query limitation, etc...

Thanks in advance
F.

strawberry...

Posted: Sat Jul 18, 2009 4:55 pm

Guest

On Jul 18, 9:00 am, "Fab" <nos... at (no spam) nospam.nospam> wrote:

Quote:

What about:
123 Joined 1998
456 Joined 1998
123 DOB 1969-12-18
456 DOB 1977-05-05
123 Salary 1000000
456 Salary 450000

SQL provides a whole plethora of lovely datatypes to choose from. With
this design you're restricted to one: VARCHAR

Plus it's a nightmare to manage. I'm sure it has its uses (see the
Wikipedia entry for EAV) but it's not for me.

Fab...

Posted: Sun Jul 19, 2009 6:10 pm

Guest

Hi Zac,
thanks for your reply, it was addressing exactly what I was searching for.
In particular the "Downsides" chapter of the Wikipedia page address the
problems the model implementation could lead.
On the other way, EAV is really attracting me. Indeed I have to manage very
different types of objects and that model could get the user quite
independent from the phsical implementation of the DB's tables.
Indeed, actually I've have to stick with many tables, one for each specific
item.

I'll read carefully the pages again.

Thanks a lot!
F.

"strawberry" <zac.carey at (no spam) gmail.com> ha scritto nel messaggio
news:6d5295b3-a332-41d4-bb36-94b3f3cb978a at (no spam) y19g2000yqy.googlegroups.com...
On Jul 18, 9:00 am, "Fab" <nos... at (no spam) nospam.nospam> wrote:

Quote:

Hello All,
anybody has experience with tag/value based databases ?
I mean those DB with an extensive use of tag/value pair to give attribute
to
a record,es:

ID User Department
-------------------------
123 Frank ICT
456 Paul Accounting

ID Tag Value
-------------------
123 Sport Archery
123 Car Volvo
456 Car Porsche

Anybody has experience about limitation encountered like, slow
performances,
query limitation, etc...

Thanks in advance
F.

--CELKO--...

Posted: Sun Jul 19, 2009 7:22 pm

Guest

Quote:

Anybody has experience about limitation encountered like, slow performances, query limitation, etc...

You couldn't find what a disaster EAV is with a quick Google? Let me
do a "Cut & Paste" for you:

====================== Someone like you posted this:

CREATE TABLE EAV -- no key declared
(key_col VARCHAR (10), -- what does null mean?
attrib_value VARCHAR (50)); -- what does null mean?

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 --no constraints, defaults, DRI
(id INTEGER IDENTITY (1, 1) NOT NULL, --vague names
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." --Law of Identity

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/nadkarni/eav_CR_contents.htm

An Introduction to Entity-Attribute-Value Design for Generic Clinical
Study Data Management Systems
http://ycmi.med.yale.edu/nadkarni/Introduction%20to%20EAV%20systems.htm

Data Extraction and Ad Hoc Query of an Entity— Attribute— Value
Database
http://www.pubmedcentral.nih.gov/articlerender.fcgi?tool=pubmed&pubme...

Exploring Performance Issues for a Clinical Database Organized Using
an Entity-Attribute-Value Representation
http://www.pubmedcentral.nih.gov/articlerender.fcgi?tool=pubmed&pubme...

A really good horror story about this kind of disaster is at:

http://www.simple-talk.com/opinion/opinion-pieces/bad-carma/

Tony Toews [MVP]...

Posted: Mon Jul 20, 2009 2:21 am

Guest

"Fab" <nospan at (no spam) nospam.nospam> wrote:

Quote:

On the other way, EAV is really attracting me. Indeed I have to manage very
different types of objects and that model could get the user quite
independent from the phsical implementation of the DB's tables.

I can see how portions of that concept are useful. For example I have something
I've called Custom Fields for lack of a better term. (If anyone has better
terminology from a user perspective I'd sure appreciate it.) The idea being that
there is some structured data about a peice of equipment, such as a truck, tractor,
skid steer loader or bulldozer, that isn't required for my app but the user wants to
track and search on.

An example might be colour, Gross Vehicle Weight, unit cell phone number, engine type
and serial number, transmission type and serial number or tire size.. For those
types of data sure this makes some sense.

But for other data that all units have such as unit number, description, make and
model? No thanks. It's way too much work for the app and me.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Granite Fleet Manager http://www.granitefleet.com/

Steve Hayes...

Posted: Mon Jul 20, 2009 5:17 am

Guest

On Sat, 18 Jul 2009 08:00:03 GMT, "Fab" <nospan at (no spam) nospam.nospam> wrote:

Quote:

I've used two - Superfile and Inmagic.

Both seemed fairly fast to me, though complex queries on unindexed fields
could take a while.

--
Steve Hayes from Tshwane, South Africa
Web: http://hayesfam.bravehost.com/stevesig.htm
Blog: http://methodius.blogspot.com
E-mail - see web page, or parse: shayes at dunelm full stop org full stop uk

Page 1 of 1

All times are GMT The time now is Wed Nov 25, 2009 2:13 am Computers Forum Index