Intriguing mysql table problem - what's most professional?

[aqw]

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

[onion2k]

I'd go with the first option. Using two tables where one would do the job perfectly adequately is silly.

I have loads and loads and loads of PHP experience. (Why do you want to know?)

[AstroTeg]

Originally posted by aqw


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).

Unions aren't evil. How you use them can lead them to being evil. (I find them very handy for dedupping or merging data.) Yes, when you do s, the column order gets messed up, but using is a bad practice in general (what if you add a column to the table in a different order? you'd have to update chunk of code associated with the query you did using *).

Option #1 looks good. You have all your jokes in one table and a second table to describe their status. This goes nicely with the rule, "all like data stays together."

My only concern is your implementation of your approved joke status and updated joke process. I may not have understood fully what you plan to do. But after rereading a few times, it appears sound. When a joke gets updated and approved, what will happen to the old approved joke? I assume you'll have some code do house cleaning.

[aqw]

Thank you both for answering... it really lit up my mind!

AstroTeg, you wrote:

When a joke gets updated and approved, what will happen to the old approved joke? I assume you'll have some code do house cleaning.

In fact, when a joke gets updated and approved, the old joke gets updated and the unapproved one deleted.

There is no cleaning up whatsoever.