December 13, 2004 at 4:39 am
Hi,
I have Product and Asset entities. Their relationship is many to many. So I end up having 3 tables. Table Product, Asset and ProductAsset. In short:
Table Product contains product_id and deal_id.
Table Asset contains asset_id and deal_id.
Table ProductAsset contains asset_id and product_id.
Note: there may be more than one deal_id in either Product or Asset table.
If I know a deal_id (@my_dealid), how can I find all the records in ProductAsset that are related to that deal_id?
Can I do: select pa.product_id, pa.asset_id from Product p join ProductAsset pa on p.product_id = pa.product_id join Asset a on a.asset_id = pa.asset_id where a.deal_id = @my_dealid and p.deal_id = @my_dealid
Thanks
December 13, 2004 at 6:21 am
I don't think there is any particular problem with what you have written unless the tables are huge.
I may have this wrong but I'm pretty sure that SQL joins the tables together then works out what is needed from the result of the join. This is why in large databases temporary tables suddenly become viable rather than a performance bottleneck.
I have worked on the basis that
* Joining small tables directly is OK.
* Joining medium tables can be better handled using derived tables.
* Large tables are best preselected into temporary tables, then join the temporary tables.
Of course the definition of small, medium and large is woolier than a very wooly thing.
December 14, 2004 at 8:02 am
Did you say you have a deal_id column in each of product and asset tables? Can 1 product have more than one deal_id? Can 1 asset have more than 1 deal_Id? If so, you need at least one table for deals, and 1 more column in ProductAsset. Even this model is not properly normalized, and depending on the number of rows, you may want to normalize further.
As for performance, IMHO, you should focus more on chosing the correct indexes rather than joining strategies.
P
December 14, 2004 at 9:17 am
I agree with "schleep". I tried your query with some test data of my own devising, and I got the same result without using the ProductAsset table:
SELECT p.product_id, a.asset_id
FROM Asset AS a
, Product AS p
WHERE a.deal_id = p.deal_id
AND a.deal_id = @my_dealid
Working assumptions regarding your data model:
- product_id is unique in the Product table
- asset_id is unique in the Asset table
- deal_id is a single-valued attribute of Product, perhaps indicating in what deal this product was acquired. It is not a multi-valued attribute -- that is, a product is not involved in multiple deals.
- deal_id is a single-valued attribute of Asset, too. An asset is not involved in multiple deals.
- An entry is made in the ProductAsset table for every combination of Product and Asset that occurs in a deal.
Intuited working constraint:
- A deal always includes both product and assets. If not, you might have deal #901 that contains Product #501 and no Asset. The ProductAsset table would contain no entry for this deal, or would contain the Product-Asset pair: #501-NULL.
If all these assumptions are correct, then the ProductAsset table is redundant.
Bob Monahon
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply