-1

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.

enter image description here

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).

enter image description here

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.).

  • 1) Sparse table is not unusual solution. 2) Store multiple values in one record in CSV form is a bad practice. Use additional table instead. – Akina Aug 20 '18 at 6:30
  • What do you mean by "sparse table"? Also, what multiple values are you talking about? – DatabaseNewbie Aug 20 '18 at 6:55
  • What do you mean by "sparse table"? Sparse table is a table any record in which contains NULL values in some fields obligatorily. For example, in your case the record contains NULL in "company_addresses" (and in "parent", "boss", etc.) if it is individual and in "last_name" (and "birth_date", etc.) if it is company. And there is no records without NULLs in all fields. what multiple values are you talking about? That's YOU are talking about: multiple addresses could still be put in a single row, like postal_address_line_1, physical_address_line_1, etc. – Akina Aug 20 '18 at 8:33
  • I didn't see the addresses as being "multiple". I see one physical address and one postal address. Null fields better than JOINs? – DatabaseNewbie Aug 20 '18 at 9:21
  • Oh, you mean these addresses (postal_address_line_1 and physical_address_line_1) are stored in different fields of a record, one address per field? If so, I misunderstand you, sorry. – Akina Aug 20 '18 at 9:23
0

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!

enter image description here

  • Why is this better than my 2 table design? – DatabaseNewbie Aug 20 '18 at 17:24
  • This is only my point of view: 1 - better data dictionary. 2 - you use only the columns you need for each seller type. 3 - the relationship between brand and company is clearly handlable. 4 - Completeness: what if a company owns 10 brands... are you repeating 10 adresses? are you leaving those adresses blank? why would you reserve that table space and not use it?. Finally, what I always recommend is to use the model that makes you feel the most comfortable and safe. All the answers are just suggestions... The call is yours. Good luck! – AMG Aug 20 '18 at 17:41
  • The mayor difference would be: my model treats seller as a role played by several different entities that exist without needing to be a seller, You can treat the company, brand and individuals (and even Product) as beings without the need of a commercial link. Then you can create that commercial link later. Your 2 table design treats all antities as sellers and only sellers. They cannot exists without this role. If someday an individual becomes a Company seller or a product buyer... it will be difficult to scale. – AMG Aug 20 '18 at 17:49
0

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:

Products Sellers schema

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.

  • What is Sellers.name? – DatabaseNewbie Aug 20 '18 at 6:57
  • it is the human level identifier for sellers. – Kapil Bhagchandani Aug 20 '18 at 8:35
  • "It is better idea to put relevant details in relevant tables." - that sounds like a concept in OOP, not DB? Your design would not allow for a level deeper than branches (it's not likely to occur, but my design would allow for it if ever). – DatabaseNewbie Aug 20 '18 at 10:33
  • I am not getting you in proper at this point, can you share some example, or update your question. – Kapil Bhagchandani Aug 20 '18 at 10:39
  • My design can allow a company to have a branch, and a branch to have its own branches, and those branches could have their own branches ... Nested set. – DatabaseNewbie Aug 20 '18 at 13:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.