I'm modeling a system where with vendors, each of which sells some overlapping subset of products available in the universe. Products (T-shirt) come in various variants (Small, Medium, Large) with generic (size) and vendor-specific (Item code) attributes. There is also vendor-specific information about the Products themselves which doesn't vary by size (e.g., their manufacturer).

I've got four tables involved:

VendorProduct (vendor_id, product_id, manufacturer)
Product (product_id, name)
Variant (variant_id, product_id, size)
VendorVariant (variant_id, vendor_id, item_code)

I'd like my database to enforce a constraint that every VendorVariant has an associated VendorProduct, which could be found with this query:

select * from vendor_variants 
  inner join variants on variants.variant_id = vendor_variants.variant_id
  inner join products on products.product_id = variants.product_id
  inner join vendor_products on vendor_products.product_id = products.product_id AND
               vendor_products.vendor_id = vendor_variants.vendor_id

Can I do this in Postgres without changing the table structure? If no, are there tricks to model the data differently to allow this type of constraint?

  • Do you mean you want to be sure that for every VendorProductVariant the associated vendor and product (which can be found through the Variant table) are actually associated themselves (there is a row in the VendorProduct)? – ypercubeᵀᴹ Dec 17 '15 at 23:03
  • Exactly. I'm using foreign key constraints to ensure a VendorProductVariant has a Variant, a Variant has a product, and a VendorProduct has a Product. What I can't do is ensure a VendorProductVariant always has a VendorProduct with the same vendor when you go through the chain. – spike Dec 17 '15 at 23:05
  • Then no way without changing the tables. If you can change the tables, you need to add the product_id in the VendorProductVariant and then modify the 2 FKs (to Variant and VendorProduct) to use composite keys, (vendor_id, product_id) and (variant_id, product_id) respectively. See a similar question: Many to Many and Weak Entities – ypercubeᵀᴹ Dec 17 '15 at 23:08
  • @YperSillyCubeᵀᴹ that's great advice (expand to an answer?) Should I use triggers or some other trick to automatically set product_id when creating a VendorProductVariant (since you can get it from the variant) or is that not the right path to go down? – spike Dec 18 '15 at 16:14
  • If you add the product_id in the VendorProductVariant table, I don't see any reason for triggers. – ypercubeᵀᴹ Dec 18 '15 at 16:19

Your Answer

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

Browse other questions tagged or ask your own question.