Database design - connecting 2 tables

[Eisaz]

hi all,

I was wondering if anyone could push me in the right direction. I am making supplier/factory database.
The database includes Supplier details as well as factory details.
What the database need to do in general, is to create 2 separate tables where one would contain Supplier information + factory (which works with the supplier) information and second table with Factory list Info only. e.g.
I found a way of doing it by setting the relationships between these 2 tables, the problem is, some of the suppliers work with more than 1 factory and some factories work with more than one supplier.

Question is: Is there a way of:
1) while inputign data in form update two tables in the same time and
2) if the same factory is used by other supplier simply link the information by e.g. inputting the factory ID No where the information is required.



I hope it all makes sense. I would appreciate any help.
Thanks,
S

[dcrake]

It sounds like you need a many to many relationship, therefore you will need what is known as a junction table that contains bothe the primary keys form the suppliers table and the primary keys from the locations table.

Then join the respective tables to the relevant fields in the junction table.

David

[Eisaz]

hi David,

Thanks a lot for you quick response. I will try to do it and let know my results.

Regards,
Slawek