foreign key-auto increment

[designtrooper]

I have 3 tables. 2 child and 1 parent.

The child tables have foreign keys that reference the parent.

I assume(and tell me if I am wrong) that the foreign keys in the child tables(which also serve as Primary keys in these table) should not be set to auto-increment
since their values will be taken from the parent table.


Am I right?

[r937]

you are right, they should not

but why are the FKs also PKs in their table? this means the 2 child tables are each in a one-to-zero-or-one relationship with the parent -- perhaps you should think about merging them all into one table

[designtrooper]

you are right, they should not

but why are the FKs also PKs in their table? this means the 2 child tables are each in a one-to-zero-or-one relationship with the parent -- perhaps you should think about merging them all into one table

Well...here is the rational.

This site will have 2 kind of users, business users and end_users.
Each of these will be in separate tables(the foreign keys will be there)

And the foreign keys from the above 2 will reference a table named credentials where the passwords and e-mails will be kept of the above 2.
So, for organisational purposes I have these 3 tables, I think it is better that a big monolithic table where business user and end users get mixed up.

What do you think?

[r937]

sounds like a supertype / subtype arrangement

could be quite acceptable, depending on how you define "better than a big monolithic table"