Topic: Using the rows of one table as the columns of another

I am creating an application where a user will enter objects and rate different attributes of that object. However, I also want the user to be able to sort those attributes by priority, so that I can offer them a weighted list of their objects.

Eventually I envision a drag and drop interface for sorting the attributes based on which is most important to you. You will then rate those attributes on a scale of 1-5 for each object you add. In human terms it would be like saying, "House A has 2 bathrooms and 2 bedrooms. House B has only 1 bathroom, but it has 3 bedrooms. It's not that important to me to have 2 bathrooms, but it's very important to me to have 3 bedrooms, so house B is on the top of my list."

The only way that I can think of to design this is by having 2 tables: one table for the objects, with a column for each attribute's rating - then another table for just the attributes and their priorities, but this seems like it will be difficult to maintain because if I want to add an attribute, I need to add it to both databases.

Is there a way to use the rows in the attribute prioritization table as the columns of the object list table? Or is there some better way to design this?

I hope I'm not totally confusing.


Last edited by jtnatoli (2011-08-15 16:26:11)

Re: Using the rows of one table as the columns of another

Your post is somewhat abstract so I'm not entirely sure as to what you're asking.  Here's a stab at an answer, though:

You would probably need two tables...  one for House attributes and one for User preferences.

You will then have to code the logic to (i) parse thru the User's preferences to create an on-the-fly query to (ii) query the database to find the most popular matches.  Google spends lots of $$'s per year on perfecting their search algorythms to essentially accomplish this.