New to this exchange site so I hope my question isn't inappropriate.
Creating a somewhat normalized database I had one design problem I'm not sure how to handle efficiently.
One table has a One-To-Many relationship with two other tables. These two tables then have a Many-To-Many relationship between them. Shouldn't I somehow ensure (other then in the programming insertion) that the items that are linked together belong to the same parent table.
Let's put it in a Scenario (This is a fictional scenario):
Division table where each row is a division in a company. It has One-To-Many relations with a Employee table. So each employee belongs to one and only one division.
The Division table also has One-To-Many relations with a Project table where specific projects are logged. The project also has to register how many hours a employee has logged with this specific project. I would do this with a Many-To-Many table between Employee and Project and a Hours column.
This should all work fine ( I think at least) except that theoretically nothing in the database would ensure that the Employee and Project belong to the same Division even when they should and in fact both belong to a entity in that table.
What is a good way to ensure the intention of this schema and the integrity of the data is handled by some abstract relational database. Would you construct the database in any other way or would you put some constraint on the Many-To-Many relationship?