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?
