lundi 7 novembre 2022

SQL constraints for table that is a subset of one to many relation

I want to create a schema that ensures the consistency of the following data:

I have a COMPANY table and a PRODUCT table in a one-to-many relationship (PRODUCT has a COMPANY_ID).

Now I need to memorize the certifications that are done on the company's products. One CERTIFICATION can have multiple PRODUCT, but only one COMPANY; one COMPANY can have multiple CERTIFICATION.

I can create a many to many relationship between CERTIFICATION and PRODUCT with a PRODUCT_CERTIFICATION (C_P) table, but in this way there could be a CERTIFICATION with multiple COMPANIES and this is what I want to avoid.

My idea is to add a COMPANY_ID on the C_P table (making it COMPANY_CERTIFICATION_PRODUCT) that references the COMPANY_ID on PRODUCT (C_P has to be in a one-to-one relation with CERTIFICATION), but i dont know exactly how to do all this stuff. Also i think there is a better way to acheive what i want.

This seems to me like a common design pattern but i couldnt find it anywhere on the internet (as you can see from the title, i dont have the right keywords).

Aucun commentaire:

Enregistrer un commentaire