Some SQL / db Questions...

[fowlplay4]

Here's how I would do it:

Characters

ID - Account

1 - Toran
2 - Toran2

Index on Account

Items

ID - Itemname

1 - Apple
2 - Orange
3 - Banana
4 - Grape

Index on Itemname

CharacterItems

ID - CharacterID - ItemID - Quantity

1 - 1 - 1 - 2
2 - 1 - 2 - 1
3 - 2 - 3 - 4
4 - 2 - 4 - 3

Index on CharacterID
Index on ItemID

Note: ID columns are auto-increment columns. So they just go 1, 2, 3, 4, etc. as you add rows to the table.

On login/character load, you would do a query to load all their item information with at least query and store it in a clientr flag for quick-reference.

When you're making changes (i.e. adding items), you would make them to the database first then reflect that in their clientr flags.

[Torankusu]

Originally Posted by fowlplay4

Here's how I would do it:

Characters

ID - Account

1 - Toran
2 - Toran

Index on Account

Items

ID - Itemname

1 - Apple
2 - Orange
3 - Banana
4 - Grape
5 - Sword

Index on Itemname

CharacterItems

ID - CharacterID - ItemID - Quantity

1 - 1 - 1 - 2
2 - 1 - 2 - 1
3 - 2 - 3 - 4
4 - 2 - 4 - 3

Awesome, that makes a bit more sense.

One thing I am wondering however, is say that for example I have a sword [itemid - 5], and I have a user's character that owns two of them.

CharacterItems

ID - CharacterID - ItemID - Quantity

1 - 1 - 1 - 2
2 - 1 - 2 - 1
3 - 2 - 3 - 4
4 - 2 - 4 - 3
5 - 1 - 5 - 2

Simple enough,
But what if I wanted to offer an option to enchant one of those two swords?
Would this be viable?

CharacterItems

ID - CharacterID - ItemID - Quantity - Enchantments

1 - 1 - 1 - 2 - Null
2 - 1 - 2 - 1 - Null
3 - 2 - 3 - 4 - Null
4 - 2 - 4 - 3 - Null
5 - 1 - 5 - 1 - str+1
6 - 1 - 5 - 1 - Null

Or is there a better alternative?

Thanks again for the help, ill see if I cant get this set up tonight to test with.

[fowlplay4]

Originally Posted by Torankusu

Awesome, that makes a bit more sense.

One thing I am wondering however, is say that for example I have a sword [itemid - 5], and I have a user's character that owns two of them.

Simple enough,
But what if I wanted to offer an option to enchant one of those two swords?
Would this be viable?

Or is there a better alternative?

Thanks again for the help, ill see if I cant get this set up tonight to test with.

That's how I would do it, except call it mods, store it as an array, and parse the array when the item is loaded to apply it to the item/clientr flags.

You could put enchantments in it's own table though. I.e.

ID - CharacterItemID - Enchantment - EnchantmentValue

1 - 5 - strength - 1

[Torankusu]

Originally Posted by fowlplay4

That's how I would do it, except call it mods, store it as an array, and parse the array when the item is loaded to apply it to the item/clientr flags.

Yeah, specific modifications was more or less what I eas going for, just thought the enchantment would be simpler to explain.

Would the storing it in an array part follow this model:

CharacterItems

ID - CharacterID - ItemID - Quantity - Modifications (better name..)

1 - 1 - 1 - 2 - Null
2 - 1 - 2 - 1 - Null
3 - 2 - 3 - 4 - Null
4 - 2 - 4 - 3 - Null
5 - 1 - 5 - 1 - {str, 1}
6 - 1 - 5 - 1 - {str, 2}, {dex, 1} /same character , same item id, it has 2 modifications..giving str +2 dex +1..

You could put enchantments in it's own table though. I.e.

ID - CharacterItemID - Enchantment - EnchantmentValue

1 - 5 - strength - 1

wouldnt this essentially mean that all items that the character owns with an item id of 5 would take on these chsracteristics? Just trying to make sure I read this right. That might be useful for something else im trying to do, but at the moment I am trying to have a single item id have the ability to have modifications , sometimes multiple modifications, done to it...

Thanks for the help.

[fowlplay4]

Originally Posted by Torankusu

wouldnt this essentially mean that all items that the character owns with an item id of 5 would take on these chsracteristics?

