Data Modeling for different roles

Question

This is my first post here, so I hope its clear enough. I want to make an application for people who sell, buy, rent and fix a certain product.

I need to create a database that would keep the data of the product and of the accounts (with logins and personal information). My problem is here, an “account” can be a seller, a buyer, a renter, a fixer, it can be all, none, or some. The sellers, buyers, renter and fixer would have different fields though, like a seller would be able to post products, and have save some information that only a seller would have, a fixer would just be able to put his listing, but a seller, can also be a fixer. A buyer would be able to sell products too and favorite others. The difference between a seller and a buyer is that a seller has to be a company, etc…

My idea of the databases would be something like this:

Product

product_id
product_name
owner_id (account_id)

Accounts

account_id
email
password

Seller

seller_id
account_id
business_name
address
[some fields that only sellers would have...]

Buyer

buyer_id
account_id
first_name
last_name
address
[some fields that only buyer would have...]

Renter

renter_id
account_id
business_name
address
[some fields that only renter would have...]

Fixer

fixer_id
account_id
business_name
address
years_experience
[some fields that only fixers would have...]

Then other tables like account_favorite (to keep info on which products have been chosen as favorite).

Now, I feel that the way I'm doing it is not the correct way. Also thinking that if in the future, I think about something else like for example "collector" I would have to create a new table. Because this is an application that will be used by a lot of people I have to care about speed, but I also have to care about people maintaining and analyzing the data. I hope this post was clear, if it's not, please ask me.

Answer 1

I am no expert in ERDs and database design but this is what I think about your case:

You may want to have a table called role with an id, role name and more columns to describe the role if needed. Then a table for a generic "user". That user can be a fixer, buyer, seller... Now some columns would apply to a certain role, some may not as you described in your question. I think this may be modeled using nullable columns and handled in you application by enforcing rules that you may want to put in your business logic models. I think this may be a simple solution.

An advanced technique is to model an ERD using subtypes/supertypes. This document is very useful for you to understand / practive how to model "optional" data:

http://sd271.k12.id.us/lchs/faculty/bkeylon/Oracle/database_design/section12/dd_s12_l04.pdf

Answer 2

If the roles that users can play are quite distinct, then yes you're on the right track. Where they have fields in common, then put them in the shared Accounts table. If that leaves just a few fields in the various role tables, you should probably ditch them and just have a single Accounts table with some spare columns.

The trade-off is greater complexity with more tables but (potentially) better performance with a few narrow table than a single wide and long table. My normal inclination would be to start with a simpler model and then optimize for performance only if necessary, but given the pain in restructuring a database, going with the more flexible model you've described from the beginning is probably worthwhile.