mlatazamlata
New Member
Here is my DB Structure:\[code\]CREATE TABLE IF NOT EXISTS `UserItems` ( `id` bigint(20) unsigned NOT NULL auto_increment, `user_id` int(10) unsigned NOT NULL, `item_id` int(10) unsigned NOT NULL, `qty` int(11) NOT NULL default '0') ;CREATE TABLE IF NOT EXISTS `UserEquippedItems` ( `user_id` int(10) unsigned NOT NULL, `user_item_id` bigint(20) unsigned NOT NULL);CREATE TABLE IF NOT EXISTS `UserFriendEquippedItems` ( `user_friend_id` int(10) unsigned NOT NULL, `user_item_id` bigint(20) unsigned NOT NULL); \[/code\]UserItems keeps all the item inventory with quantity.
let say if I have 5 item (item id 123456). then the entry will be(null, $userid, 123456, 5).the qty is the quantity of all the entity with the same item_id.However, some Users may equip the item. Some do not. If they equip it, it will be an entry in UserEquippedItems table. Also, users' friends can equip the user's item too. Sample Data:\[code\]UserItems:id, user_id, item_id, qty( 1, 4567, 123123123, 5)( 2, 4567, 100010001, 2)( 3, 4567, 100010099, 1)UserEquippedItems: (user_item_id is UserItems.id)user_id, user_item_id( 4567, 1)( 4567, 2)UserFriendEquippedItems(user_item_id is UserItems.id)user_friend_id, user_item_id( 4100, 1)( 4100, 3)\[/code\]So, how can I find out the quantity of items that are equipped?and how can I find out the quantity of items that are NOT equipped ?Side Story: Before, we have each individual UserItems as a single entry in the DB. e.g. for item_id = 123123123, if I have 5 of them, i have 5 entries in the DB. But then, our DB grows like crazy to 4 million UserItems records. that's why instead of having 5 entries, we only have one entry in the UserItems table, with the qty field for keep track how many in total. I don't know if it is the right approach, but I hope it can cut down 75% of the DB size.Also, it was the query to get the unequipped items:\[code\] SELECT Items.id, count(UserItems.id) as numCount FROM UserItems INNER JOIN Items ON UserItems.active=1 AND UserItems.item_id=Items.id AND Items.active=1 AND UserItems.user_id=$userId WHERE NOT EXISTS ( SELECT UserEquippedItems.user_item_id FROM UserEquippedItems WHERE UserEquippedItems.user_item_id= UserItems.id) AND NOT EXISTS ( SELECT UserFriendsEquippedItems.user_item_id FROM UserFriendsEquippedItems WHERE UserFriendsEquippedItems.user_item_id= UserItems.id) GROUP BY Items.id\[/code\]Of course, this query doesn't work with the new schema.
let say if I have 5 item (item id 123456). then the entry will be(null, $userid, 123456, 5).the qty is the quantity of all the entity with the same item_id.However, some Users may equip the item. Some do not. If they equip it, it will be an entry in UserEquippedItems table. Also, users' friends can equip the user's item too. Sample Data:\[code\]UserItems:id, user_id, item_id, qty( 1, 4567, 123123123, 5)( 2, 4567, 100010001, 2)( 3, 4567, 100010099, 1)UserEquippedItems: (user_item_id is UserItems.id)user_id, user_item_id( 4567, 1)( 4567, 2)UserFriendEquippedItems(user_item_id is UserItems.id)user_friend_id, user_item_id( 4100, 1)( 4100, 3)\[/code\]So, how can I find out the quantity of items that are equipped?and how can I find out the quantity of items that are NOT equipped ?Side Story: Before, we have each individual UserItems as a single entry in the DB. e.g. for item_id = 123123123, if I have 5 of them, i have 5 entries in the DB. But then, our DB grows like crazy to 4 million UserItems records. that's why instead of having 5 entries, we only have one entry in the UserItems table, with the qty field for keep track how many in total. I don't know if it is the right approach, but I hope it can cut down 75% of the DB size.Also, it was the query to get the unequipped items:\[code\] SELECT Items.id, count(UserItems.id) as numCount FROM UserItems INNER JOIN Items ON UserItems.active=1 AND UserItems.item_id=Items.id AND Items.active=1 AND UserItems.user_id=$userId WHERE NOT EXISTS ( SELECT UserEquippedItems.user_item_id FROM UserEquippedItems WHERE UserEquippedItems.user_item_id= UserItems.id) AND NOT EXISTS ( SELECT UserFriendsEquippedItems.user_item_id FROM UserFriendsEquippedItems WHERE UserFriendsEquippedItems.user_item_id= UserItems.id) GROUP BY Items.id\[/code\]Of course, this query doesn't work with the new schema.