I'm trying to model something fairly simple, but to my shame I'm having a hard time figuring out a good design!

The situation is I have a set of web pages that can occasionally display announcements. An announcement can appear one one or many pages; also some announcements should appear on all pages (i.e. "global announcements").

Here's my first attempt at a model, a simple many-to-many relationship.



What's causing me problems is the notion of an announcement appearing on all pages - I'm not sure whether to use a "page" called "global", and have global announcements associated with that page. If I do this, I need to make sure that these announcements can't also be associated with individual "real" pages. There's nothing I can see in the design that would enforce this rule, so it doesn't feel right.

Thanks for any help -
Steve

there might be other ways to handle this, but to me the simplest is to have a column in the announcements table called isGlobal or similar

in the admin process to add an announcement, whether a global one or otherwise, the first thing we do is INSERT the announcement -- until then, we don't have a PK yet that we can reference in the page_announcement FK

so if we've just added a new global announcement, we wouldn't let the admin user even try to link it to any specific pages

Thanks for the reply - I like that idea, it's nice and simple. One issue that remains though, is that there's nothing in the data model to enforce the rule that global announcements shouldn't be associated with any pages.

I probably will not fret about that too much though, and enforce the rule through application code.

Thanks again,
Steve

there is a way, but it involves a compound PK and a CHECK constraint

application logic suffices nicely here