Super-basic concept overview of linking tables

Fife_Club

I know nobody here has the time to teach me everything about this but can somebody give a general outline of how you use multiple tables in mySQL?

I use mySQL with my php web pages. I've made my own table before (singular) and the php code necessary to add/update/and querry the data. So I have the most basic of basic understanding of how tables work and doing how to access the database. But as I said, it was only one single table. I don't understand the general thought process of using multiple tables (which I suppose is the very point of using a relational database).

So lets make up a simple ficticious example. Say I have a database of people in one table. Now if I understand correctly, a good use of multiple tables would be to make a separate table for all the states (50 US plus DC). How do I "connect" somebody's data in the first table to say what state they live in from the second table. I read that combining tables is primarily done in the querry process but that doesn't seem 100% right to me. Something must be in the first table to say "he lives in the state in table2 with the unique ID of 12" (or whatever).

Can somebody please help me out with the basic concept side of this. Then maybe I'll be able to head in the right direction to learn more

zone16

2 tables:

tbl_people

id name state_id
1 name1 1
2 name2 2
3 name3 1

tbl_state

state_id state_name
1 alabama
2 california

if you want to have all the people that live in california:

select p.name
from tbl_people p,tbl_state s
where p.state_id=s.state_id
and s.state_name="california"

that is the normalised way of doing things. If you want, you can just have the state_name in the tbl_people rather that the state_id...

hope it helps,

zone16

Fife_Club

Yes. That does help. So for the database structure, just have the uniqueID field name of the second table (forgive me if I used the wrong term since I'm just beginning to learn mySQL) match one of the field names from the first table... and from that point on it is just done with the query language. Cool. If I got that concept correct that does help me out so I can try it out. Thanks!!