I have a very unusual situation and wanted to get some thoughts from people on how they might handle this. Normally when you build a product database you have a table of items and in that table you might have fields that have the following information:
And then you might create another table with manufacturers and then use the PK_ID field to fill the manufacturers field in the table above.
However, what if multiple manufacturers make an item. I have a situation where a table consists of about 5.9 million rows. The first table has all the basic fields in it like Name, Size, Weight, Color and that is consistant across all the items regardless of manufacturer. However, there can be a number of manufacturers and they all have unique data of their own that must be displayed in a single result set. So a release date might be a field, since maybe the manufacturers released the items at different times. So far I have had to have two 5.9 million row tables join each other by a PK and a FK in order to have multiple manufacturers for given item, but this is incredibly slow.
Anybody ever encounter this issue before? Any thoughts on how this should be setup.