CODEWITHSUNDEEP
mysqlsqldatabaserelational-database
zcahfg2
zcahfg2

Reputation: 901

Relational schema : (0,1) to (0,1) relationship with attributes - make a new table?

My understanding is that when the cardinalities are 1 to 1, then instead of making a relation table, we add the primary keys of one entity as foreign keys to the entity table.

But what about the case when it is (0,1) to (0,1) relation?

I.e. When a staff can be allocated to a single foreign workspace and no more than 1 person can be allocated to that workspace. These staffs will have scheduled start and end dates for the allocations.

So Staff entity, workspace entity and assigned relation between them. I intend to create two attributes in the relation 'assigned' - start_date and end_date.

1) If I do not create a new table, what happens when a staff is not assigned to any workspace? Do I simply set the foreign key to be NULL?

2) Furthermore, would I add the relation attributes to the staff entity table?

I think simply making a separate table would be a lot easier...

Many thanks in advance.

Upvotes: 0

Views: 663

Answers (2)

Narayan Bhandari
Narayan Bhandari

Reputation: 426

In my opinion, you will have to create another table workspace_allocation where allocationID would be primary key, staff_id would be foreign key references staff, workspace_id would be foreign key references workspace, start_date, end_date.

In this way you can have 0-1 relationship between staff and workspace.

how does it sounds? please leave in comment.

thank you

Upvotes: 1

philipxy
philipxy

Reputation: 15156

How you arrange tables depends on the modeling method you are using. There can be many relational schemas representing the same application situation. (In Chen ER modeling, which is really the only method that should be called "ER", entity tables do not have FKs to other entity tables.) For a 0-or-1-to-0-or-1 case the obvious & straightforward design is a separate table with the appropriate pairs and each entity unique. Or you could put a nullable unique FK in one of the entity tables to the other entity table; a NULL in an entity's entity table row indicates that no other entity is paired with it in the relationship represented by the former table. Notice that the latter design could be described as embedding the former table into one of the entity tables. The latter design is asymmetrical & usually less natural, but you might do it because of the usual reason for not using the obvious & straightforward design, which is engineered performance improvement.

Upvotes: 2

Related Questions