Are identical primary keys bad practice?

Question

I am trying to create a site where users can register and create a profile, therefore I am using two MySQL tables within a database e.g. users and user_profile.

The users table has an auto increment primary key called user_id.

The user_profile table has the same primary key called user_id however it is not auto increment.

*see note for why I have multiple tables.

When a user signs up, data from the registration form is inserted into users, then the last_insert_id() is input into the user_id field of the user_profile table. I use transactions to ensure this always happens.

My question is, is this bad practice?

Should I have a unique auto increment primary key for the user_profile table, even though one user can only ever have one profile?

Maybe there are other downsides to creating a database like this?

I'd appreciate if anyone can explain why this is a problem or if it's fine, I'd like to make sure my database is as efficient as possible.

Note: I am using seperate tables for user and user_profile because user_profile contains fields that are potentially null and also will be requested much more than the user table, due to the data being displayed on a public profile.

Maybe this is also bad practice and they should be lumped in one table?

Answer 1

I find this a good approach, I'd give bonus point if you use a foreign key relation and preferably cascade when deleting the user from the user table.

As too separated the core user data in one table, and the option profile data in another - good job. Nothing more annoying then a 50 field dragonish entry with 90% empty values.

Answer 2

It is generally frowned upon, but as long as you can provide the reasoning for the 1 to 1 relationship I'm sure it is fine.

I have used them when I have hundreds of columns (and it would be more logical to split them out into separate tables) or I need a thinner table to speed up fullscans

In your case I would use a single table and create a couple of views.

see: http://dev.mysql.com/doc/refman/5.0/en/create-view.html

In general a single table approach is more logical, quicker, simpiler, and uses less space.