June 13, 2009 at 6:27 am
I have a product table with information from my products and my competitors products. I'd like to create a compatibility reference table between my products. the best way is to create a table with two field reference the same sku column in the product table?
June 13, 2009 at 3:59 pm
Hello,
I believe you are trying to model the relationship between your company’s products and the competitor’s products – correct?
Presumably each of your company’s products can have multiple competitors. I would also guess that a single competing product can be a rival to more than one of your products.
In this case you have a many-to-many relationship. This indeed you would want to represent via a “Resolver Table” i.e. no direct Foreign-key relation ship between the In-House-Products and Competitor-Products. Instead the Primary-Keys of these two Entities would be Attributes (columns) in the Resolver Table.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
June 14, 2009 at 4:03 pm
Nicely done John -good to know we still have good data modelers out there; I've nothing else to add.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 14, 2009 at 6:39 pm
Thanks for the reply.
What John Marsh said is true. I have a product table and a competitors table. I made a compatibility table with two column. ( productID, competitorsID ) for this. I think is that what John said. ( correct me if I am wrong ).
Another question. I thought about inputing all data products ( in-house and competitors ) in the same product table. I would have only two table.
Product Table ( with data from in-house products and my competitors ).
Compatibility Table ( productID, productID ).
Is common to have data from these two tables (products+competitors) merged in a single product table?
What Would You suggest?
June 15, 2009 at 7:15 am
nalmir.hugo (6/14/2009)Is common to have data from these two tables (products+competitors) merged in a single product table?
:w00t: Nope.
The list of your-company's-products is one entity.
The list of your-competitor's-products is another entity.
nalmir.hugo (6/14/2009)What Would You suggest?
Follow John's directions. 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 15, 2009 at 10:25 am
Hello again,
I have a product table and a competitors table. I made a compatibility table with two column. ( productID, competitorsID ) for this. I think is that what John said. ( correct me if I am wrong )
That is what I meant.
As Paul has stated it is best to keep In-House-Products and Competitor-Products as separate Entities (Tables).
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
June 15, 2009 at 11:11 am
:-)thanks John and Paul. It's good to have a place to share concepts about DB Designs.
July 13, 2009 at 1:12 pm
You can then create a view as a UNION ALL of the those two tables and use that for simplicity.
SELECT * , 1 as 'party' FROM myproducts
union all
SELECT * ,2 FROM myCompetitor
the last column to distinguish your items from the rivals. THis is just a simple idea, I'm sure you can build something more elaborate.
----------------------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply