How should my DB model looks if I have two connects to the same tables?-CodePudding

I'm creating app when Users can be customer or trader (I can't create separate tables for each of them). Every trader can be owner of many products and create special products that can be shown only for selected users (Special_product table). Every user can place an order (Order_table) specific product. The problem is that I have two similar join tables in my database model. I don't know if this is a good solution and that's why want to ask you - it should looks like this or maybe there is a better solution?

Graphic model:

CodePudding user response：

I think your model is good.

To get the available products for a given user, I would do

SELECT
  p.product_id,
  p.product_name,
  vendor.user_name vendor_name
FROM
  (
    SELECT product_id FROM product
    UNION 
    SELECT product_id FROM special_product WHERE user_id = ?
  ) selection
  INNER JOIN product p ON p.product_id = selection.pproduct_id
  INNER JOIN [user] vendor ON vendor.user_id = p.user_id

user is a reserved word in SQL, I recommend picking a different table name.