ER Diagram to Database conversion

Question

Suppose i have Two strong entity E1 and E2 connected by a 1 to many relationship R.

E1 <--------- R ---------- E2

How many table will be created when i will convert the above ER diagram into database ?

I know that when E2 will be in total participation answer will be 2. Since, E2's primary key will merge perfectly. I am not sure about above. I have seen multiple places and found different answer. I am looking for some solid argument with answer.

Answer can be 2 or 3. I want to know which is more correct.

Answer 1

Chen's original method mapped every entity relation and relationship relation to a separate table. This would produce 3 tables:

E1 (e1 PK)
E2 (e2 PK)
R (e2 PK, e1)

Full participation by either E1 or E2 can be handled by an FK constraint.

As you can see, E2 and R have the same determinant / PK. This allows us to combine the two relations into one table, using a nullable e1 column if E2 participates partially in the relationship, non-nullable if it participates fully. Full participation by E1 still requires an FK constraint:

E1 (e1 PK)
E2 (e2 PK, e1)

I want to know which is more correct.

Logically, the two solutions are pretty much equivalent.

Making 3 tables maintains the structure of the conceptual (ER) model, but produces more tables which increases complexity in one way. On the other hand, it avoids nulls which create their own complexity.

Making 2 tables reduces the number of tables but introduces nulls. In addition, we have to resort to different mechanisms (nullable columns vs FK constraints) to implement a single concept (full participation).

Other requirements can also affect the decision. If I have 50 optional attributes, I certainly don't want to deal with 50 distinct tables! However, if I wanted to create another relationship (R2) which only applies to values in E2 which are already participating in R, I could enforce that constraint in the first design using an FK constraint: R2 (e2) referencing R (e2). In the second design, I would need to use a trigger since I only want to allow references to e2 which have non-null e1 values.

There is no ultimately correct answer. Conceptual, logical and physical modeling address different concerns, and as-yet unknown requirements will affect your model and contradict your decisions. As in programming, try to keep things simple, refactor continuously and hope for the best.