Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The question is pretty broad and a little vague, my answer may be inclusive of considerations you've already made.</p> <p>In order to do the comparison logic you need three things; the product, the ratings of that product and some method to logically group products for comparison. Granularity with proper indexes is usually best.</p> <p>Here is an example of the tables/indexes I would create:</p> <pre><code>Create Table Product (ProductID Int, ProductName Varchar(256), etc) Create Table Groups (GroupID Int, GroupName Varchar(256), etc) Create Table ProductGroup (ProductID Int, GroupID Int) Create Table RatingType (RatingID Int, RatingName Varchar(64)) Create Table ProductRatings (ProductID Int, RatingID Int, RatingValue Varchar(32)) Create Clustered Index ix_Product_pID On Product (ProductID) Create Nonclustered Index ix_Product_pID_pName On Product (ProductID, ProductName) Create Clustered Index ix_Groups_gID On Groups (GroupID) Create Nonclustered Index ix_ProductGroup_gID_pID On ProductGroup (GroupID, ProductID) Create Clustered Index ix_RatingType_rID On RatingType (RatingID) Create Nonclustered Index ix_ProductRatings_pID_rID On ProductRatings (ProductID, RatingID) </code></pre> <p>Putting Clustered Indexes on Product, Group and RatingType make sense as their ID columns will be their identity columns so you don't have to worry about the physical ordering since they will be sequential. ProductGroup and ProductRatings on the other hand can receive any records in any order at anytime so putting clustered indexes on them would slow down inserts and cause faster fragmentation.</p> <p>To perform the logic done in the query in the linked example you would do something like this:</p> <pre><code>Create Proc GetProductComparisonRatings (@productID Int, @ratingID Int) As Declare @columns Nvarchar(Max), @SQL Nvarchar(Max); ;With DistinctCols As ( Select Distinct p.productName From ProductGroup pg1 Join ProductGroup pg2 On pg1.groupID = pg2.groupID Join Product p On pg2.productID = p.productID Where pg1.productID = @productID ) Select @columns = Coalesce(@columns + ',','') + '[' + productName + ']' From DistinctCols Order By Case When p.productID = @productID Then 0 Else 1 End; -- This will put your product that was the basis of the comparison as the leftmost column Select @SQL = ';With baseRecords As ( Select pg2.productID, pr.ratingValue, Count(1) As cnt From ProductGroup pg1 Join ProductGroup pg2 On pg1.groupID = pg2.groupID Join ProductRatings pr On pg2.productID = pr.productID And pr.ratingID = ' + @ratingID + ' Where pg1.productID = ' + @productID + ' Group By pg2.productID, pr.ratingValue; ) Select ratingValue, ' + @columns + ' From baseRecords br Join product p On br.productID = p.productID Pivot (Sum(cnt) For p.productName In (' + @columns + ')) pivot'; Exec sp_executeSQL @SQL; </code></pre> <p>This takes it a step farther and limits the ratings comparison to a specific ratingType; some examples could be (durability, size, overall). If you only have one ratingType it will still work fine. You can edit or change it however you want.</p>
    singulars
    1. This table or related slice is empty.
    1. POProducts comparision database schema
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USEric J. Price
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COThis is not i meant. There must be a comparisions table that customer's comparisions can be saved. And then we want to list the most compared products or in a product information form we want to show 'this product is the most compared with another product'.
      singulars
      1. PO
      1. USramazanulucay
    2. COCan you please provide the schema of the proposed comparisons table? There are many ways you could do it and it will be more productive to work within your conception thus far; if there are problems with it then I can make suggestions at that point.
      singulars
      1. PO
      1. USEric J. Price
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload