First post here so hope somebody can be of some help.
I have a table of product information from manufacturers, which has a number of child tables etc. It's basically a collection of tables, related, that contain product information for around 100,000 products.
We also have a table with a list of products that are for sale. Of course, every row isn't going to match so I can't create a PK->FK relationship.
Does anybody have any ideas on a design solution? I'm pretty sure the solution is very simple and I've just overlooked it.
Thanks for your reply. I solved the problem, was pretty much staring me in the face; by setting up an intermediate entity between the items for sale, and the table with the product information that had the 2 FK columns. Thereby the searches commence on the product catalogue and if additional information is needed an INNER JOIN can be used through the intermediate entry which contains the corresponding rows. I'm not over-joyed about adding another relation but as every search on the products table won't result in the join being used (e.g. this tuple will only ever be used when additional information is needed) it's not so bad.