Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009
    Posts
    2

    Data Modeling Problem

    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.

    Click image for larger version. 

Name:	announcements_data_model_manytomany.png 
Views:	57 
Size:	17.9 KB 
ID:	9317

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2009
    Posts
    2
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there is a way, but it involves a compound PK and a CHECK constraint

    application logic suffices nicely here

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •