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"

[abazovic]

If You are talking about
"I think it is better that a big monolithic table where business user and end users get mixed up."
or
"Each of these will be in separate tables"
You are talking about
Single Table Inheritance - table per concrete class
Look at
http://martinfowler.com/eaaCatalog/classTableInheritance.html
http://martinfowler.com/eaaCatalog/concreteTableInheritance.html
http://www.martinfowler.com/eaaCatalog/singleTableInheritance.html

Use that best suits for problem

Of course for some cases You can't use auto increment or sequence
Best way is that ID is generated by object itself for example GUID

[oddz]

I assume business users and end users have *very different data/column requirements?

[system]