Query over name/value table

Hello NG,
In my program users should be able to manage their documents. To allow users
to search for documents they can use predefined attributes like Title or
Description or they can assign their own custom attributes. I'm using the
following tables for this.
Document (Stores the predefined document attributes)
- Id
- Title
- Description
- ...
Attributes (Stores the custom attributes)
- Id
- Name
AttributeValues (Stores the custom attribute values assigned to a document)
- DocumentId
- AttributeId
- Value
Users should be able to do complex queries to find documents (and/or/not,
nested parenthesises). They should not only be able to query for the
predefined attributes, but also for the custom attributes. I also want to
have all attributes (predefined or custom) present for each document that
matches the query conditions to load them into a document-object in my
application.
What is the best way to do such queries in SQL? The problem that I have is
with the custom attributes. When I want to query for the predefined
attributes, I could just do a:
SELECT * FROM Document, AttributeValues WHERE Document.Title like '%bla%'
AND AttributeValues.DocumentId = Document.Id ORDER BY Document.ID
The result would give me all attribnutes of every document that contains
"bla" in the title, with the custom attributes in seperate rows.
However I can't come up with a good query that would return a similar result
set for searching custom attributes.
Can you help me?
Thanks a lot!
Thomas Krause
P.S. The user should be able to query an infinite number of custom or
predefined attributes at the same time and combine them with nested
ANDs/ORs/etc!Ok. Thank you very much.
What's about the performance? Is there a better/faster way than having this
subqueries.
We are not talking about a very big dataset here, but if there is a
noticible faster way for doing this I would prefer it anyway.
For example: I think you could do the same with multiple joins of the
AttributeValues-table. Would this be faster?
Thanks,
Thomas Krause
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote:
> How about (add an exists per custom attribute):
> SELECT * FROM Document
> WHERE Document.Title like '%bla%'
> and exists (select 1 from AttributeValues
> where AttributeValues.DocumentId = Document.Id
> and AttributeValues.AttributeId = '@.AttributeID'
> and AttributeValues.Value like '%blablabla%'
> )
> ORDER BY Document.ID
> This sounds like it will require dynamic SQL to handle N number of custom
> attributes, but this should give you an idea of the simplified approach.
> If
> you do use dynamic SQL, check out these links for some security concerns.
> Some reading on SQL Injection:
> http://www.sqlservercentral.com/col...qlinjection.asp
> Advanced Injection:
> http://www.nextgenss.com/papers/adv...l_injection.pdf
> http://www.nextgenss.com/papers/mor...l_injection.pdf
> Dynamic SQL and SQL injection:
> http://www.sommarskog.se/dynamic_sql.html#sp_executesql
>
> "Thomas Krause" <Forum.Thomas.Krause.RemoveThis@.gmx.de> wrote in message
> news:e37qq1$m2u$01$1@.news.t-online.com...
> users
> document)
> result
>|||You could do the same with multiple joins, and you would have to test each
aproach to see which is faster. I would expect the exists and the inner
join to perform pretty much the same, while the outer join would likely be
slower. In any case, proper indexes are critical (probably one on either
(AttributeId, Value) or (DocumentId, AttributeId, Value). The indexes may
determine whther SQL Server treats the exists and inner join differently or
not.
If the attribute has to be found, use an inner join:
SELECT * FROM Document
WHERE Document.Title like '%bla%'
inner join AttributeValues
on Document.Id = AttributeValues.DocumentId
and AttributeValues.AttributeId = '@.AttributeID'
and AttributeValues.Value like '%blablabla%'
ORDER BY Document.ID
If the attribute is optional, use an outer join:
SELECT * FROM Document
WHERE Document.Title like '%bla%'
left outer join AttributeValues
on Document.Id = AttributeValues.DocumentId
and AttributeValues.AttributeId = '@.AttributeID'
and AttributeValues.Value like '%blablabla%'
ORDER BY Document.ID
"Thomas Krause" <Forum.Thomas.Krause.RemoveThis@.gmx.de> wrote in message
news:e382bl$fvp$02$1@.news.t-online.com...
> Ok. Thank you very much.
> What's about the performance? Is there a better/faster way than having
this
> subqueries.
> We are not talking about a very big dataset here, but if there is a
> noticible faster way for doing this I would prefer it anyway.
> For example: I think you could do the same with multiple joins of the
> AttributeValues-table. Would this be faster?
> Thanks,
> Thomas Krause
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote:
custom
concerns.
http://www.sqlservercentral.com/col...qlinjection.asp
or
the
(and/or/not,
to
that
'%bla%'
contains
>|||Ok, thanks again.
If the performance is not that different, I'll go with the subqueries, since
they are a little bit easier to generate dynamically.
Thomas Krause
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote:
> You could do the same with multiple joins, and you would have to test each
> aproach to see which is faster. I would expect the exists and the inner
> join to perform pretty much the same, while the outer join would likely be
> slower. In any case, proper indexes are critical (probably one on either
> (AttributeId, Value) or (DocumentId, AttributeId, Value). The indexes may
> determine whther SQL Server treats the exists and inner join differently
> or
> not.
> If the attribute has to be found, use an inner join:
> SELECT * FROM Document
> WHERE Document.Title like '%bla%'
> inner join AttributeValues
> on Document.Id = AttributeValues.DocumentId
> and AttributeValues.AttributeId = '@.AttributeID'
> and AttributeValues.Value like '%blablabla%'
> ORDER BY Document.ID
> If the attribute is optional, use an outer join:
> SELECT * FROM Document
> WHERE Document.Title like '%bla%'
> left outer join AttributeValues
> on Document.Id = AttributeValues.DocumentId
> and AttributeValues.AttributeId = '@.AttributeID'
> and AttributeValues.Value like '%blablabla%'
> ORDER BY Document.ID
> "Thomas Krause" <Forum.Thomas.Krause.RemoveThis@.gmx.de> wrote in message
> news:e382bl$fvp$02$1@.news.t-online.com...
> this
> custom
> concerns.
> http://www.sqlservercentral.com/col...qlinjection.asp
> or
> the
> (and/or/not,
> to
> that
> '%bla%'
> contains
>