Design problem

[Christo Yssel]

Hi,
I am not sure how to solve the following problem:

I have 3 tables, Suppliers, Adresses and Clients
The address table must store addreses from suppliers and clients. A Supplier
can have more than one address, postal and lets say delivery (same for
clients). There is a one-to-may relationship between supplier and address.
Normally you would just have the SupplierID as a foreign key in the address
table and set up a one-to-many relationship between supplier and address.
The same for clients. It would work fine if I created two address tables,
one for suppliers and one for clients, but I would like to use only one
address table and link both (clients and suppliers) to this one table. I am
having problems setting up the relationship with all 3 tables.

Thanks
Christo

 

[Albert D.Kallal]

Just add another field called ClientID to the address table.

Make sure that the ClientID and the SupplierID DO NOT have a default value
(such as 0).

Since ClientID would be null when you attach the reocrd to Suppplier, it
should work....

 

[Christo Yssel]

I thought about doing just that, but I wanted to use the same field (kind of
ageneric field) to link either client or supplier like companyID, without
adding a field for each table. I will try it out.

Thanks