1

I am building a ecommerce application with two different types of users, users who shop and vendors/brands.

Should I make two tables for each user like? 

User| id, email, password, username, address, stripeCustomerId

Brands| id, email, password, username/brandName, shippingRate, address, stripeAccessToken etc.

Or should I make it like so: 

Users| id, email, password, username, address, stripeCustomerId

Brands| userid, etc...
Prgmmr
  • 41
  • 4
  • 3
    No, One table with a column in it where you place something that identifies the user type, like `user_type = shop` or `user_type = brand` _This way when you come up with another user type, its a simple job to add another type, and you dont need to create more tables_ – RiggsFolly May 08 '19 at 09:32
  • Does some data repeat in both tables? if yes, make just one table for users and a column who's brands. If no, make two tables. The login would be easy if users are in one just table. – Roy Bogado May 08 '19 at 09:32
  • You can add a column to differentiate vendors with customers. `isCustomer` and `isVendor` can be helpful – Ravinder Reddy May 08 '19 at 09:32
  • As PostgreSQL is tagged you could consider using table inheritance but it will not work in MySQL as MySQL didnt implement anny kind of SQL 1999 standard type inheritance feature https://en.m.wikipedia.org/wiki/Structured_type – Raymond Nijland May 08 '19 at 09:42
  • ... so @RiggsFolly ‘s option is the best if you need to implement it in both MySQL and PostgreSQL and or make it across database systems – Raymond Nijland May 08 '19 at 09:44
  • Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy May 08 '19 at 18:16

2 Answers2

1

This is an example of trying to model the object-oriented notion of inheritance in a relational database. If you search for that term, you'll find several answers on Stack Overflow.

In your case, I think you have 3 logical entities:

  • User: email, password, username, address...
  • Customer (is a type of user): StripeID
  • Vendor (is a type of user): shipping rate, stripe token

How you model those logical entities to physical objects in your database is mostly a question of trade-offs - the other answers explain those.

I assume there will be significant differences in both the behaviour and attributes between "customer" and "vendor".

I also assume your data model will evolve over time - for instance, you probably need to store more than one address for each user (shipping, billing), you probably have different lifecycles for "customers" (new, registered, registration confirmed, payment confirmed) and "vendors" (new, approved, rejected).

If those things are true, I'd just bite the bullet and have 2 tables, customer and vendor. This means you can evolve their behaviour more easily - you don't have to worry about needing a slightly different address logic between two "customer" and "vendor", you just build what you need. Your schema is a little more self-explanatory - your foreign keys go to tables that say what they do (products -> vendors, not products -> users).

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
  • By 3 entities do you mean 3 tables? Also when you mean 'is type of user' do you mean just to refernce the user id in the column of customer table and vendor table? – Prgmmr May 09 '19 at 00:23
  • thanks for clearing things up, makes sense. From what I understand, I need 3 tabels, one for Users, one for Customers, and one for Vendors. While adding a FK to customer and vendor of the userid. – Prgmmr May 09 '19 at 09:27
  • @Prgmmr no he means you need exactly 2 tables in case there will be big difference between Vendor user and Customer User. So, `VendorUser` and `CustomerUser` – Sarvar Nishonboyev Jul 27 '21 at 06:46
0

It shouldn't be two tables, but three :D

 1. users (id, name, password, )
 2. customers (user_id, customer_specific_fields)
 3. vendors (user_id, vendor_specific_fields)
Said
  • 689
  • 6
  • 20