Yep, using a separate table would be the more 'normalized' way to do it though. You could have a 100 enchantments for the same item if you wanted.

The tricky part is deciding how you want to query and load the information. I would probably do:

1. Load characteritem data
2. Load and apply enchantments using array of character item ids

I.e.

SELECT CharacterItems.*, Items.*
FROM CharacterItems, Items
WHERE CharacterID = 1 AND Items.ID = CharacterItems.ItemID;

SELECT CharacterItemID, Enchantment, EnchantmentValue
FROM Enchantments
WHERE CharacterItemID IN (1, 2, 3)

I wrote a function that generates an escaped IN clause with an array of data:

PHP Code:

// usage: generateInClause("column", {1,2,3});
public function generateInClause(column, items) {
  temp.clause = column @ " IN (";
  temp.last   = items.size() - 1;
  for (temp.i = 0; temp.i < items.size(); temp.i++) {
    temp.item = items[temp.i];
    if (temp.i == temp.last) {
      temp.clause @= "'" @ escapestring2(temp.item) @ "'";
    } else {
      temp.clause @= "'" @ escapestring2(temp.item) @ "',";
    }
  }
  temp.clause @= ")";
  return temp.clause.link();
} 


[Torankusu]

Originally Posted by fowlplay4

Yep, using a separate table would be the more 'normalized' way to do it though. You could have a 100 enchantments for the same item if you wanted.

The tricky part is deciding how you want to query and load the information. I would probably do:

1. Load characteritem data
2. Load and apply enchantments using array of character item ids

I.e.

SELECT CharacterItems.*, Items.*
FROM CharacterItems, Items
WHERE CharacterID = 1 AND Items.ID = CharacterItems.ItemID;

SELECT CharacterItemID, Enchantment, EnchantmentValue
FROM Enchantments
WHERE CharacterItemID IN (1, 2, 3)

I wrote a function that generates an escaped IN clause with an array of data:

PHP Code:

// usage: generateInClause("column", {1,2,3});
public function generateInClause(column, items) {
  temp.clause = column @ " IN (";
  temp.last   = items.size() - 1;
  for (temp.i = 0; temp.i < items.size(); temp.i++) {
    temp.item = items[temp.i];
    if (temp.i == temp.last) {
      temp.clause @= "'" @ escapestring2(temp.item) @ "'";
    } else {
      temp.clause @= "'" @ escapestring2(temp.item) @ "',";
    }
  }
  temp.clause @= ")";
  return temp.clause.link();
} 


oh wow, thanks a lot.

I feel better knowing that what I wanted to do is in fact possible, and will post some results in a few days when I have a day off to actually write up the functions.

Was not sure exactly how to store the information
such as
-modifications, stages, enchantments, etc
that would be unique to:
-a specific character (or even just account..)
-and a specific item a character owned
(even if they owned multiples of that exact item, that did not share those same modifications, stages, and/or enchantments)

but this gives me a lot of information to structure around. :]

thanks a lot.

I should have asked outright, but is there no real downside to storing information in the column for modifications or enchantments like this? :

ENCHANTMENTS
{str,1},{dex,2}

and accessing it similar to an array:

example = {str,1}, {dex,2}

echo(example[0][0]); // str
echo(example[0][1]); // 1
echo(example[1]); // "dex,2"

I won't be able to write anything up and test it until I have a day off, but I'm trying to gather enough info to set up something the best and most correct way, and so far you've been a great help.

thanks again.

[fowlplay4]

Originally Posted by Torankusu

I should have asked outright, but is there no real downside to storing information in the column for modifications or enchantments like this? :

ENCHANTMENTS
{str,1},{dex,2}

and accessing it similar to an array:

example = {str,1}, {dex,2}

You would use: {{"str",1},{"dex",2}}

Using an array would make it harder to query against for statistical purposes, but if it's just being used to load/save then you would be fine doing it that way.

I.e. Calculate how many items that have more than 5 strength enchanted exist.

SELECT COUNT(*)
FROM CharacterItems, Enchantments
WHERE Enchantments.CharacterItemID = CharacterItems.ID
AND enchantment = 'str' AND enchantmentvalue > 5;

If you design your system properly the only part that cares how that information is loaded and stored should be easy to change from a column in an array, to it's own table if you decide.