question about datamodel (link table)

[amien]

i have an orderitem table which contains a debtor_address

i have an order table which contains a customer_adress

i have a master table with all adresses. both can link to debtor and customer.

what i could do is to read the address table twice and create a different key for each one (one on debtor address and one for customer_address)

but can i do this using a link table? how would the script look like?

this is what i have now:

order table : AUTONUMBERHASH128(casenr,companycode)                        AS %case_ID  

orderitem table : AUTONUMBERHASH128(casenr,companycode)                  AS %caseitem_ID,

link table:

order table / order item table using CONCATENATE (fieldname are renamed differenly to prevent SYN key)

casenr                                                                    AS %casenr_ID,

companycode                                                          AS %companycode_ID,

AUTONUMBERHASH(casenr,companycode)               AS %caseitem_ID / %case_ID

the result is, when i select a customer name, the debtor names disappear and the other way arround.

What is the proper way to achive this?

Thanks in advanced!

[stephencredmond]

Hi,

If you concatenate the two tables like this, then the joins to customer address and debtor address will be logically on different rows so therefore will do what you are experiencing - select one and the others disappear.

Do you really need to analyse debtors and customers together like this?  I would probably go for having separate address tables.  Use Exists or Keep to reduce the number of records in the Master table to match the relevant customer or debtor information.

Stephen