-3

I have three tables named, Customer , Product , Sale . I need to know which products has been presented in which Sales and which customers have been attended to which Sales. So here is what we have so far:

Customer (Id, Name)

Product (Id, Name)

Sale(Id, Title, Date)

CustomerSale(Id, CustomerId, SaleId) //intermediate table

ProductSale(Id, ProductId, SaleId)   //intermediate table

Also I need to know which customer likes (just imagine they can up-vote products!) which products in each Sale and also I need to know which customer mark which product as useful in each Sale. So I have two solutions:

Scenario 1: simply make an intermediate table between those two intermediate tables!

CustomerProduct(Id, customerSaleId, productSaleId, doesLikes, isUseful)

Scenario 2: make an intermediate with FK to three tables (Customer, Product, Sale)!

CustomerProduct(Id, SaleId, CustomerId, ProductId, doesLikes, isUseful)

Problem: None of the above solutions insure data integrity because in scenario1 it is possible to insert customerSaleId and productSaleId which does not refer to the same Sale. In scenario2 it is possible to insert an invalid (Sale, Customer, Product) triple. because you cannot be sure whether the product is presented in that Sale or not moreover you cannot be sure the customer has attended to that sale or not.

So is there any better solution?

Hossein Narimani Rad
  • 31,361
  • 18
  • 86
  • 116

2 Answers2

0

If you ignore the surrogate keys on CustomerSale and ProductSale and have scenario 2 reference the compound unique keys on these tables of (CustomerId, SaleId) and (ProductId, SaleId) respectively would this give you what you want?

DrabJay
  • 2,989
  • 2
  • 13
  • 12
-2

I change your structure to this

Customer (Id, Name)

Product (Id, Name)

Sale(Id, CustomerId, Title, Date)

ProductSale(Id, ProductId, SaleId)  

CustomerProduct(Id, productSaleId, doesLikes, isUseful)

isn't it better?

Farrokh
  • 1,167
  • 1
  • 7
  • 18