what's the best relationship method for a user table with app tables

[sniperman]

I wanted to know your thoughts on information architecture for MySQL when it comes to a username and password table.

The basic (and common) premise is that a user will need to enter a username and password to access their in-app details. I will need a "users" table for this with two fields "username" and "password" I assume. This is one table.

A second table will deal with in-app objects the user owns (object-oriented tables).

However, unlike in MS Access where you can create a LOOKUP field and therefore duplicate data and make connections, what is the best method to relate user data to user's app data?

[Nile]

Give each user an ID (using AUTO_INCREMENT) and in the user's app data put the id under a field called user_id

[sniperman]

that seems like the most logical way, but from a developer point of view, the more object-orientated tables linked to, the more duplication across tables we create.

is it inevitable that in relational databases we create these duplicate entries. The reason I say this is because when I pull these variables out of a database and say, into a JavaScript array (JSON), the database will be fashioned in such a way that extra duplicate variables will have to be accounted for?

[sniperman]

i suppose my ultimate point is, with many relational tables, how does a coder avoid creating duplicated values via a server-side process and let the database engineer those links?

i.e.

"User" table has "username" "password" "ID"
"User_Objects" table has "user_id" and "objects"

If I passed these values via PHP to MySQL I think in some cases where AUTO INCREMENT is not applicable, I would need to pass the "username" variable to both "username" and "user_id" to meet the criteria of some relational databases. I think, not too sure?

[Nile]

When would auto increment not be applicable?

[james438]

Auto increment is not applicable when it does not operate the way the user wants. I, actually, am moving away from using auto increment, because it does not allow for the usage of row# 0. Also, if row 4 is deleted from a table with rows 1,2,3,4,5 and another row is created the table will have rows 1,2,3,5,6. If row 5 were to be deleted instead, then you would have 1,2,3,4,6. See the problem?

PHP can solve this problem (except for the row 0 problem), but that makes the usage of auto_increment moot.

There are 2 threads of the same name. Either they should be merged, or one should be deleted.

You can make a row or rows in a table unique so that no to fields in that column are the same. You can also use FOREIGN KEYS, which will limit the data you can enter into column A of Table A to the values in Column A of Table B, just as an example.