Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2004
    Posts
    1

    Unanswered: Intriguing mysql table problem - what's most professional?

    Mysql table building choice problem driving me mad.

    It's very well explained so please try to help me out.

    Here's the situation:

    I am building a jokes directory where users can submit jokes that are validated by the administrator.
    There are 2 situations:
    1/ When a new joke is submitted by a user, it's added to the database and it's status is "not validated".
    2/ If a user already has a VALIDATED joke and updates it, the old one will remain validated and a new joke entry will be created that will substitute the old one once it gets validated (the user can also update the pending joke - anyway there can only be 1 pending joke).

    So here's the issue:

    I have 2 database possibilities of solving this problem:

    1/First solution: Have just one table named jokes: (just useful columns)

    joke_id INT
    is_joke_approved INT NOT NULL
    associated_joke_id INT NULL
    ...

    If a new joke is added, it's added like this:
    is_joke_approved=0, associated_joke_id=NULL

    If an already validated joke is updated, a new entry in the jokes table is added:
    is_joke_approved=0, associated_joke_id=old_joke_id


    2/Second solution: Have two tables named jokes and unapp_jokes: (just useful columns)

    jokes:
    joke_id INT
    associated_joke_id INT NULL
    ...

    unapp_jokes:
    unapp_joke_id INT
    associated_joke_id INT NULL
    ...

    If a new joke is added, it's added like this: (in unapp_jokes)
    associated_joke_id=NULL

    If an already validated joke is updated, a new entry in the unapp_jokes table is added:
    associated_joke_id=old_joke_id


    Pros and cons of the 2 options:

    In the admin panel, the joke directory will display (on a same page) already validated jokes (with or without an associated unvalidated joke) and not yet validated jokes without an associated validated joke (that is completely logical).

    If I were to choose the first solution, the corresponding sql query would be:

    select *
    from jokes approved_jokes
    left join jokes unapproved_jokes
    on approved_jokes.associated_joke_id = unapproved_jokes.joke_id
    where approved_jokes.is_joke_approved = 1
    or (approved_jokes.is_joke_approved = 0 and approved_jokes.associated_joke_id=NULL)


    If I were to choose the second solution, the corresponding sql query would be:

    (select *
    from jokes
    left join unapp_jokes
    on jokes.associated_joke_id = unapp_jokes.associated_joke_id)
    UNION
    (select *
    from unapproved_jokes
    where associated_joke_id=NULL)


    At first, at least to me, the first solution seems better as there is no union (unions are evil especially when you do *'s as they fail if you change the column order in the tables).

    Moreover, with the first solution, the joke_id's are all unique and hence when you have a page, let's say delete_joke.php, one only needs to do delete_joke.php?joke_id=6. With the second solution, the delete_joke.php script must handle both delete_joke.php?joke_id=7 and delete_joke.php?unapp_joke_id=56!!!
    What's your opinion on that? What's the most professional?

    BUT, one could also say that the second solution is better because it seperates the unapproved jokes from the approved ones neatly. What's your opinion?


    Which one of these tho "building conventions" should I choose?
    Please indicate how much experience you have in PHP programming.

    Thank you very much you life-savers

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which solution is more "professional"? based on what i've seen created by people who were paid to build tables, a professional solution isn't necessarily a good one

    which one is easier? the single table solution

    your sql for the first solution is wrong, there is only one table, so there's no join

    also, your reason for splitting the table into two, "because it seperates the unapproved jokes from the approved ones neatly," is not a good enough reason

    you could just as easily say you should split an employee table into several tables for people who make different salaries, "because it separates the high salary employees from the low salary ones neatly"

    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
  •