Are identical primary keys bad practice?

Question

I am trying to create a site where users can register and create a profile, so I use two MySQL tables in the database, for example. 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 , but it is not auto increment .

* see note on why I have multiple tables.

When a user subscribes, data from the registration form is inserted into users, then last_insert_id() is entered into the user_id field of the user_id table. I use transactions so this always happens.

My question is, is this bad practice?

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

Maybe there are other flaws in creating a database like this?

I would appreciate if anyone could explain why this is a problem, or if everything is in order, I would like to make sure my database is as efficient as possible.

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

Maybe this is also a bad practice, and they should be concentrated in one table?

Answer 1

I think this is a good approach, I would give a bonus ball if you use the foreign key ratio and preferably cascade when deleting the user from the user table.

Getting too much basic user data in one table and profile profile data in another is a good job . Nothing more annoying than 50 field dragon records with 90% empty values.

Answer 2

As a rule, he frowned at him, but so far you can give arguments in favor of relations from 1 to 1, I am sure that everything is in order.

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

In your case, I would use one 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, faster, simpiler and uses less space.

Answer 3

I do not think this is bad practice. This is sometimes very useful, especially if you want one class to work with authentication and not load all the profile data. Then you can change how your authentication works, create web services, etc., without worrying about maintaining data structures for profiles that can change as your project grows.

Answer 4

This is a very good practice.

This is right at the heart of writing good, modular, normalized relational database structures.