Aggregating many user created items into one "general" item

I'm building a site where users can inventory items and apply various attributes to it, eg. photos, urls, comments, etc.

I have a database structure of three tables: users, entries, associations.

The tables have the following fields:

users

id | joined | email | salt | password

entries

id | created | creator | type | value

associations

id | created | creator | type | node1 | node2

Here's a breakdown of the site function:

Users adding items to their inventory

All user-created items go in entries with a type of 'item'. A row is added to associations with type 'possession', node1 users.id and node2 entries.id. This associations row is how I would (using INNER JOIN entries) pull and display a user's inventory (not just pu开发者_运维知识库lling all entries where creator = users.id, because a user may create an item they don't own).

Adding attributes to items

This part is what seems to throw off everyone I explain things to. An "attribute" is really just another item. In this way, it basically renders a user-created free-form hierarchy. E.g., You may 'tag' For Whom The Bell Tolls with "Book", and Book is another item (whether or not it's in the user's inventory matters not). To makes this work, I just add another row to the associations table with type 'tag' and node1 entries.id (parent item) and node2 entries.id ('tag' or child item). Remember than an entry may also be a url, comment, photo, etc, it would just depend on entries.type. Now I can pull all an items attributes. Eg, all photos: ($item_id = page I'm looking at) "SELECT * FROM entries INNER JOIN associations ON associations.node1 = $item_id AND associations.node2 = entries.id AND entries.type = 'photo'.

I can use a similar query to pull all an item's comments, it's url, whatever. This allows me to create a fluid system of associations between items, items and their owners, items and comments, comments and comments (replies).

My question is, once I have many user created entries of an item eg., "MacBook", what would be the best way to merge, aggregate, amalgamate or however else you like to call it, all those individual items into one general item, so that all these pieces of data created by users can be one knowledge chunk, if you will.

Again, I'm not so worried about users entering "mac book" "Apple Macbook" etc. En masse, those users are just doing it wrong and won't effect the community.

Basically, if a user that didn't own "MacBook" did a search and landed on the MacBook page, they would see the most popular tags, some photos (random, popular, whatever, that's trivial), comments about it, most popular URL, etc.

Also, thanks so much for taking time to read my confusing and elaborate description! :)

---

Have a table called "Tags", which would have a unique Name field. Whenever a user enters a new Name, it's added there.

Every Item should be linked to that table. This sounds like a many items to one tag arrangement, so you wouldn't need an Item_Tags table, just a foreign key Tag_Id in the Items table.

For Comments, you just have a Comments table which links to that table.

To display only 4 photos, you do a SELECT of photos that are joined to that name (presumably, they're photos of Items that have that Name) and LIMIT 4

For other/similar design patterns, do a search for questions related to tags, like this one.