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_id product_name owner_id (account_id)
account_id email password
seller_id account_id business_name address [some fields that only sellers would have...]
buyer_id account_id first_name last_name address [some fields that only buyer would have...]
renter_id account_id business_name address [some fields that only renter would have...]
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.