The use of Primary Keys and Foreign Keys is to insure data integrity. For instance, by creating a Foreign Key in the login table you insure that all client_id's point to a valid info_id in the clients table.

To make the tables interact with each other, you would create a JOIN statement.

SELECT *
FROM clients JOIN login
ON login.client_id = clients.info_id

I've been Googling

Try database normalization or similar.

The idea is to reduce the redundant data stored in multiple tables, make selects faster, reduce table size, and make programming for your database more simple and flexible.

auto_id¦unique_id¦fname¦lname¦state(char, 2 character abbreviation)

In the above example, you may have many other uses for "state." Examples are ship-to state, a contact's state related to this record, or similar. So you would do something like

auto_id¦unique_id¦fname¦lname¦state_id(int or tinyint)

auto_id¦state_id¦abbreviation¦full_text_state

You would get any of the state textual values by doing a join on the states table as described. The "state_id" field in the member table is now a numeric type, which is faster and makes for a smaller table.

There are programming advantages too. To generate a state drop down list, you'd only query the states table. This would allow your administrative interface to add or remove values as required, updating all resources that reference "state."

Another is that instead of a limited set of "options," they can be infinite. Instead of

auto_id¦prod_id¦title¦option_1¦option_2¦option_3

you would do

auto_id¦prod_id¦title

then in an option table,

auto_id¦prod_id¦option_id¦option_title¦option_value

joining the options to the product on prod_id. You can now have as many options as you want for a given product.

In your case, one approach would be

+------------------------------------------------------------------------+ 
¦ clients ¦ 
+------------------------------------------------------------------------+ 
¦ info_id ¦ first_name ¦ last_name ¦ email ¦ website_name ¦ website_url ¦ 
+------------------------------------------------------------------------+ 

+--------------------------------------------------------------------------+ 
¦ login ¦ 
+--------------------------------------------------------------------------+ 
¦ client_id ¦ username ¦ password ¦ regdate ¦ email ¦ website ¦ last_login ¦ 

When you create a record, you need to do something like this:

- insert data into clients table
- get the last inserted id.* I'm presuming this is info_id, there is a pre-programmed function to get the last inserted id in the auto increment field. If it's not PHP, you'd do "select info_id from clients order by id limit 1." Same result.
- using this id, insert that into client_id (which you may not be able to in some configurations, the auto increment field is not "writable, see *.")

*A bad habit to form is reliance on the auto increment field for relational joins. Lets say you start a record set, then delete some entries, you have

1¦...
2¦...
5¦...
7¦...

For whatever reason, you move the database, export it somewhere, whatever - the auto increment field will set those numbers sequentially,

1¦...
2¦...
3¦...
4¦...

and none of your joins will connect with the right records. So a better solution is to always make your joins on a unique field. If it ever moves, it will remain connected:

1¦1...
2¦2...
5¦5...
7¦7...

1¦1...
2¦2...
3¦5...
4¦7...

So a choice I would make is

+------------------------------------------------------------------------------------+ 
¦ clients ¦ 
+------------------------------------------------------------------------------------+ 
¦ rec_id ¦ client_id ¦ first_name ¦ last_name ¦ website_name ¦ website_url ¦ 
+------------------------------------------------------------------------------------+ 

+----------------------------------------------------------------------------------------+ 
¦ login ¦ 
+----------------------------------------------------------------------------------------+ 
¦ rec_id ¦ client_id ¦ username ¦ password ¦ regdate ¦ email ¦ website ¦ last_login ¦ 

Note I've eliminated email from the client table (see "redundant data.") You will likely use it for login, so left it there - whatever works best for you. The fundamental idea of normalization is to eliminate storing (and updating, and maintaining) redundant data across tables.

select * from clients,login where clients.client_id=login.clients_id and login.email='$input['email_address']';

or,

select * from clients where login.email='$input['email_address']' left join login on clients.client_id=login.clients_id;

[edited by: rocknbil at 7:38 pm (utc) on Nov. 8, 2009]

SELECT *
FROM clients JOIN login
ON login.client_id = clients.info_id

Thanks syber. I'll give it a try next time I'm in there.
----
rocknbill, good stuff. Appreciate the lesson and the Google search term. :)