Hi there,
I'm building a CMS for my own needs, and finished planning my database layout.
Basically I am abstracting all possible data-models into "sections" and all entries into one table. The final layout is as follows:
Database diagram: I have yet to be allowed to post images, so here is a link to a diagram of my database.
Entries (section_entries) are children of their section (sections). I save all edits to the entries in a new revision (section_entries_revisions), and also track revisions on the sections (section_revisions), in order to match the values of a revision, to the fields of the section that existed when the entry-revision was made. The section-revisions can have a number of fields (section_revision_fields) that define the attributes of entries in the section. There is a many-to-many relationship between the fields (section_revision_fields) and the entry-revisions (section_entry_revisions), that stores the values of the attributes defined by the section revision.
Feel free to ask questions if the diagram is confusing.
Now, this is the most complex SQL I've ever worked with, and the task of fetching my data is a little daunting.
Basically what i want help with, is fetching an entry, when the only known variables are; section_id, section_entry_id. The query should fetch the most recent revision of that entry, and the section_revision model corresponding to section_revision_id in the section_entry_revisions table. It should also fetch the values of the fields in the section-revision.
I was hoping for a query result, where there would be as many rows as fields in the section. Each row would contain the information of the entry and the section, and then information for one of the fields (e.g. each row corresponding to a field and it's value).
I tried to explain the best I could. Again, feel free to ask questions if my description somehow lacking.
I hope someone is up for the challenge. Best regards. :-)
