Hi.
I'm thinking about a possible database schema for
a content management system that is supposed to use
MySQL as a backend. The system stores multiple
versions for each piece of content.
Content is referenced by a content-id (cid), each version
gets a version-id (vid). Each version has some meta-data,
in the following example only one field "meta_example". I
want to filter by this metadata.
Also, each version is only "live" (available online) for
a given period of time. If multiple versions of one piece
of content are live at the same time, the latest one should
be used (the one with the higher live_from value).
I have created a table called "versions":
vid int auto_increment # the version-id
cid int # the content-id
live_from date # when this version goes live
live_to date # when this version becomes obsolete
meta_example bool # meta-data about this version.
Consider the following table-content, where is_live means that
live_from<now() and live_to>now()
vid cid is_live meta_example
1 1 TRUE TRUE
2 1 FALSE FALSE
Then
select * from versions where cid=1 and live_from<now() and live_to>now() order by
live_from desc limit 1;
gives me the current version of the content-piece 1, if there is any.
*** But the problem is listing all content-pieces, that match a given meta-tag in
their current version.
I want to do a:
select * from versions where meta_example=TRUE
and "version is the current version of the file"
*** Or even easier: to list all content-pieces, that are currently live
select * from versions where "version is the current version of the file"
But I simply can't find a (good) query without using a subselect. The problem
is that I have to check, wheter the live_from value ist the maximum for all
the versions of this content-piece. Joining the table with itself doesn't
seem to bring me nearer to a solution. :-(
I could solve the second problem using the
"group-and-concatenate-the-interesting-pieces"
trick, but that is too slow.
Can anyone help me out?
bye, Paul.
--
Paul Mallach
ARIVA.DE AG
Ostseekai 2
D - 24103 Kiel
Tel.: +(49) 0431/97108-24 E-Mail: [EMAIL PROTECTED]
Fax: +(49) 0431/97108-29 Internet: www.ariva.de
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
