This certain MySQL database organisation

Question

Hypothetically, let's say that I have three tables:

- Products (id, product_name, added_timestamp)
- Individuals (id, name, last_name)
- Companies (id, company_name, company_number, company_address)

Now, let's say that both individuals and companies can add their products to my virtual shop.

Now, let's say I want to display all the added products sorted from descending order by the Products.added_timestamp column. However, I don't know how to connect Products with Individuals and Companies and display all the data.

How would I approach this problem?

Answer 1

I can think of at least 2 ways of doing this.

1. Add nullable individual_id and company_id FK columns to Products table.

   When products are added by an individual, populate the individual_id. When products are added by a company, populate the company_id. This would be a many-to-one relationship between products-and-individuals or products-and-companies.

   Sample query would be something like:

   select p.product_name, p.added_timestamp, i.name as 'individual_name', c.company_name as 'company_name'
   from Products p
   left outer join Individuals i on p.individual_id = i.id
   left outer join Companies c on p.company_id = c.id
   order by p.added_timestamp desc
   

2. Create separate mapping tables, e.g. Products_By_Individuals and Products_By_Companies, to associate products with individuals and companies externally. This would be a many-to-many relationship between products-and-individuals or products-and-companies.

   Sample schema:

   • Products_By_Individuals (id, product_id, individual_id)
   • Products_By_Companies (id, product_id, company_id)

   And sample queries:

   select p.product_name, p.added_timestamp, i.name as 'entity_name'
   from Products_By_Individuals pbi
   inner join Products p on pbi.product_id = p.id
   inner join Individuals i on pbi.individual_id = i.id
   union
   select p.product_name, p.added_timestamp, c.company_name as 'entity_name'
   from Products_By_Companies pbc
   inner join Products p on pbc.product_id = p.id
   inner join Companies c on pbc.company_id = c.id
   order by p.added_timestamp desc
   

   If you play with the projections (the select clause), you could possibly due a union between these 2 queries.

EDIT: modified sample queries to return (1) product name, (2) product timestamp, and (3) individual or company name, as mentioned in comments.

Answer 2

You can approach this by creating the relevant foreign keys so you can use relations in your databases. Here is a nice read about relational databases. I strongly recommend it for you to read it, sou you can get the hang of the relational design idea.

Edit: In your particular case i would to this:

• Products (id, product_name, added_timestamp, seller_type, seller_id)
• Individuals (id, name, last_name)
• Companies (id, company_name, company_number, company_address)

where seller_type can be either 'i' as Individual or 'c' Company, and seller_id is the corresponding foreign id.