[SOLVED] question on my structure

[kryppienation]

OK i am having a problem. First i will explain what it is I'm trying.

 

CASE: each user should be able to store 100 inventory items.

 

So i have 4 tables to do this, i have left out many of the columns in my illustration just because none of them are used.

 

1. users table - this table uses iid to connect to the inventoryonhand table

 

2. inventoryonhand - in this table will be the users 100 items. I may have though this out wrong but I'm open to better suggestions. In this table i have 201 columns. the first column is iid which links back the users table. for each item the user has, i have made an item column and also a dbvalue column. you can see better in the illustration, but it goes: iid item1 dbvalue1 item2 dbvalue2 item3 dbvalue3.... and so on all the way to 100.

 

My reasoning for this was my items all come from differant tables, for this example i have added the fish table. the fish table has an inventory sort key to link it to the last table which is the inventorysort table.

 

3. the item tables, these tables are used to whole all the values of the items. in this example, i am using the fish table, but there are many other tables.

 

4. inventorysort table - i made this table to be able to tell the difference between what an item is that a user has. sid =1 name = fish dbvalue = 1. so all of the fish in the fish table will have the dbvalue of 1. so if there was a fish in the inventoryonhand table, it might look like this....

 

iid    item1        dbvalue1          item2            dbvalue2

2    fishname          1          2nd fish name          1

 

 

 

 

now.... here is the problem.... i don't know how to use this data i guess. This must be the wrong way to do this. can someone help me figure out a better way or tell me how to manipulate this data.... like say someone sells item1... how will i make it go to null and make sure that slot get's used up again before item50 gets used up. It's weird because everytime a user buys an item, i have to update 2 values, one with the item's name, and the second with the item's dbvalue so that i can display it later.... please anyone any suggestions?

 

[attachment deleted by admin]