Modeling Sellers and Products

Question

I'm trying to model product and seller information. The seller can be either an individual, or a company, or a company with branches (and the product is being sold by a particular branch). Each product will only have one associated seller (which could be an individual, a company with no branches, or a branch of a particular company).

I feel like I should have a table for individuals and another table for companies (with a parent-child relationship, perhaps to itself, so it can represent the company-branch relationship). The products table could then have an individual_id (the PK of the individuals table) and a company_id (the PK of the companies table). The products table will just populate either the individual_id or the company_id column.

The above feels messy, though.

I feel I could just use two tables: products and sellers. The sellers table can represent an individual, a company, or a companies branches (meaning the sellers table would need that parent-child relationship even though it wouldn't apply to individuals -- just like it doesn't apply to some companies). Individuals and companies have different columns, though -- but I guess just leave non-applicable columns blank).

Is the above model good? Any possible downsides?

Also, when designing a database -- is it a good idea to put related data in a single table? For example, the companies need to store address information. Do I put that in a company_addresses table (means a JOIN). Or do I just represent them as columns in the companies/sellers table (assume each company has one address only -- but having multiple addresses could still be put in a single row, like postal_address_line_1, physical_address_line_1, etc.).

Answer 1

My proposal is this:

You will have different tables for Brands, Companies and Individuals beacuse of their different attributes... the only thing they have in common is that they all are SELLERS. Note that I have included the relationship between Company and Brand

As the product can exists without having a seller (you can assign a seller days after you create the product), I'm not going to put any foreign key in this table; also it will keep the products table cleaner with only product attributes.

Now, let's go to the Sellers table (which is not a normalization of entities... it is just a ROLE that they play in this business):

• since the product will only have a single seller; I'm including a 1 to 1 relationship with product table. This means product_id is a foreign key but also the primary key.
• all the other foreign keys are not mandatory (all of them can be null)

• seller_check, is a calculated field with this formula:

  seller_check    SMALLINT AS(
  CASE WHEN company_id IS NULL THEN 0 ELSE 1 END
  + CASE WHEN brand_id IS NULL THEN 0 ELSE 1 END
  + CASE WHEN individual_id IS NULL THEN 0 ELSE 1 END) NOT NULL
  

• And finally you need to include a table check constraint to ensure that only ONE of the foreign keys is NOT NULL:

  ALTER TABLE sellers ADD CONSTRAINT sellers_ck_1 CHECK ( seller_check <= 1 );

Good luck!

Answer 2

Answer heavily depends on the normalization form you are targeting as well as your application requirements.

Based on details you have reveled following schema can be better:

It is better idea to put relevant details in relevant tables. It addresses specified needs in following manner:

1. Seller can be any individual: For this case set type to individual and leave company_branch_id null. Here contact will reveal the individual details.

2. Seller can be company branch: In this case company_branch_id will point to the branch and maintain the company details in companies table, such as CIN. and contact will still reveal the contact point.

3. Seller can be company with no branch: Treat this similar to case 2, just the difference is the brach points to single unit of the company.