0

I am creating a CMS where all pages for our website can be added/updated/deleted/re-positioned etc. I am creating a oneToMany relationship between pages and page_versions so each time a page is amended in the CMS a copy will be saved as a page_version so we can revert back to this version if needed. Also, when someone is currently editing a page, it will become locked so no other user can edit it at the same time. Each page can also have a parent page and each page can have multiple rewrite rules using another oneToMany relationship with the rewrite_rules table. However, there are a few fields which I am unsure as to what table they should be in and how they can be used as objects when mapping using Symfony2 and Doctrine entity relationships. Here are my tables at the moment:

page table

id
parent_id
locked

page_version table
page_id
title
content
enabled
position

rewrite_rules table
id
page_id
rewrite
canonical

My questions are:

  1. Should the position field be within the page table or page_version table? The position for each page will not change depending on the version of the page. This will only be changed in the list view of all pages. This is so that if you have for example,5 child pages of the 'About Us' page, these can be ordered for rendering the output on the front end.
  2. Should the rewrite_rules table join the page table or page_version table? Again, this will be linked to a page rather than the version. If someone edits a version of a page and adds a rewrite rule, this will be applied to the page, not just that version of the page. I.e if you were to revert back to an older version, the rewrite rule would still apply to this version.

  3. To make a page version the active page, should this be a simple field in the page table or in the page_version table?

    Thanks

    UPDATE

Here are my Page and PageVersion classes:

class Page
{
    /**
    * @ORM\OneToMany(targetEntity="PageVersion", mappedBy="page")
    */
   private $pageversions;
}

class PageVersion
{
    /**
     * @var page
     *
     * @ORM\ManyToOne(targetEntity="Page", inversedBy="pageversions")
     *
     */
    private $page;

}
user1961082
  • 1,015
  • 17
  • 41

1 Answers1

1

Consider that versioning associations becomes really annoying on the long term. Also, you should probably remove OneToMany relations and keep only the ManyToOne side of them.

This makes it easier to version your data by using a tool like EntityAudit, which unfortunately didn't work in my case (doesn't support inheritances).

So my advice is:

  1. if it's not relevant to version a property, then don't do it. Versioning is a complex problem, and an expensive one too. YAGNI.
  2. if you want to track if a page had rewrite rules or not in the past, you will need to version the association, but since the rewrite rule doesn't have effects per-version, YAGNI. In my opinion you may just connect a page with a rewrite (also here: keep the association uni-directional if possible).
  3. You will probably need a reference to the "active" page in the "page" object. This speeds up queries quite a bit, plus makes it easier for you to work with the OOP API.
Ocramius
  • 25,171
  • 7
  • 103
  • 107
  • Thanks @Ocramius. 1. How do you mean remove OneTOMany but keep ManyToOne? 2. Yes I agree that there is no need to version rewrites or positioning. 3. Again, I agree thanks – user1961082 Feb 08 '13 at 23:58
  • @user1961082 you can define unidirectional associations. Basically, you avoid defining the side with the collection where possible. – Ocramius Feb 09 '13 at 01:34
  • Thanks again. Coming back to the "active" page suggestion. Would you have active_page_id in the Page entity or have an active field in PageVersion? We will also have the ability to save a page version as a draft so this will need considering also. – user1961082 Feb 09 '13 at 08:05
  • Also, coming back to removing the oneToMany what about when I want to list all page versions for a specific page so a user can vervet back to one? – user1961082 Feb 09 '13 at 08:40
  • Drafts would simply be versions of the page that weren't set as "active". As long as `Page#activePage` is not changed, you can add as many drafts as you want. The `active` field in the `PageVersion` may help if you have pages that are published as `hidden` or that generally have to be removed from publication (may also be `null` for `Page#activePage`). To list specific page versions, you can always use DQL. – Ocramius Feb 09 '13 at 15:46
  • Thanks! The issue I'm having at the moment is listing all pages in the CMS but showing the page title for the active version for each page. The query Im using (with active in the page_version table) is `SELECT p,pv FROM XYZWebsiteBundle:Page p JOIN p.pageversions pv WHERE pv.active = 1` Ive also updated my original question with my two classes. Do you suggest removing the oneToMany `$pageversions` property from the Page class? – user1961082 Feb 09 '13 at 16:19
  • Yes, I wouldn't keep a reference to all versions from the `Page` class (performance and SOC reasons). You'd probably want to only keep a reference to the active version and proxy method calls from the `Page` object to the selected `PageVersion` object. – Ocramius Feb 09 '13 at 16:24
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/24239/discussion-between-user1961082-and-ocramius) – user1961082 Feb 09 '13 at 16:29
  • So if I create an `$activeVersion` property within the Page you said this would be a ManyToOne relationship, is that right? Wouldn't it be one active version has one page version? – user1961082 Feb 09 '13 at 16:58
  • No, it is not a `OneToOne`. `Page#activeVersion` is a `@ManyToOne(targetEntity="PageVersion")` – Ocramius Feb 09 '13 at 17:04
  • Thanks. Can you please explain why it is ManyToOne and not OneToOne? – user1961082 Feb 09 '13 at 17:10
  • 1
    Avoid `@OneToOne` where possible: it causes weird lazy loading issues if you access the non-owning side of a relation, and it enforces unique keys. Use `@ManyToOne` if it is strict enough for your use case :) – Ocramius Feb 09 '13 at 19:30
  • Ok thanks. I've got it working now using OneToMany as you suggested. The only problem I've got is when you create a page and you choose to save the version as a draft rather than set as active. I can't then use the $activeVersion property to pull out the page title when I list all pages in the CMS. Any suggestions? – user1961082 Feb 09 '13 at 19:43
  • 1
    You probably want to show both drafts and active versions at the same time somehow (in backend operations). In frontend, only the active version counts. – Ocramius Feb 09 '13 at 20:35
  • Possibly add `Page#draftVersion` in the same way as I have `Page#activeVersion` because you would only have one draft wouldn't you? Then I can check if activeVersion is null then load draftVersion (in backend only) – user1961082 Feb 09 '13 at 21:53
  • In backend you need latest versions anyway. – Ocramius Feb 09 '13 at 22:23
  • Sorry what do you mean "latest" versions? – user1961082 Feb 10 '13 at 01:03
  • "latest" as in "latest draft" (newest one, regardless if published or not) – Ocramius Feb 10 '13 at 01:46
  • So when editing a page in the backend you would load the latest version no matter if published or not and then the option to revert to previous versions? Therefore, no need for a specific draft identifier? – user1961082 Feb 10 '13 at 02:02
  • Right. That's achieved with something like http://stackoverflow.com/questions/2111384/sql-join-selecting-the-last-records-in-a-one-to-many-relationship – Ocramius Feb 10 '13 at 02:12
  • Ok so I'd use this query to list all pages and get the latest page but use the Page#activeVersion property when I want to get the latest version only on the front end? I was currently looping through all Page entities then calling Page->getActiveVersion()->getTitle() to print each page title but this seems to run an additional query for each page row. – user1961082 Feb 10 '13 at 10:23