Relationship with to 2 rows ?!

Toad Data Modeler
1

Hi ppl, I have been trying to associate an ID from 1 table to 2 rows of another table ( for example ShipingAddress and BillingAddres ), how I can do this ?!

Thanks in advance

2

Hello Thamathar,

I’m not entirely sure what do you mean. I assume you want to create a relationship between a column in one entity and multiple columns in another entity? Please try to describe your problem a bit in detail.

Regards,

Lukas

3

Hi Thamathar,

you have several possibilities.

A) In Settings | Options change value of Automatic FK Mapping to Always Show Dialog or Disable.

fk-mapping-options.png
fk-mapping-options.png727×472 28.4 KB

  1. Then draw two relationships from parent to child entity (let the tool to create keys. In result two ID attributes will appear in child entity)

  2. Rename one ID in child entity to BillingAddress and the other ID to ShippingAddress.

B) Use Foreign Keys mapping of existing attributes:

  1. Create parent entity with primary key ID.

  2. Craete child entity with attributes ShippingAddress and BillingAddress

  3. Draw first relationship and map the parent attribute to ShippingAddress.

  4. Draw second relationship and map the parent attribute to BillingAddress.

C) Keep everything as is and:

  1. Create first relationship from parent to child.

  2. Rename ID in child entity to BillingAddress.

  3. Create second relationship from parent to child.

  4. Rename ID in child entity to ShippingAddress.

Regards,

Vaclav

4

Yes, I have an primary key on one table that is an FK on another table on 2 columns.

The table that is the PK is the Address, and on the other table I want to associate on ShipingAddress and BillingAddress.

I was hable to put the relation on the ShipingAddress but not on the BillingAddress unless I put another relation to between the tables ( sorry for my english not my native languange ).

5

I did try the A option, but no Dialog did apear to select the child and such :confused:

6

Hi,

can you send us a full screenshot of the two tables please? On your last screenshot I see identifying relationship but foreign key attribute only and I think that won’t work.

BTW: to map parent attribute to existing child attribute, edit your relationship and change the attribute from ID to BillingAddress in relationship properties:

mapping.png

Regards,

Vaclav

7

The image of the full tables:

I want to use the Relationship 16 from AddressID to the ShippingAddress and BillingAddress without creating a new relationship just for it, would like to use the same for both

8

Hi,

it is not possible to use the same relationship. Two relationships should be created.

mapping-two-rels.png

BTW: on your screenshot there are probably some other attributes hidden in the Customer entity.

Regards,

Vaclav

9

Thanks, I think it would be possible for do it.

10

Wanted to do something like this ( as we can do on MySQL ) it as one relationship from 1 PK on the table Address to 2 colums on the Customer

11

Hi,

it can be modelled in MySQL Workbench, that's true.
I created the sample model in Workbench, generated SQL code and executed it. As a result error 150 was thrown by MySQL.

code.png
code.png1062×871 63.9 KB

I used InnoDB engine.

Regards,

Vaclav

12

Thanks for all the help :D, I did create 2 relationship for it :smiley:

13

My pleasure :slight_smile: