Buyers and sellers - one table or two?

[jsinger]

Not sure if this is a newbie question or not. I used to be pretty good at Access but it has been many years, so....
I need to set up an application to match up buyers and sellers.
Each buyer has a list of things he wants to buy, and each seller has a list of things he wants to sell.
I want to match up buyers and sellers based on how many items they have in common - for instance, seller #17 wants to sell cats, dogs, hamsters and guinea pigs. Buyer #6 wants to buy guinea pigs, turtles, hamsters, goldfish and cats. They have 3 items in common so they are a pretty good match.
For some reason I can't decide conceptually how to set up the database - should buyers and sellers be in the same table? It seems as though they should be in two different tables, but I can't figure out what the relationship between the tables should be.
Ideas?
TIA

[John_G]

You have a many-to-many relationship here. I think you need 5 tables:

tblSeller
tblSellerProduct ' each seller has one or more products
tblBuyer
tblBuyerProduct ' each buyer is looking for one or more products

The fifth table is the join table, where you would join tblSellerProduct and tblBuyerProduct on product name or code

You could have a separate table for products, where you list product details, and use product code in the others.

Determining best matches on multiple products will require some thought, but it is certainly possible.

[orange]

I'm not sure of the details, but here is a draft model that may be helpful.
Adjust as necessary to meet your requirements. This model has a common product table which may not be realistic.

Good luck

[jsinger]

Yup, this is definitely the right direction. The multiple match-ups will require some thought, though. Thanks to both of you.