Re: Database development
On Mon, 26 Apr 2010, Martin Gregorie wrote:
On Mon, 26 Apr 2010 13:37:46 +0200, Zlatko Duric wrote:
Well, my objects are documents - those are "nodes". But so are the
"folders" holding the documents. And so are their parents. And I want,
for example, all the docs that have the keyword FOO and their parent is
"Reports".
Thats what additional indexes are for. For example, to support the
following example you'd want indexes on doc.keyword and folder.name,
since they'll probably be used often for data selection and sorting.
We'll assume that the DB designer was sensible and put indexes on both
table's prime keys. Your example requirement could be satisfied with a
single query, which would involve a prime key join and use the additional
indexes to select the rows to be included in the dataset. Something like
this:
SELECT required fields
from folder f, document d
where f.key = d.folderkey
and d.keyword = 'FOO'
and f.name = 'Reports';
Any decent RDBMS should be able to optimise that type of query and would
return just the required result set.
I suspect documents may have more than one keyword, in which case your
query might look like:
SELECT required fields
from folder f, document d
where f.key = d.folderkey
and d.keyword LIKE '%FOO%' -- if keywords are packed space-separated into one column (bad idea)
and f.name = 'Reports';
Or:
SELECT required fields
from folder f, document d, keyword k
where f.key = d.folderkey and d.key = k.documentkey
and k.word = 'FOO' -- if keyword is a relation (documentkey, word)
and f.name = 'Reports';
Or even:
SELECT required fields
from folder f, document d, document_keywords dk, keyword k
where f.key = d.folderkey and d.key = dk.documentkey and dk.keywordkey = k.key
and k.word = 'FOO' -- if keywords are first-class and there is a join table (fully normalised but overcomplicated)
and f.name = 'Reports';
That's messy - iterate through all the folders to find the stuff I need.
Why would you need to do that? A correctly written query will only
return the data you need, present it in the order you want and not slow
database updates down by requiring additional reference tables which
must be maintained.
I think the OP is saying he would have to navigate in that way if he was
using ORM rather than SQL. That still isn't true, though, because in JPA
you have JP-QL, which is more or less isomorphic to SQL, and in fact
simpler, because the mapping of relationships to properties makes joins
easier to express:
select doc from Document doc
where
'FOO' in doc.keywords
and doc.folder.name = 'Reports'
(i think)
tom
--
Everybody with a heart votes love