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