One-to-many relationship, how to generate 3 tables instead of 2

[nfh]

Hi,

I'm creating a conceptual model (which I believe is the corresponding
model to a Entity-Relationship Diagram) and I have a few one-to-many
relations with no mandatory participation on both sides.

If I'm not mistaken, this should generate 3 tables, one for each
original entity and a third one for the relation. This 3rd table
should get the primary key from each of the other 2 tables and have
its primary key set as the foreign key that came from the "many" side.

Despite that, PowerDesigner is generating just 2 tables and placing
the primary key from the "one" side on the "many" table.

What do I have to do in order to generate a physical model with 3
tables for a one-to-many relationship?

Best regards,
norberto

[gtss]

 Hi,
 I'm creating a conceptual model (which I believe is the corresponding
model to a Entity-Relationship Diagram) and I have a few one-to-many
relations with no mandatory participation on both sides.
 If I'm not mistaken, this should generate 3 tables, one for each
original entity and a third one for the relation. This 3rd table
should get the primary key from each of the other 2 tables and have
its primary key set as the foreign key that came from the "many" side.
 Despite that, PowerDesigner is generating just 2 tables and placing
the primary key from the "one" side on the "many" table.
 What do I have to do in order to generate a physical model with 3
tables for a one-to-many relationship?
Best regards,
norberto

Only Many-to-Many relationships generate the structure you have been
expecting, i.e. the participating tables and a 3rd 'intersection'
table.
The relationships that have cardinality defined as One-to-Many
generate as FK references between the two tables in the PDM.

Why do you believe that a 1:M relationship that is optional on both
roles should generate the 3rd 'intersection' table?
Have you used any other CASE E/R data modeling tool that did this?

Regards,
G

[nfh]

Post by gtss

 Hi,
 I'm creating a conceptual model (which I believe is the corresponding
model to a Entity-Relationship Diagram) and I have a few one-to-many
relations with no mandatory participation on both sides.
 If I'm not mistaken, this should generate 3 tables, one for each
original entity and a third one for the relation. This 3rd table
should get the primary key from each of the other 2 tables and have
its primary key set as the foreign key that came from the "many" side.
 Despite that, PowerDesigner is generating just 2 tables and placing
the primary key from the "one" side on the "many" table.
 What do I have to do in order to generate a physical model with 3
tables for a one-to-many relationship?
Best regards,
norberto

Only Many-to-Many relationships generate the structure you have been
expecting, i.e. the participating tables and a 3rd 'intersection'
table.
The relationships that have cardinality defined as One-to-Many
generate as FK references between the two tables in the PDM.
Why do you believe that a 1:M relationship that is optional on both
roles should generate the 3rd 'intersection' table?
Have you used any other CASE E/R data modeling tool that did this?
Regards,
G

Hi,

If you have a one-to-many relationship between entities A and B that
is optional on both sides, this means that A may exist without B and
that B may exist without A.
Considering that A is the "one" and that B is the "many", if the
table corresponding to B gets the primary key of A as a foreign key,
this means that whenever there's a B which is not related to A, the
field with the foreign key will have to be null.

If you have a 3rd table (let's call it C) with two foreign keys (the
primary keys from A and B), in which the primary key is just the
foreign key that came from B (the "many" side), there will not be any
nulls. Furthermore, due to integrity constrains on table C, you will
never be able to create two records for the same B entity, because
although each record of C has both foreign keys (from A and B), the
primary key is the foreign key from B, thus the "one-to-many" rule is
supported.

I'm not sure if I made myself clear :).

Regards,
Norberto

[gtss]

 Hi,
 If you have a one-to-many relationship between entities A and B that
is optional on both sides, this means that A may exist without B and
that B may exist without A.
 Considering that A is the "one" and that B is the "many", if the
table corresponding to B gets the primary key of A as a foreign key,
this means that whenever there's a B which is not related to A, the
field with the foreign key will have to be null.
 If you have a 3rd table (let's call it C) with two foreign keys (the
primary keys from A and B), in which the primary key is just the
foreign key that came from B (the "many" side), there will not be any
nulls. Furthermore, due to integrity constrains on table C, you will
never be able to create two records for the same B entity, because
although each record of C has both foreign keys (from A and B), the
primary key is the foreign key from B, thus the "one-to-many" rule is
supported.
 I'm not sure if I made myself clear :).
Regards,
Norberto

Hmmm... almost clear :-)

The first design "pattern", with the nullable FK from B to A, is what
PowerDesigner considers the standard design, so that's what it
generates.

In the second pattern, in the table C, what is the optionality of the
FK column that points to the PK of A?
You do not state this explicitly, although I infer from "... there
will not be any nulls." that your assumption is that both FK columns
in the table C are mandatory.

So, in other words, table C only holds records for the instances of B
that do have a relationship with instances of A.
Also, the relationship from C to B will have to be defined as One-to-
One, as you say: "....will never be able to create two records for the
same B entity, because although each record of C has both foreign keys
(from A and B), the primary key is the foreign key from B..."

Yes, I agree that the second pattern is a design that works, and which
avoids the nullable FK in table B that is a 'feature' in the first
pattern.


Alas, PowerDesigner generates PDM from CDM according to the first
pattern. I do not know whether there is any possibility whatsoever in
PD to customize this, in order to get it to generate according to the
second pattern.
With PD's standard functionality as of now, you'll have to design your
desired pattern directly and explicitly in the CDM or LDM to get it
generated in the PDM.


Regards,
G