Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Jun 11 '13 at 3:19

This question came from our site for professional and enthusiast programmers.

    
Use JOINs, Luke. –  raina77ow Jun 10 '13 at 20:14
    
edit cuz reading is hard Put references to the individual/company in the products table. –  SenorAmor Jun 10 '13 at 20:14
2  
Are you looking for something other than the obvious which is to create a table with a key to the product id and another to the individual or company ids? –  Burhan Khalid Jun 10 '13 at 20:15
1  
use the Party Model with Table Inheritance. essentialstrategies.com/publications/modeling/advanceddm.htm –  Neil McGuigan Jun 11 '13 at 17:32

2 Answers 2

up vote 0 down vote accepted

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.

share|improve this answer
    
Thanks superEb, this is more what I was looking for but still... –  user2394156 Jun 10 '13 at 20:30
    
Alright, I will rephrase the question a little bit: An individual "Kate" added a product at 8 am called "Teddybear" A company "SuperCompany" added a product at 9 am called "Laptop" An individual "John" added a product at 10 am called "Lollypop" Now I want to display something like: Products: 1. Lollypop - by Kate (individual) at 10 am 2. Laptop - by SuperCompany (company) at 9 am 3. Teddybear - by Kate (individual) at 8 am How do I do this? –  user2394156 Jun 10 '13 at 20:31
    
Plus I have to keep in mind that there might be a lot of groups of users with diffirent types of data that can add their products, so the "individual_id", "company_id" might be too much of a killer for the project. –  user2394156 Jun 10 '13 at 20:32
    
You're gonna need a way to relate the entities, unless you duplicate data somewhere (as one might do with a NoSQL database), so I'm not sure what your concern with individual_id and company_id is. With #1 from my answer, you can do exactly what you want, just change select * to select the columns/values that you need (e.g. product name, added timestamp, individual name, and company name). Then have your UI use individual name if it returns a non-null value; otherwise use company name. –  superEb Jun 10 '13 at 20:39
    
Let's clear it up. You're telling me to pack all the tables in one row? Like product_id|product_name|ind_id|ind_name|ind_last_name|comp_id|comp_name? And then just display corresponding columns depending on if they're not null? I was thinking about this in the beginning but wasn't sure if it was a good solution because it might generate like 100 columns in one result quesry for example. –  user2394156 Jun 10 '13 at 20:45

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.

share|improve this answer
    
Sorry but I don't get it... I can put a column adder to Products and relate it to only one other table. How does this relate to my problem? –  user2394156 Jun 10 '13 at 20:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.