0

MySQL:

If I have a user table and multiple other tables like seller, buyer etc, is it okay to use user_id foreign key as primary key inside tables like seller, buyer?

That'll leave gaps in primary key field of such tables. Would that result in slower queries? Also, will mysql attempt to use up the gaps by inserting new items in middle (which I don't want)?

1

Primary keys must be unique. As long as any particular user/user_id can't become more than a single seller/buyer (each), you will be fine. Whether or not that restriction does hold, depends on your domain. It would, e.g., fail, if a user can run multiple shops with each shop's owner being (considered) a distinct seller. (You might, e.g., want to attach contact information to a seller, which could be different from shop to shop - but specific to the seller, not to the shop. Just think of co-owned businesses…) In case of doubt, you might want to play safe, and not make the foreign key the primary key to save you from the trouble of propagating a redesign through your schema some time in the future.

As for MySQL trying to attempt to fill in the gaps: If it is a foreign key, you(r application) will be the only one to insert records. Thus: No.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.