Should I use AI on these fields in MySQL?

Question

I have this db below. I wonder how I should use the ID to identify each record. Everything is connected from the classified_table! Two Questions:

Should I use AI on every PK in this case?

Could somebody give me the FULL code for selecting an entire classified from only an ad_id ("bmw_330ci_8939483" for example)?

I am new to normalized db and making a good database work, so detailed instructions is very much appreciated... Also if you notice any 'wrongs' in this db please let me know.

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:
classified_id (PK)
ad_id (VARCHAR) something like "Bmw330ci_28238239832" which will appear in URL
poster_id (FK)
cat_id (FK)
area_id (FK)
headline
description
price
etc....

posters table:
poster_id (PK)
name 
email
tel
password

area table:
area_id (PK)
area
community

Thanks

Answer 1

I would auto-increment (AI) on fields that I would do majority of searching by. AI makes it easier to return results, but there are performance issues where it can slow down the database.

In regards to the query, I am not exactly sure what you would want to return, but this query returns the classified_id by the given ad_id

SELECT classified_id FROM classifieds_table WHERE ad_id = "bmw_330ci_8939483" 

To perform a single insert into your classifieds table and column ad id the value audi a4 would be:

INSERT INTO classifieds_table ad_id VALUES "audi_a4"

Or multiple inserts using the same table, multiple fields and multiple values would be:

INSERT INTO classifieds_table (ad_id, poster_id) VALUES ("audi_a4", 10)

Notice I left out classified_id because if you choose to auto-increment it will automatically assign a value without you explicitly assigning one.

Check out MySQL :: Building a Database-Driven Website using PHP and MySQL for more tutorials.

Answer 2

Using auto-increment for your PKs sounds sensible, because it sounds like you already want to use a surrogate key and auto-increment makes the inserts very straightforward. Worth taking a look at this discussion about how to pragmatically choose what primary key to use.