Two fields on a same table pointing (associating) to a single field on another table

[#1]

Hi members of this Forum !

Say that you have a Client record, with a two Cities field.

One field is the City where the Client was BORN. The other field is the City where the Client lives. On those fields we have the CITY CODE.

On another table, we have the CITY CODEs and the CITY NAMES.

How to associate that ? Do we have to duplicate the CITY Table ? Is this the best approach ?

IOW :

CLIENT_TABLE :

CLIENT_ID

CLIENT_NAME

BORN_CITY_CODE

ADDRESS

CITY_ADDRESS_CODE

----------

CITY TALBE

CITY CODE

CITY NAME

Another approach would be to inquiry (LOOKUP) the CITY ADDRESS name during load. But that's way too slow and cumbersome.

Thanks in advance for your help !

[#2]

Well I've prepared a test prototype (a simple QVW).

There inside we need to find a way to associate the CODE_CITY field both to CITY_BORN_CLIENT field and CITY_ADDRESS_CLIENT field. Cause we need to know the NAME_CITY for both of them. And, of course they can be different for the same client.

[johnw · Accepted solution]

There are two basic approaches that I've used in similar cases.

1. Duplicate the city table to have a City Born table and a City Address table. That's a good approach if you want to be able to deal with both cities independently, such as finding all clients born in Paris and currently in London.

2. Create a linkage table between client and city, with a linkage type on each record. For your sample data:

Name, Type, City
ANN, ADDRESS, NEW YORK
ANN, BORN, BOSTON
JOHN, ADDRESS, WASHINGTON
JOHN, BORN, WASHINGTON
MARY, ADDRESS, BOSTON
MARY, BORN, NEW YORK

Well, technically the linkage table would just have codes for the client and the city, but the above is easier to read in an explanation. This approach is good if you want to know all information associated with a particular city. So you select New York, and you can see that Ann lives there, and that Mary was born there, at the same time. If a chart only wants one or the other, just use set analysis to look at that specific relationship. It's also good if users will only be looking at one type of relationship at a time, allowing charts to serve dual purposes, rather than needing a born city chart and an address city chart, for instance.

[rwunderlich]

If it is a single attribute you are associating, I would move the City Name to the CLIENT table using either mapping or join. See attached example.

-Rob

[#5]

Hi !

It was nice to know about the MAP command :

MAP CITY_BORN_CLIENT, CITY_ADDRESS_CLIENT USING CITY;

I knew the APPLYMAP function but this command is very practical too.

Thanks !

[#6]

Well, the duplicate table is the approach I had used before I knew the answers from my Post.

I used the QUALIFY *; / UNQUALIFY *; commands to insulate both tables.

Thanks for your help !