mySQL database logic for PHP connectivity

I'm working on making a dynamic list of reps, and I'm having trouble figuring out a good database structure.

Here is what I need to be able to do:
Pull down menu with states (AZ, CA, Etc)
When state is selected the appropriate Rep info is shown the the user


ie if CA is selected, then this is shown:

Rep Name
222 Test Drive
Santa Barbara, CA 93150
Tel. 555-559-9996

Now for the tricky part. Some reps will be used on multiple states, and some states will have multiple Reps.

What I have so far:

Table - Reps
id
name
address
tel
fax

Table - State
state
rep

==> I was planning on storing the Rep $id into the $rep field on the State table, but what if there are more than one rep?

I'm not sure about how to put more than 1 rep in per state...

Am I attempting to do this correctly, or does anyone have a better suggestion?

If you had more than one rep you'd just put another row in the table...

rep | state
23 | GA
24 | TN
25 | MN
26 | GA

Then when you display the reps just have it pull all reps that are in state GA, right? Then it would display reps 23 and 26 in this example, and pull their contact info from the other table.

And you'll probably want to make fields for first and last name, and each part of the address as well, so you can sort by first/last name, and city or zip code.

Just a quick mock up of what I think you are doing..... Deker is right, you need to break the info into as many tiny bits as possible. Name should be first and last. Numbers should be area, prefix, number, addresses should be broken up into street, city, postal, state/province, etc.... and you need a "key" that uniquely identifies every record in every table. The easiest and most used method is a simple "ID" number. You can also combine 2 or more columns as unique id's into composite keys, but only when you need to.

[php]
CREATE TABLE reps (
rep_id SMALL INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
rep_fname VARCHAR (50),
rep_lname VARCHAR (50)
);


CREATE TABLE phone_nums (
phone_num_id rep_id SMALL INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
phone_num_area VARCHAR (3),
phone_num_prefix VARCHAR (3),
phone_num VARCHAR (4)
);


CREATE TABLE addresses (
address_id rep_id SMALL INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
address_street VARCHAR (100),
address_city VARCHAR (75),
address_state VARCHAR (75),
address_zip VARCHAR (10)
);


CREATE TABLE states (
state_id rep_id SMALL INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
state_name VARCHAR (25),
state_init VARCHAR (2)
);


CREATE TABLE rep_state (
rep_state_id rep_id SMALL INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
rep_id,
state_id
);
[/php]

In this last table rep_id and state_id would be foreign keys to table rep_state, but I am not sure how well or if mySQL supports foreign keys. So you might have to insure that integrety using php. In good relational database design, when you have a many:many setup, ie "a rep can have many states, a state can have many reps" it's better to abstract that relationship into a new table whose sole purpose is to bind those 2 relationships. Using JOIN or types of SELECT to bind the info from those 2 tables into 1 record set using that extra table.

Wow you guys rock! Thanks so much. That makes a whole lot more sense to break it all up.