Dynamic Parameter List

Is it possible to have a dynamic number of parameters? Basically I have a
vertical table that will contain a number of records. As records are added
to this table, they will need to be passed to a stored procedure. So say I
have this structure:
SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.dynamic_param1,
@.dynamic_param2, etc. etc.
Tbl_Dynamic_Params
ID
1 Nationality
2 Net Worth
3 Hair Color
4 Shoe Size
So I want to pass:
SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.Nationaltiy,
@.Net_Worth, @.HairColor, @.ShoeSize.
Is this possible without using dynamic SQL?"James" <neg@.tory.com> wrote in message
news:Ol%23pUYC5HHA.3684@.TK2MSFTNGP02.phx.gbl...
> Is it possible to have a dynamic number of parameters? Basically I have a
> vertical table that will contain a number of records. As records are
> added to this table, they will need to be passed to a stored procedure.
> So say I have this structure:
> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.dynamic_param1,
> @.dynamic_param2, etc. etc.
> Tbl_Dynamic_Params
> ID
> 1 Nationality
> 2 Net Worth
> 3 Hair Color
> 4 Shoe Size
> So I want to pass:
> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.Nationaltiy,
> @.Net_Worth, @.HairColor, @.ShoeSize.
> Is this possible without using dynamic SQL?
>
You can have *optional* parameters in a proc, yes. Just specify a default
value for those parameters. For example:
CREATE PROC usp_proc
@.p1 INT = NULL,
@.p2 INT = NULL
AS ...
Both @.p1 and @.p2 are optional and will default to null if they are not
specified. Obviously your parameter *names* must be known at runtime
otherwise you couldn't write any code that used those parameters could you?
Important: Do not use "sp_" as a prefix for your procs. "sp_" is the name
used for system procs and has a special meaning that will cause unwanted
side-effects to your code.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I don't actually use sp as a prefix, it was merely a visual cue. I
understand optional parameters, but I want the list of parameters to be
dynamic, not just the arguments...that's the crux of the problem. I don't
think there's a really good solution to this.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uX5l3nC5HHA.5724@.TK2MSFTNGP05.phx.gbl...
> "James" <neg@.tory.com> wrote in message
> news:Ol%23pUYC5HHA.3684@.TK2MSFTNGP02.phx.gbl...
>> Is it possible to have a dynamic number of parameters? Basically I have
>> a vertical table that will contain a number of records. As records are
>> added to this table, they will need to be passed to a stored procedure.
>> So say I have this structure:
>> SP_Update_Contact @.first_name, @.last_name, @.city, @.state,
>> @.dynamic_param1, @.dynamic_param2, etc. etc.
>> Tbl_Dynamic_Params
>> ID
>> 1 Nationality
>> 2 Net Worth
>> 3 Hair Color
>> 4 Shoe Size
>> So I want to pass:
>> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.Nationaltiy,
>> @.Net_Worth, @.HairColor, @.ShoeSize.
>> Is this possible without using dynamic SQL?
> You can have *optional* parameters in a proc, yes. Just specify a default
> value for those parameters. For example:
>
> CREATE PROC usp_proc
> @.p1 INT = NULL,
> @.p2 INT = NULL
> AS ...
> Both @.p1 and @.p2 are optional and will default to null if they are not
> specified. Obviously your parameter *names* must be known at runtime
> otherwise you couldn't write any code that used those parameters could
> you?
> Important: Do not use "sp_" as a prefix for your procs. "sp_" is the name
> used for system procs and has a special meaning that will cause unwanted
> side-effects to your code.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>|||"James" <neg@.tory.com> wrote in message
news:eK3xtqC5HHA.5212@.TK2MSFTNGP04.phx.gbl...
>I don't actually use sp as a prefix, it was merely a visual cue. I
>understand optional parameters, but I want the list of parameters to be
>dynamic, not just the arguments...that's the crux of the problem. I don't
>think there's a really good solution to this.
>
I don't understand what you mean when you say you want the "list of
parameters to be dynamic". Parameters have names and types. If you don't
know the names and types in advance then how could you possibly write a proc
that made use of them - even assuming it was possible to pass them as
parameters?
Maybe what you want is an array. SQL Server has a data structure that is
much more powerful than an array: a table! :-)
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>> ... but I want the list of parameters to be dynamic, not just the
>> arguments...that's the crux of the problem.
The crux of the problem has to do with the poor design scheme you use to
represent various attributes as values. If you have a static set of
properties that you have to manage, treat them as columns rather than values
in a column. The right answer is that you should reconsider the logical
design. Mixing up data with metadata is always a recipe for messy and
complex solutions.
--
Anith|||You're preaching to the choir. This isn't my design. Let me rewind. A
colleague approach me with this problem.
Contacts Table:
ID First_Name Last_Name ... ...
Pretty basic. Now there is additional data that needs to be stored. I.E.
Shoe Size, Hat Size, and various other data. However, not every record will
have all of these fields. Also, fields need to be added on the fly with the
lowest amount of maintenance possible.
Performance is a huge consideration. He has approximately 2 million records
in the Contacts table and say, 10 fields +/-. He believes that performance
will be better if he has a flat table that is a 1-to-1 with this table that
stores the extra columns...as opposed to a more normalized alternative that
they have in place now which CRAWLS.
Should I talk him out of this redesign, encourage it, or suggest an
alternative? If so, that begs the question...what alternative?
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23rOPW9C5HHA.4964@.TK2MSFTNGP06.phx.gbl...
>> ... but I want the list of parameters to be dynamic, not just the
>> arguments...that's the crux of the problem.
> The crux of the problem has to do with the poor design scheme you use to
> represent various attributes as values. If you have a static set of
> properties that you have to manage, treat them as columns rather than
> values in a column. The right answer is that you should reconsider the
> logical design. Mixing up data with metadata is always a recipe for messy
> and complex solutions.
> --
> Anith
>|||"James" <neg@.tory.com> wrote in message
news:eLGgUPD5HHA.5316@.TK2MSFTNGP04.phx.gbl...
> You're preaching to the choir. This isn't my design. Let me rewind. A
> colleague approach me with this problem.
> Contacts Table:
> ID First_Name Last_Name ... ...
> Pretty basic. Now there is additional data that needs to be stored. I.E.
> Shoe Size, Hat Size, and various other data. However, not every record
> will have all of these fields. Also, fields need to be added on the fly
> with the lowest amount of maintenance possible.
> Performance is a huge consideration. He has approximately 2 million
> records in the Contacts table and say, 10 fields +/-. He believes that
> performance will be better if he has a flat table that is a 1-to-1 with
> this table that stores the extra columns...as opposed to a more normalized
> alternative that they have in place now which CRAWLS.
> Should I talk him out of this redesign, encourage it, or suggest an
> alternative? If so, that begs the question...what alternative?
>
Two million rows is not large and should be well within the capabilities of
even an entry level server. If performance is inadequate then start looking
at indexing, query plans, statistics and procedure design.
As for the logical model, a normalized design is the right place to start.
Nothing you have said suggests any need for a change from that.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:elfSovC5HHA.3400@.TK2MSFTNGP03.phx.gbl...
>.
> Maybe what you want is an array. SQL Server has a data structure that is
> much more powerful than an array: a table! :-)
>.
This is a disturbing statement from someone who purports to
understand a strong typed system. I assume its rationale
is not intellectual. Shall I pull out the sql couch and
start the examination? :-)
www.beyondsql.blogspot.com|||You mean like printf. I seriously doubt it considering the hoops C has to go
through to make it work.
How about name/value pairs passed into one large varchar?
set @.paramerters = 'first_name='+@.first_name+';last_name='+@.last_name
create procedure sp_update_contact @.parameters
as
declare @.parameters varchar(max)
loop through the string looking for the ; delimiter, then split the
name/value pair and put it in a table variable:
declare @.MyArray TABLE (
name varchar(20)
value varchar(20)
)
Then do as you please.
Since the name portion had to corospond to a column in the table, you better
have some decent error checking an support for an error return.
"James" <neg@.tory.com> wrote in message
news:Ol%23pUYC5HHA.3684@.TK2MSFTNGP02.phx.gbl...
> Is it possible to have a dynamic number of parameters? Basically I have a
> vertical table that will contain a number of records. As records are
> added to this table, they will need to be passed to a stored procedure.
> So say I have this structure:
> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.dynamic_param1,
> @.dynamic_param2, etc. etc.
> Tbl_Dynamic_Params
> ID
> 1 Nationality
> 2 Net Worth
> 3 Hair Color
> 4 Shoe Size
> So I want to pass:
> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.Nationaltiy,
> @.Net_Worth, @.HairColor, @.ShoeSize.
> Is this possible without using dynamic SQL?
>sql

Dynamic data elements for a data collection application

What is the better table design for a data collection application.
1. Vertical model (pk, attributeName, AttributeValue)
2. Custom columns (pk, custom1, custom2, custom3...custom50)

Since the data elements collected may change year over year, which
model better takes of this column dynamicness(mittal.pradeep@.gmail.com) writes:
> What is the better table design for a data collection application.
> 1. Vertical model (pk, attributeName, AttributeValue)
> 2. Custom columns (pk, custom1, custom2, custom3...custom50)
> Since the data elements collected may change year over year, which
> model better takes of this column dynamicness

The vertical model is certainly cleaner from a relational perspective.
It also requires less maintenance.

But admittedly queries can be more complex. If attributes can be of
different data types, you need some triggers to check this. A tip
is that the sql_variant data type is good in this case.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||1. But the data collection and reporting is in horizontal format. If
collected data is edited vertically, won't there be a extra steps of
converting horizontally obtained data to vertical and then vertical to
horizontal reports. In custom column model data always remains
horizontal. Won;t performance not be a issue in taking care of two
extra steps required in vertical model.
2. Won;t the concurrency be a issue, considering the fact that a
logical single horizontal row is edited as say 10 rows. Two people
might be changing same Primary key's different attributes at the same
time.

Erland Sommarskog wrote:
> (mittal.pradeep@.gmail.com) writes:
> > What is the better table design for a data collection application.
> > 1. Vertical model (pk, attributeName, AttributeValue)
> > 2. Custom columns (pk, custom1, custom2, custom3...custom50)
> > Since the data elements collected may change year over year, which
> > model better takes of this column dynamicness
> The vertical model is certainly cleaner from a relational perspective.
> It also requires less maintenance.
> But admittedly queries can be more complex. If attributes can be of
> different data types, you need some triggers to check this. A tip
> is that the sql_variant data type is good in this case.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||(mittal.pradeep@.gmail.com) writes:
> 1. But the data collection and reporting is in horizontal format. If
> collected data is edited vertically, won't there be a extra steps of
> converting horizontally obtained data to vertical and then vertical to
> horizontal reports. In custom column model data always remains
> horizontal. Won;t performance not be a issue in taking care of two
> extra steps required in vertical model.

If you are to present ten of those custom values as columns in a report,
you get a 10-way self-join. Certainly bulky in code. Performance is
probably not top-notch, but I don't see that it would be absymal.

> 2. Won;t the concurrency be a issue, considering the fact that a
> logical single horizontal row is edited as say 10 rows. Two people
> might be changing same Primary key's different attributes at the same
> time.

Good point. This can be handled fairly easily, but it requires more
careful programming than the horizontal method.

Overall, there certainly is a tradeoff. If the set of custom fields are
faily stable, only change once per year or so, you might be prepared to
take the extra maintenance cost. But if users asks for new fields every
week, then the horizontal method could be a nightmare.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks a lot for the reply.
1. I am not able to understand why i require a 10 way join though. If i
have a mapping between custom column and actual column, all i need is a
dyanamic sql generated from the mapping.

E.g.
table
--
pk, custom1, custom2, custom3...custom10

map
--
customColName ActualColName
custom1 ActualName1
custom2 ActualName2
...

Now I can generate dynamic sql using map.

2. As long as number of custom columns is enough to take care data
element additions which happen in a year. All that is needed is
addition of new elements to mapping table to decifer newly assigned
custom columns

Erland Sommarskog wrote:
> (mittal.pradeep@.gmail.com) writes:
> > 1. But the data collection and reporting is in horizontal format. If
> > collected data is edited vertically, won't there be a extra steps of
> > converting horizontally obtained data to vertical and then vertical to
> > horizontal reports. In custom column model data always remains
> > horizontal. Won;t performance not be a issue in taking care of two
> > extra steps required in vertical model.
> If you are to present ten of those custom values as columns in a report,
> you get a 10-way self-join. Certainly bulky in code. Performance is
> probably not top-notch, but I don't see that it would be absymal.
> > 2. Won;t the concurrency be a issue, considering the fact that a
> > logical single horizontal row is edited as say 10 rows. Two people
> > might be changing same Primary key's different attributes at the same
> > time.
> Good point. This can be handled fairly easily, but it requires more
> careful programming than the horizontal method.
> Overall, there certainly is a tradeoff. If the set of custom fields are
> faily stable, only change once per year or so, you might be prepared to
> take the extra maintenance cost. But if users asks for new fields every
> week, then the horizontal method could be a nightmare.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Neither. The design flaw you are calling a vertical model is actually
known as "EAV" or "Entity-Attribute-Value" because it is a common
newbie mistake. I have no idea what your #2 means.

