Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I'm reading this tutorial, and I have question about it.
Look at this picture:
'Reading' the logical Relationship I read that a Project must have one or more Employee. But the physical implementation has that a Project could have zero or more ProjectEmployee.
Or maybe I am wrong and the physical implementation is correct.
What do you think?
The physical model is what is created in most physical implementations of a many-to-many relationship. However, it does not correctly replicate the rules in the logical model.
The physical model allows for zero or more in both directions. The logical model indicates one or more from project to employee.
The reason that many physical implementations of any relationships do this is that in many DBMSs the SQL declarative referential constraints (i.e. foreign key constraints) don't allow for the rule to be suspended while data is inserted into two tables at one time. The SQL standard provides for "deferrable constraints" and some DBMSs implement these, but many don't and the ones that do haven't always, so not everyone uses them, even if they are available. Old habits can die hard.
For the ones that don't implement deferrable constraints, think about it this way: For there to be any kind of mandatory relationship in both directions (i.e. 1:1,M or even 1:1) then you would have to insert the parent and the child at the same time. While you can create a transaction that ensures the parent and child are created in one logical unit of work, the referential integrity rules aren't built to "suspend judgement" until the whole transaction finishes.
This is why in practice, physical implementations without deferrable constraints are essentially always optional at the child end. This lets you insert the parent and then insert the child.
The practical way around this is generally to write additional application logic to ensure that there is at least one child per parent when 1:1,M is the rule or to just leave it and hope that users do the right thing. These work-arounds aren't necessary if you do happen to have access to deferrable constraints.
