0

I am building a site, and among other things, I need to allow a user to create their custom landing page.

Each user will upload their own header image, create a custom title, description, and other content.

Currently, I have a table that holds all the page elements. But I want to enable users to have a draft version of the page while a published version might already exist. And while they are editing their page, I want to allow the users to preview their work (for example, clicking a "preview" button while editing).

What is the best way to achieve the above in terms of database tables?

For example, the main table containing the elements of the saved finalized and published page is something like the following:

user_page table

user_id: Integer -  foreign key to the user table
header_image: Varchar - contains the name of the image, save on S3
title: Varchar - the title of the page
description: Text - some page content

At the moment, I think that to have a preview mode, which is a non-published page, I could have another table, identical to the user_page table called for example preview_user_page.

So while the users are editing their page, I save all in preview_user_page. And when they click the preview button, I show the page with the content stored in the preview_user_page.

When the users are ready and press the publish button, I copy their content into the user_page table, providing the data for the posted finalized page.

Is there a better way?

2
  • Just to be clear, are you developing a CMS? Jan 4 at 21:26
  • No, it is not a CMS. It's just a site that allows users to have their own semi-customized single page that they can share. It has nothing to do with LinkedIn but imaging the LinkedIn users' own profile page.
    – andrea
    Jan 4 at 21:31
1

There's no need for extra tables. Just add a column that designates, if a row stores a draft or a published version. The details, like constraints or column data type(s) depend on the details you envision there -- Can there be multiple drafts, possibly named or designated by a timestamp or version number? Should an unpublished page be degraded to a draft or be delete all together? ...

For the simple case that there can maximally be one draft and of course only maximally one published page, that could be something along the lines of (assuming that the users table is called "user" and has an integer column id as its (single column) primary key):

CREATE TYPE user_page_status
            AS ENUM('draft',
                    'published');

CREATE TABLE user_page
             (user_id integer,
              header_image text,
              title text,
              description text,
              status user_page_status,
              PRIMARY KEY (user_id,
                           status),
              FOREIGN KEY (user_id)
                          REFERENCES "user"
                                     (id));

(The implicit unique constraint the primary key introduces ensures that there cannot be more than one page of a status for a user. The implicit not null constraints make sure a page belongs to a user and has a status.)

In this example you can use the state column to decide whether to get a draft -- WHERE status = 'draft' AND ... -- or a published page -- WHERE status = 'published' AND ....

And why do you think you need to persist data of a preview in the database (again)? If you want to show the preview of an existing draft or a published page, that data already is in the database. Get it and parse it. If you want to show a preview of a not yet saved draft, WYSIWYG-style, you already have the data in your application's memory, just parse that. There's no need to first write it to the database just to retrieve it again immediately afterwards and then promptly delete it in the database.

12
  • If I don't duplicate the content into another table, while users are editing their page the content will reflect in the live published page.
    – andrea
    Jan 4 at 21:33
  • @andrea: I don't understand. In your question you wrote that you want to give the users the possibility to save drafts, now you write you want to let them only directly change the single live page? Then neither more tables nor more rows are needed. Update the existing row in the existing table.
    – sticky bit
    Jan 4 at 21:39
  • I don't think I wrote two different things. Let me try again. I cannot store the data into the application memory, which I assume you might mean the session of the currently logged-in user, because if they log out they lose all their work. Imaging there is already a published page that the user has created previously. Now a month later they want to make changes to that page. While they make the changes they want to preview their work without affecting their currently published page.
    – andrea
    Jan 4 at 21:48
  • @andrea: (1) There is a current page that is displayed when the user's profile is asked for or whatever the trigger is to show it, and that is created with the values currently stored in the database, right? (2) If a user wants to edit the page, the application loads the values from the database and provides the user with some means, say a form, to edit the values, right? (3) They can then save their changes which will cause the values to be stored in the database again, right? So between (2) and (3) the values are in the application's memory.
    – sticky bit
    Jan 4 at 21:54
  • 1
    @j-d Thank you, yes, I understand that a copy of the data is necessary but not a duplicate table. My question was just about where to save this duplicate data. I gave my thoughts and asked if there was a better way. I am grateful to @sticky-bit; the example he added to his answer showed me the better way.
    – andrea
    Jan 6 at 0:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.