CODEWITHSUNDEEP
mysqldatabasedatabase-design
kazehaya
kazehaya

Reputation: 521

Create a separate table for a users functionality

I'm working on a project where I have regular users (buyers) and sellers. The project must be future proof to handle millions of records. I'm wondering what would be the best approach for storing this inside the database since a seller has some fields that a normal user does not have. For example a bank number, company name, about me, why me etc.

At the moment I have a users table and a user_profile pivot table.

Do you think creating a separate table for the sellers is a better idea combined with a seller_profile pivot table or add the seller fields to the user_profile table and check for the role of the user?

And what will be the best option performance wise?

Upvotes: 1

Views: 987

Answers (2)

VIjay J
VIjay J

Reputation: 766

Basically you have two entities Buyer and Seller.Lets see each case:
Separate Tables with separate attributes in each :

Table Structure :
Buyer(id,name,address,b1,b2,b3);
Seller(id,name,address,s1,s2,s3,s4 );
(Note b and s are column names);

Buyer and Seller can login to your system. If you put both in separate table you need to check in both tables. Similarly for signup ,you need to check for existence of user in both tables. So here you are hitting database twice. Imagine if you have billions of records.This may slower down performance.

3 Separate Tables:
Table Structure:
User(id,username,password,name,address)
Buyer(id,b1,b2,b3,fk_to_user);
Seller(id,s1,s2,s3,s4 ,fk_to_user);
(Note b and s are column names);

We have just taken out common field into User table. Fields specific to Buyer and Seller are shifted to Buyer and Seller table respectively. This design helps to solve two problems:
1.If there are additional attributes are added to Buyer or Seller table , it will not affect overall User table or overall design.
2.This is optimized as it hits database once while login to system.


[Note: I have taken case of login as example.Once user logs in to system you will have its details like "id" you can fetch any records corresponding to that user. ]

Upvotes: 1

Matthijs van Hest
Matthijs van Hest

Reputation: 789

As any database design you should start with asking yourself: What are the entities?

To me, a buyer and a seller seem to be diffrent. They both have other properties. A seller has a collection of items to sell. A buyer should place orders, maybe combining items of multiple sellers. Therefor they act in a whole other way.

I think they should have seperate tables.

Upvotes: 0

Related Questions