how to relate two tables within a database?

sarmenhb · 02-02-2008, 09:47 PM

if i have 2 tables

1) user logins
2) user attributes, what was uploaded, etc.

how do i relate the two?

is it by the index that is incremented(if i do add it of course)

xenon · 02-02-2008, 10:19 PM

By using indexes. To be more specific, the user logins table perhaps looks like this:

Code:

- id (auto increment, primary)
- username
- password

and the user attributes table looks like this:

Code:

- attribute_id (auto increment, primary)
- user id (related to the user id in the previous table - index key)
- phone no
- email, etc...

So, when you add a new row in the user attributes table, you use the user id in the users table, thus relating the 2 tables by the user id. Then you can use a query like this to fetch the username and the details for a given user id:

Code:

SELECT u.username, a.phone_no, a.email FROM users u LEFT JOIN user_attributes a ON(a.user_id=u.id) WHERE u.id=12

Of course, you may keep the user id (u.id) somewhere in a session or a cookie or something like that.