Mysql table design

Peter B

I have a table for advertisments on my site.

There are 2 types of users who can place ads.

1. Not logged in person
2. Logged in person

I'm trying to decide on the correct way to set up my advertisments table and how it should (ideally connect with the other tables).

I'm trying to create my database in 3rd normal form however this is a bit challenging for me at the moment.

AD_table
AD_ID
User_comment
price
user_id


Users_table
user_id
user_comment

The logged in users will have a preset users comment but the non-logged in persons will not so they must insert one when creating the ad.

could someone tell me the correct way of storing / calling the information from ads according to third normal form.

I would have gone for the sql statment

select *, (select users.user_comment from users where Users_table.user_id = AD_table.user_id) as logged_in_user_comment from AD_table

In my php I check is the "logged_in_user_comment" equal to "" (empty). If it is I user the user_comment from AD_table otherwise I use the "logged_in_user_comment".

My actual situation is a lot more complicated than this but I'm using this to get some sort of idea.

Thanks in advance

Peter

ceejay

From what I remember of the 3rd normal form, your problem boils down to whether the user_comment fields are actually the same piece of information. If they don't have to be, then there isn't really a problem. So, if you renamed the field in your user table to default_user_comment, then perhaps that might make it clearer. You can then use something like this:

SELECT AD_table.AD_ID, AD_table.user_comment, AD_table.price, AD_table.user_id, Users_table.default_user_comment FROM AD_table LEFT JOIN Users_table ON AD_table.user_id = Users_table.user_id

The LEFT JOIN means that you should get all the records from AD_table whether or not you get a matching entry in the user table. You can check the value of the AD_table.user_comment field to see if it's blank, and if so, you can use Users_table.default_user_comment. Where there's no matching user then Users_table.default_user_comment should be NULL.

HTH

Ciarán.