SQL Server Performance Forum – Threads Archive
Table design (search with ranking)
Hello! I am trying to design some tables to solve this problem. The main goal is to have a very good performance, but still have a good design. I am trying to build a search function with a ranking system. The search function can be used to search for cars/hotels or whatever. Each car/hotel have x numbers of attributes and each attribute can have a value. For example Attributes for a car can be: make, year, type.Values for the attribute make can be: Audi, BMW, Ford
Values for the attribute type can be: Cab, Coupé, SUV It should be possible to make a decision of how important a attribute is. These values can be.
– Not at all
– Some
– A lot
– Required If the choice for a specific attribute is ‘not at all#%92 it should not affect the points at all.
If the choice for a specific attribute is ‘some#%92 it should give 50% points if the car has that attribute value.
If the choice for a specific attribute is ‘A lot#%92 it should give 100% points if the car has that attribute value.
If the choice for a specific attribute is ‘Required‘ it should vive 100% points and it will require that the car has this attribute value to even show in the result list. If we have 3 attributes and the one that makes the search do something like this: Make: Audi, Ford (a lot)
Year: 01,02,03 (some)
Type: (not at all) The max points in the result should be 100. And since it is more important (for the searcher) that the make is correct than that the year is, that will generate 50% more points.
That will give us that if the make is a audi or a ford it will give that post 75 points. If the year is right (01,02,03) it will give the post 25 points. So if the car is a audi or a ford and is created in either 01,02 or 03 it will give that car 100 points.
Since the one that makes the search has set the type to ‘not at all#%92, that will not affect the points at all. This point calculation also means that this: Make: Audi (a lot) – 33.3 points
Year: 01,02 (a lot) – 33.3 points
Type: SUV (a lot) – 33.3 points Is the same thing as
Make: Audi (some) – 33.3 points
Year: 01,02 (some) – 33.3 points
Type: SUV (some) – 33.3 points Since we will always return 100 points. But if we change the importance of any of the attributes, it will also affect the possible points. Make: Audi (some) – 25 points
Year: 01,02 (a lot) – 50 points
Type: SUV (some) – 25 points Anyone have any ideas of how to design the tables? Remember that it should be simple to add new attributes and attribute values. The performance is also very very important.
You don’t need a scale of 1-100, it’s probably easier to go with a ‘weighted’ calculation – each attribute has a value of 100, and you multiply each attribute with the factor that the user assigns (required = 1, a lot = 0.75, some = 0.50, not at all = 0 – or 0.25). Next you order by the multiplied values, starting with the "required" columns, then the overall sum of the multiplied values per row, then the "a lot" columns, and finally the "some" columns – all in descending order.
But the required and a lot should return the same amount of posts. Its just that the required differs in that way that the attribute value is required to even show in the results. That means that when a user says that a attribute is required then it will generate a inner join or a where-statement like, where attribute1 = ‘some value’. The ‘not at all’ should not affect the points at all, wich means that it will not matter if the car has that attribute or not. Another way to see it is that that attribute is not even set in the search criteria.
Then add a subquery in the WHERE clause that verifies that the COUNT(*) for the required matching attribute values is equal to the number of these attributes. The ordering would still work, but you lose the entities that do not have all required attributes.
]]>