Re: Database development
This message is in MIME format. The first part should be readable text,
while the remaining parts are likely unreadable without MIME-aware tools.
---910079544-1410690323-1272313580=:14878
Content-Type: TEXT/PLAIN; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8BIT
On Mon, 26 Apr 2010, Lew wrote:
Tom Anderson wrote:
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 [sic] think)
What you wrote looks correct, but I am fairly certain it requires
'keywords' to be expressed as a collection (probably a 'Set'), which is
best done if the keywords are in their own table, which they should be
anyway. I don't think it works with the space-separated list of
keywords as in your first of three examples.
I believe you're absolutely right - sorry, i should have been clearer on
what context that query required.
Your second example, as you hint, is probably optimal and the third
overkill.
Probably. The advantage of the fully normal design is that queries like:
"find me all the documents with keywords with 'brew' in them"
are fast, because doing that in any schema involves a LIKE over the
keyword text, which more or less means a table scan, and the keyword table
in that design is smaller than in the second one (proportional to the
number of distinct keywords, not the sum of the number of keywords over
all documents), and much smaller than in the first one, where it's the
whole table.
But then, if you have full-text indexing, you can search the packed string
without doing a table scan. Although that's not necessarily fast enough:
http://www.pui.ch/phred/archives/2005/06/tagsystems-performance-tests.html
But then, you can full-text index the more normal schemas too:
http://www.opensymphony.com/compass/content/about.html
Er, so, yeah, anyway, glad to have entirely eliminated the OP's confusion,
i'm sure.
tom
--
Mass motoring effects an absolute triumph of bourgeois ideology on the
level of daily life. It gives and supports in everyone the illusion
that each individual can seek his or her own benefit at the expense of
everyone else. -- Andre Gorz
---910079544-1410690323-1272313580=:14878--