Designing a Callouts System and Connecting it to Pages

acmeinteractive

I'm developing a CMS for a website that has editable pages and separate editable callouts. Since a callout can appear on multiple pages, I need a way to keep track of what pages they are linked to. At the moment I have this set up:

a "pages" table and "sections" table both of which can have callouts
"callouts" table
"callout_connector" table with id, page_id, callout_id, and type

On the admin page to edit the pages, i have a checkbox list of the callouts. This seemed to be a good idea but then I started thinking about the complications of updating or adding new pages...

If I simply delete the rows in the "callout_connector" that pertain to the page every time you edit it and then add new rows, the auto-increment id could potentially get huge.

So now I'm thinking about adding a "visible" field to "callout_connector" and creating an entry for every page/callout combination so the only thing that needs to be updated is the visibility. But this gets a little bit complicated because every time the user creates a new page, i'll have to add that page and all of the potential callouts to the table. And vice versa if a new callout is created.

Anyone have any ideas about the best ways to set something like this up?

Thanks!

acmeinteractive

What if I just add a field to the pages table that contains a comma delineated list of the callouts i.e. "1,3,7"

Then there's nothing to update if a new page or callout is added. Are there any downsides to doing that?

Weedpacket

You have three tables. One for pages, one for callouts, and a third that has pairs of (page/callout) IDs - if there is an entry in that table then the callout with that ID is on the page with that ID.

Read up on database normalisation.

acmeinteractive

That's what I had. But every time you update which callouts below with which pages you would have to go through the third table and remove rows where the callout has been disabled, leave rows where they are the same and add new rows where new callouts have been enabled. Or you would have to delete all of the rows associated with the page and re-add them based on the new list of selected callouts.

I used the comma list that I was talking about before and it is much easier. Only one string needs to be updated and it can be easily utilized on the front end when it needs to find the callouts to display. It's kind of like tagging the pages but using the callout IDs instead of words. Since those ID's are generated from a checklist of callouts in the admin section, the user never has to actually input the numbers either. I think this is more practical for my application.