I'm modelling a relational database in which the following schema can be used to explain 2 tables: ERD Model.

The guidelines specified are that:

  1. A workplace includes a manager
  2. Each employee is designated for an office

To be able to model i produced an ERD using MySQL work bench, which provided the next DDL.

The problem I've is the fact that to be able to enforce that the office must possess a manager, the foreign type in work table isn't nullable. Likewise, the foreign type in employees table representing work they work with is needed for each staff and for that reason not nullable. This will make sense in my experience within the model, but also for the implementation it causes it to be impossible to place data as each depend around the existance of tuples within the tables.

The only real answer I'm able to think about would be to result in the secrets nullable such that certain can temporarily exist with no other.

Is the right way to solve the problem? The database will ultimately be normalised to 3NF possibly BCNF.

However , you are trying to record the connection between offices and staff two times. Once at work record and again within the staff record. You need to only record the connection in one location. Frequently this really is completed in a mix-reference table with two posts: Office_ID and Staff_ID. But it is also present with skip the 3rd table and merely record the connection within the tables.

Within this situation, you are able to eliminate your condition by getting rid of work area and foreign key in the Staff table. You'll have the ability to create as numerous Staff records since you need. When you create a workplace record, you'll have the ability to assign among the Staff to work.