Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2011
    Posts
    3

    Unanswered: Help Designing Relationship Database

    Hi

    I am trying to design an article website and have got a mental block over the best way to design the database, I have attached a screenshot of the current design (please ignore the column types), I will try to best explain the problem below, please let me know if you need any additional information

    An article can be in any number of categories (article_to_category table) all the articles have set values which are stored in the article_content. Depending on the type of the article it can have additional attributes which are stored in the report, article, event, viewpoint tables.

    The problem is there is not direct link between the article_content table and the attributes in the above tables, I would have to union all the tables together and workout which of the values I need to display.

    I have looked at a EAV model and feel it's not the correct way to go. I have thought about combining the report, article, event, viewpoint tables into the article_content and making the column NULL but this feels 'wrong'.

    Thanks for your advice, please let me know if you need any further explanation

    Regards
    Attached Thumbnails Attached Thumbnails database_design v1.png  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kirk112 View Post
    The problem is there is not direct link between the article_content table and the attributes in the above tables...
    there is, actually -- article_content_id in each of those tables is a foreign key back to the article_content table

    but a UNION isn't right, you'd want to use LEFT OUTER JOINs instead, one for each of the subsidiary tables, which will work nicely because only one of the joins will return anything for each given article

    Quote Originally Posted by kirk112 View Post
    I have looked at a EAV model and feel it's not the correct way to go.
    and you are right

    Quote Originally Posted by kirk112 View Post
    I have thought about combining the report, article, event, viewpoint tables into the article_content and making the column NULL but this feels 'wrong'.
    that's actually quite workable, although you should stick to the separate subtables for the time being
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2011
    Posts
    3
    Hi

    thanks for the reply,

    there is, actually -- article_content_id in each of those tables is a foreign key back to the article_content table
    The problem is if I select an article direct for the article_content table I don't know which of the report, article, event, viewpoint tables to join it to.

    ummm, are you saying if I do a LEFT OUTER JOIN on all the tables that it will find a match and just return the columns from the table with the correct article_content_id?

    that's actually quite workable, although you should stick to the separate subtables for the time being
    the phase 'time being' worries me can you see any pitfalls in the current way I have designed it, really don't want to have to change the structure in 3 months time.

    Again thanks for you help!!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, multiple LEFT OUTER JOINs, as only one of them will return anything

    for the time being means that you're on the right track, and the only thing that will change your mind to a single monolithic article_content table with lots of nulls in each row is if you get tired of coding all your application scripts to handle the different subtype tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2011
    Posts
    3
    Thanks for the advice really appreciate it been agreeing and disagreeing with myself for days over this - good to get someone else knowledge and opinion.

    Cheers

Posting Permissions

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