3 tables-foreign keys

[jimfog]

I have 3 tables:Business_usersEnd_UserCredentials The credentials table will hold the username/password of the end users and the business users, whose other details(address, phone...etc) will be kept in the separate corresponding tables. Here is to what schema I have concluded-and tell me please what you think about it: The primary keys of business users and end users tables(businesuser_Id/enduser_id) will appear as foreign keys in the credentials table. That said, the credentials table will have 4 columns:crID(primary key)usernamepasswordbusinessuser_idenduser_id I just want to know if I am implementing correctly the foreign key logic here.

[justsomeguy]

That seems backwards, it seems like the primary key in the credentials table should be a foreign key in the other 2. The other 2 can even use the foreign key as their primary keys.

[jimfog]

That seems backwards, it seems like the primary key in the credentials table should be a foreign key in the other 2.

After some thought...I concluded that what you propose is the best

The other 2 can even use the foreign key as their primary keys.

If for example crId is the primary key in the credentials table.Are you suggesting that crID will be used as the foreign key in the other 2 tables-with the same exact name?

[justsomeguy]

Sure, why not? It can be the primary keys for the other tables too, you wouldn't need multiple records per user I assume. If it's a 1-to-1 relationship then make it the primary key in every table.

[jimfog]

Sure, why not? It can be the primary keys for the other tables too, you wouldn't need multiple records per user I assume. If it's a 1-to-1 relationship then make it the primary key in every table.

Ok I have 1 rather important question which has to do with the way foreign keys works.AS I said I have 2 tables, the one is credentials(username, password) and the other table holds info such as address, phone etc...of the userwho is stored in the credentials table. Suppose the second table is filled at a later time(this table holds the foreign key from the credentials table)----- how am I going to fill the foreign key column.AM i going to use a SELECT statement to take the primary key from the credentials table(which is the foreign key in the second table) and put it in the second table? I thought that InnoDB automatically updates foreign keys in tables where there is one.I hope you understand what I am talking about-if not I will try to clarify further.

[birbal]

you have to do it manually InnoDB does not know which id from parent table you are trying to referenced in child table. Though InnoDB will ensure every time that the id you have passed in child table have a reference of it in parent table.

Edited February 21, 2013 by birbal

[jimfog]

Ok I will do it manually...Does that mean using a select a statement to grab the primary key from the credentials table(which is the foreign key in the second table). Is this the logic?If yes, I suppose-among others- that the foreign key in the second table should NOT be set to auto increment

[justsomeguy]

You need the primary key somehow, if you don't already have it defined in your code then yes, you need to get it.

[jimfog]

if you don't already have it defined in your code then yes,

How can I accomplish that?

[justsomeguy]

I don't know the context of your application, but when I'm working with user accounts I usually have the user ID in a variable. If you don't already have that, then get it from the database.

[jimfog]

Soon I will implement what is mentioned in this post and will see how these things can work on practice.I will post again if necessary.

[jimfog]

I don't know the context of your application, but when I'm working with user accounts I usually have the user ID in a variable. If you don't already have that, then get it from the database.

Ok...here we are. I am at a point where I have to implement in practice what is said above.Here is the situation,1 table named credentials(sorry for repeating myself here but I do it in order to illustrate the problem as good as possible), 2 users in it, with crID of 1 and 2 respectively(both of these are end users and and a table is kept named end_users where other relevant data to them are kept) And now a business user comes to register which means 2 new records are created, one in the credentials table with crID of 3 and one new record in the business users table where the crID is the foreign key referencing the crID in the credentials table. That means the crID in the business users table must take the value of 3. How am I going to achieve that? I thought this and tell me if it sounds OK. AFTER the record is created in the credentials table, pick the crID corresponding to the e-mail provided by the specific user and put it n the business user table. What do you think?

[jimfog]

Ιt seems mysql_insert_id can help me achieve my goals.

[jimfog]

Sure, why not? It can be the primary keys for the other tables too, you wouldn't need multiple records per user I assume. If it's a 1-to-1 relationship then make it the primary key in every table.

If the relationship you describe is one -to -one, the primary key of one table is used as foreign key in other tables(more than 2 in my case).Then...which relationship is called one-to-many?

[justsomeguy]

For a one-to-many relationship you can do the same thing, the primary key in the unique table would appear many times in the dependent table. For a many-to-many relationship you need to use a third lookup table to link the records.

[jimfog]

Ok...got it,thanks.But in my case you will agree that one-to-one is the way to go.Sorry if I am answering for the second time

[jimfog]

I assume, since foreign keys in 2 tables reference the primary key of another table(the parent table). I must NOT set these foreign keys to AUTO-INCREMENT.

[justsomeguy]

You don't want the database to set those values, you want to set them yourself.