If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > Intriguing mysql table problem - what's most professional?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 12-15-04, 06:57
sebseb sebseb is offline
Registered User
 
Join Date: Dec 2004
Posts: 1
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
Reply With Quote
  #2 (permalink)  
Old 12-15-04, 08:08
r937 r937 is offline
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
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"

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On