My final mysql db, could someone check if the tables are correctly made?

Question

I have these tables:

category table:
cat_id (PK)
cat_name

category_options table:
option_id (PK)
cat_id (FK)
option_name

option_values table:
value_id (PK)
option_id (FK)
value

classifieds table:
ad_id (PK) (VARCHAR) something like "Bmw330ci_28238239832"
poster_id (FK)
cat_id (FK)
headline
description
price
etc....

posters table:
poster_id (PK)
name 
email
tel
password
etc....

Three main questions:
1- Is the above good enough? It covers all my needs atleast...

2- Sometimes when I try out different queries, I get strange results... Could you write a PHP query string which will fetch one complete ad from an ad_id only? (imagine the only variable you have is ad_id)

3- In the query string, must I specify all different tables which are connected in order to display an ad? Can't I just use something like "SELECT * FROM classifieds WHERE ad_id=$ad_id" and it would handle the links automatically, ie fetch all related information also?

Thanks and if you need more input let me know!

Answer 1

1) If it meets your needs, then wouldn't that make it "good enough"? But seriously, I would agree with davek that you should make the ad_id field an int/bigint, and I'd also suggest the same for the posters table. Make the name a regular value field and create an autonum int/bigint PK field for it. If for any reason that user wants to change their name (for privacy concerns, perhaps), then you would have to update any foreign keys in the database as well. With an autonum key, you wouldn't have this problem.

2) Yes, from what I see you should be able to gather all the data on an ad by knowing only the ad_id.

3) No, you need to do more than that, either equi-join in a SELECT query, or use the JOIN keyword to pull your data in. MySQL doesn't have a "meta" relationship model (like MS Access), so it won't automatically understand your primary/foreign key relationships.

Answer 2

You have serious design problems. Never ever ever use name as a PK; it is not unique and it is subject to change! Women change thier names when they get married for instance. In fact, don't use any varchars as PKS at all. Use surrogate keys instead. Surrogate keys don't change, text keys values often do and they are slower too.

And never store name as just one field, this is a poor practice. At a minumum you need first name, last name, middle name, and suffix. You wil also need a autoincrementing id field so that John Smith at one address in Chicago can exist in the table with a different John Smith who lives elsewhere in Chicago.

No you can't get all the data from related tables without adding them to the query through the use of a join. This is database 101 and if you don't know that, then you don't understand relational databases enough to design one. Do some research into joins and querying. You can get all the information for an ad from just having the ad id though as your current relations appear to work.

Do not use implied joins when you add the other tables to your queries. They are outdated by 18 years. Learn correctly by using explicit joins.