How to model direct ownership alongside group ownership

database-design

I want to model the following:

  1. Companies sell their own Products.
  2. Sometimes, Companies will package multiple Products into a Bundle, and sell those as well.
  3. A Company may only sell their own Products and Bundles.

I feel like it should be possible to model this just using foreign keys but I can't work it out. The possibilities I have considered, do not properly ensure data integrity.

I don't want to rely on something that will only work in a single kind of database, but could use something Oracle specific as a last resort.

Failed Attempt #1

products
  id

bundles
  id

products_bundles
  product_id REFERENCES products(id)
  bundle_id REFERENCES bundles(id)
  company_id REFERENCES companies(id)

companies
  id

This doesn't allow individual products to be related to a company. They are only related when they are part of a bundle, but they may not necessarily be part of any bundle.

Failed Attempt #2

products
  id

bundles
  id
  company_id REFERENCES companies(id)

products_bundles
  product_id REFERENCES products(id)
  bundle_id REFERENCES bundles(id)

companies
  id

This is the exact same problem as #1.

Failed Attempt #3

products
  id
  company_id REFERENCES companies(id)

bundles
  id
  company_id REFERENCES companies(id)

products_bundles
  product_id
  bundle_id

companies
  id

In this model, it is possible to put a product for one company, into a bundle owned by a different company.

Failed Attempt #4

products
  id
  company_id REFERENCES companies(id)

bundles
  id

products_bundles
  product_id REFERENCES products(id)
  bundle_id REFERENCES bundles(id)

companies
  id

In this model, the ownership of a bundle could be derived from the products in the bundle. However, there is nothing to prevent products owned by different companies being put into the same bundle which breaks this possibility.

Failed Attempt #5

products
  id
  company_id REFERENCES companies(id)

  UNIQUE (id, company_id)

bundles
  id

products_bundles
  bundle_id REFERENCES bundles(id)
  product_id      
  company_id

  FOREIGN KEY (product_id, company_id) REFERENCES products(id, company_id)

companies
  id

This is better but I'm slightly uncomfortable about the need for the the composite foreign key. Much of what I have read suggests that a composite foreign key is a sign of the data not being fully normalised but I'm not sure if that is true here and, if it is, how to improve it further.

This also doesn't allow me to enforce a company-wide unique naming scheme for bundles (although I can do that for products).

Possible Attempt #6

products
  id
  company_id REFERENCES companies(id)
  UNIQUE (id, company_id)

bundles
  id
  company_id REFERENCES companies(id)
  UNIQUE (id, company_id)

products_bundles
  bundle_id
  product_id      
  company_id
  FOREIGN KEY (product_id, company_id) REFERENCES products(id, company_id)
  FOREIGN KEY (bundle_id, company_id) REFERENCES bundles(id, company_id)

companies
  id

I guess this might be the right solution. Are these composite foreign keys the right way to go?

Best Answer

  • A simple way is to make Companies sell only Bundles.
  • A Bundle can contain one or more Products
  • A Bundle has a flag that enforces "single product" and/or handling of it

Schema:

company
  company_id (PK)
  ...

bundle
  bundle_id (PK)
  company_id REFERENCES company(company_id)
  is_single_product
  ... 
  UNIQUE (bundle_id, company_id)

product
  product_id (PK)
  company_id REFERENCES company(company_id)
  UNIQUE (product_id, company_id)
  ... 

product_bundle
  bundle_id REFERENCES bundle(bundle_id, company_id)
  product_id REFERENCES product(product_id, company_id)
  company_id REFERENCES company(company_id)
  PK (bundle_id , product_id )
  ...

Logic to enforce (trigger, view, whatever)

  NOT "count(*) > 1 FROM product_bundle JOIN bundle where is_single_product"

Variation which allows FKs only is_single_product to restricted_product_id

bundle
  bundle_id 
  company_id REFERENCES company(company_id)
  restricted_product_id REFERENCES product(product_id) (nullable)

Then the logic is "no rows in product_bundle if restricted_product_id is NOT NULL"

Edit, added company_id to bundle and products with super keys to push down "unique company per product"

Related Question