Search code examples
database-designcircular-dependency

Database Design: Circular dependency

Imagine the following database:

Table 'companies' has fields id, name and flagship_product_id. Table 'products' have fields id, name and company_id.

A company must have a flagship product (1:1 relationship) and all products have one company (1:N relationship).

When using a storage engine such as MyISM, there shouldn't be any problem with the above scenario, but when using an engine such as InnoDB, problems result when INSERTing new data.

What is a good solution except allowing a NULL relationship for the initial INSERT?

To summarize, A company must have one flagship product.

Solution

  • I don't know that particular database engine, but search for a way to temporarily suspend the data consistency checks or referential integrity during your atomic insert and update operations.