As your data elements change, you need to re-design the schema --
constraints, keys, data types, etc. Learn RDBMS and do it right.

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 a 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 lopped into the same table. There should be separate tables for
Locations and Events.

The column names are seriously painful. Beyond the fact that I
personally hate underscores in column names, using underscores at the
end of the column name is really non-intuitive. I removed them for my
example and came across the next column name faux pas. Don't use "key"
and "value" for column names. It means that the developer *has*
surround the column name with square brackets for everything which is a
serious pain.

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." --Aristole

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

ry 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.

ry 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/...%20*systems.htm

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

Exploring Performance Issues for a Clinical Database Organized Using
an Entity-Attribute-Value Representation
http://www.pubmedcentral.nih.g*ov/a...l=pub*med&pubme...|||(mittal.pradeep@.gmail.com) writes:
> Thanks a lot for the reply.
> 1. I am not able to understand why i require a 10 way join though. If i
> have a mapping between custom column and actual column, all i need is a
> dyanamic sql generated from the mapping.
> E.g.
> table
> --
> pk, custom1, custom2, custom3...custom10
>
> map
> --
> customColName ActualColName
> custom1 ActualName1
> custom2 ActualName2
> ...
> Now I can generate dynamic sql using map.

When I said 10-way join I was thinking of the vertical solution. For the
horisontal solution it's a simple join - once you have gone through all
that SQL building. To me, this sounds more complex to implement. Then
again, if the user selects dynamically which columns he wants to see,
the horizontal solution would require dynamic SQL as well.

> 2. As long as number of custom columns is enough to take care data
> element additions which happen in a year. All that is needed is
> addition of new elements to mapping table to decifer newly assigned
> custom columns

I didn't realise that you had this mapping table. One could say that
this is a kind of compromise between the horizonal model and an entirely
static vertical model.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||CELKO,

If I change the data elements 10 times during the year, I cannot change
the front end 10 to accomodate the same. I need to come up with a
dyanamic solution to take care of same. Vertical approach is the best
way to get the same. Using custom columns is a compromise, as vertical
approach is harder to implement.

--CELKO-- wrote:
> Neither. The design flaw you are calling a vertical model is actually
> known as "EAV" or "Entity-Attribute-Value" because it is a common
> newbie mistake. I have no idea what your #2 means.
> As your data elements change, you need to re-design the schema --
> constraints, keys, data types, etc. Learn RDBMS and do it right.
> 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 a 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 lopped into the same table. There should be separate tables for
> Locations and Events.
> The column names are seriously painful. Beyond the fact that I
> personally hate underscores in column names, using underscores at the
> end of the column name is really non-intuitive. I removed them for my
> example and came across the next column name faux pas. Don't use "key"
> and "value" for column names. It means that the developer *has*
> surround the column name with square brackets for everything which is a
> serious pain.
> 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." --Aristole
> All data integrity is destroyed. Any typo becomes a new attribute or
> entity. Entities are found missing attributes, so all the reports are
> wrong.
> ry 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.
> ry 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/...20*systems..htm
>
> Data Extraction and Ad Hoc Query of an Entity- Attribute- Value
> Database
> http://www.pubmedcentral.nih.g*ov/a...l=pub*med&pubme...
>
> Exploring Performance Issues for a Clinical Database Organized Using
> an Entity-Attribute-Value Representation
> http://www.pubmedcentral.nih.g*ov/a...l=pub*med&pubme...