Best approach

[gwboolean]

I have two tables. One is a vendor table and the other is a part table. Each record in each of the tables is identified by a unique 9 digit part number (both tables derive their part numbers from the same source). So each part can have one or more vendors (source). Each vendor can be used in one or more part. I am want to have a relation between these two tables. I would like to know what is the best practice for relating records in the two tables that can be many-to-many.

What I have done is created a third table that contains two fields. Field one holds part numbers for parts and field two holds part numbers for vendors. The relationships can be seen below (barely):



Is this the best way to deal with two tables that can be related many-to-many or is there a better approach that I should take? My database is contained as a dataset in a Visual Basic application. There is no issue that I can find in doing this, but I would like to know what is the best practice.

[pbaldy]

That's what I'd do. It's often called a junction table.

[gwboolean]

Thanks, sounds good to me.