[web] sql begginer, is it possible to store an array in a db?
Author
i'm just starting to use sql (sql 2005 express with visual studio) & was wondering if there was a way to store say an array of int in a single field (i have a monster table wich has a LootTable column in wich i'd like to store multiple numbers wich correspond to different item.ItemID is this possible? if not how would you handle storing loot tables knowing that the number of item in a loot table can be diferent based on the monster & can even change over time)
SQL Server 2005 can consume XML data as a native datatype, so if you can serialize your int array to an XML fragment, you can store it directly in a data row.
Of course, you can store the array as a chunk of binary data but then you need to store the length and stride of the data explicitly.
If robustness of the implementation is important, I would recommend using a separate table for the Loot entries and associate each entry with the ID of the monster that is carrying it.
Of course, you can store the array as a chunk of binary data but then you need to store the length and stride of the data explicitly.
If robustness of the implementation is important, I would recommend using a separate table for the Loot entries and associate each entry with the ID of the monster that is carrying it.
The standard way is to store one array element per row in a table. Of course, you can use blob's if your SQL implementation supports that. Or you can think of some forth and back conversions to a single field, e.g. converting each int to a string and concatenating them all separated by any non-number character (e.g. a comma).
Author
niko i saw that xml datatype but sounds a bit overkill for what i want to do (since i'll prolly be editing the db by hand in vstudio too i would have to make a separate program just to edit the loot tables instead so i can serialise to xml) but i'll look into this if all else fails
how would you go about doing that? you mean 1 table per loottable wich contains 1 itemid per row? wouldn't it cause a massive slowdown to have a huge numbers or table (would be like 1 table for items 1 table for monsters & 500+ table for loot tables with each table having an id as it's name?)
edit err or did you mean change my monster table to have lootslot1 lootslot2 lootslot3 columns instead of having a single loottable column? i guess that could work but wouldn't allow for different loot table sizes (or maybe i could create like 10 slots & just default em to NULL? but am i right to assume that NULL take some room too & having 10more column in a 5column table would increase the db size quite a bit?)
thanks for the help!
Quote:Original post by haegarr
The standard way is to store one array element per row in a table. Of course, you can use blob's if your SQL implementation supports that. Or you can think of some forth and back conversions to a single field, e.g. converting each int to a string and concatenating them all separated by any non-number character (e.g. a comma).
how would you go about doing that? you mean 1 table per loottable wich contains 1 itemid per row? wouldn't it cause a massive slowdown to have a huge numbers or table (would be like 1 table for items 1 table for monsters & 500+ table for loot tables with each table having an id as it's name?)
edit err or did you mean change my monster table to have lootslot1 lootslot2 lootslot3 columns instead of having a single loottable column? i guess that could work but wouldn't allow for different loot table sizes (or maybe i could create like 10 slots & just default em to NULL? but am i right to assume that NULL take some room too & having 10more column in a 5column table would increase the db size quite a bit?)
thanks for the help!
Note that when having several arrays in memory you have another information, namely the (adress of the) array itself. Mapping this 1:1 to SQL would yield in too many tables, right you are. Hence, spend the LOOT table another column that stores an array identification, e.g. the number of the monster. Such a table then defines a relation between the monster table and the item table.
If you have to guarantee the order of items per array, it may be also senseful to store an index in yet another column. Then a query can be equipped with a sort operation.
If you have to guarantee the order of items per array, it may be also senseful to store an index in yet another column. Then a query can be equipped with a sort operation.
Author
Quote:Original post by haegarr
Note that when having several arrays in memory you have another information, namely the (adress of the) array itself. Mapping this 1:1 to SQL would yield in too many tables, right you are. Hence, spend the LOOT table another column that stores an array identification, e.g. the number of the monster. Such a table then defines a relation between the monster table and the item table.
If you have to guarantee the order of items per array, it may be also senseful to store an index in yet another column. Then a query can be equipped with a sort operation.
i'm a bit confused here if i understand well how would you define the relation beetween the monster & the item table with an ID? (& an id of what array if i can't have arrays in sql?) not quite sure i understand what you meant
also i only need a way to store the loot tables (having a 1:1 sql to my program representation is no biggy i don't need to store them as an array in my prog i'm planning to do something like randomly drop 1 item so i'll query the loottable with random & only return 1 item my only concern is how to store it)
The general mechanism of relations in a database is as follows: Assume a table A which provides, besides others, a column named ID. This column stores a number which is unique w.r.t. all stored numbers in that column. That means that the number of the ID column can be used to identify just 1 row in table A.
Now assume a table B in which a column REFERENCE is available. Storing herein a number that appears in column ID of table A can be interpreted as a relation of the row in B to the belonging row in A, similar (but not identical) to an index.
One could now create a table ITEM with a column ITEM_ID, a table MONSTER with a column MONSTER_ID, and a table LOOT with the columns ITEM_REF and MONSTER_REF. Filling in table LOOT with appropriate IDs, one relates rows in the table ITEM (i.e. Item instances) and rows in the table MONSTER (i.e. Monster instances). If you then want to know all Items related to a given Monster, then you start a query on LOOT with MONSTER_REF == the Monster's ID, and gets as result a list of rows, where each ITEM_REF can itself be used to get the belonging row from ITEM where ITEM_ID == ITEM_REF. (AFAIK, there is a possibility to conveniently perform these actions in 1 step.)
In my previous reply I stated "an id of the array" since I assumed 1 array per monster, so that monster and array define a 1:1 relation, and the monster's id would be synonymous to the array id. But perhaps I misunderstood why you would have 500+ tables of loot? I expected this due to 500+ monsters ...
Now assume a table B in which a column REFERENCE is available. Storing herein a number that appears in column ID of table A can be interpreted as a relation of the row in B to the belonging row in A, similar (but not identical) to an index.
One could now create a table ITEM with a column ITEM_ID, a table MONSTER with a column MONSTER_ID, and a table LOOT with the columns ITEM_REF and MONSTER_REF. Filling in table LOOT with appropriate IDs, one relates rows in the table ITEM (i.e. Item instances) and rows in the table MONSTER (i.e. Monster instances). If you then want to know all Items related to a given Monster, then you start a query on LOOT with MONSTER_REF == the Monster's ID, and gets as result a list of rows, where each ITEM_REF can itself be used to get the belonging row from ITEM where ITEM_ID == ITEM_REF. (AFAIK, there is a possibility to conveniently perform these actions in 1 step.)
In my previous reply I stated "an id of the array" since I assumed 1 array per monster, so that monster and array define a 1:1 relation, and the monster's id would be synonymous to the array id. But perhaps I misunderstood why you would have 500+ tables of loot? I expected this due to 500+ monsters ...
Author
Quote:Original post by haegarr
The general mechanism of relations in a database is as follows: Assume a table A which provides, besides others, a column named ID. This column stores a number which is unique w.r.t. all stored numbers in that column. That means that the number of the ID column can be used to identify just 1 row in table A.
Now assume a table B in which a column REFERENCE is available. Storing herein a number that appears in column ID of table A can be interpreted as a relation of the row in B to the belonging row in A, similar (but not identical) to an index.
One could now create a table ITEM with a column ITEM_ID, a table MONSTER with a column MONSTER_ID, and a table LOOT with the columns ITEM_REF and MONSTER_REF. Filling in table LOOT with appropriate IDs, one relates rows in the table ITEM (i.e. Item instances) and rows in the table MONSTER (i.e. Monster instances). If you then want to know all Items related to a given Monster, then you start a query on LOOT with MONSTER_REF == the Monster's ID, and gets as result a list of rows, where each ITEM_REF can itself be used to get the belonging row from ITEM where ITEM_ID == ITEM_REF. (AFAIK, there is a possibility to conveniently perform these actions in 1 step.)
In my previous reply I stated "an id of the array" since I assumed 1 array per monster, so that monster and array define a 1:1 relation, and the monster's id would be synonymous to the array id. But perhaps I misunderstood why you would have 500+ tables of loot? I expected this due to 500+ monsters ...
yes i meant 500+ tables of loot because of 500+ monsters but i at 1st understood you meant i should create 1 loot table for each monster (each monster being a row not a table) i already have item.ItemId & monster.MonsterId (btw when i said loot table to be clear i meant loot table as in a list of loot a monster can drop not table as in the sql term) i think i understand what you meant you means something like
monster table:
monsterid loottable
10 150
item table:
itemid itemsname
530 "whatever"
200 "ubber item"
loot table
monster_ref item_ref
10 530
10 200
& i could select all item ref where monster ref=10 & all item where itemid= itemref is this what you mean?
if so guess that'd work
Quote:Original post by ranakor
i could select all item ref where monster ref=10 & all item where itemid= itemref is this what you mean?
Yes, that is what I mean (except that I don't know for what column loottable in table monster is good for). As said, AFAIK there is a syntax of query where these both steps can be performed at once, but also AFAIK not all SQL dialects support it.
Author
Quote:Original post by haegarrQuote:Original post by ranakor
i could select all item ref where monster ref=10 & all item where itemid= itemref is this what you mean?
Yes, that is what I mean (except that I don't know for what column loottable in table monster is good for). As said, AFAIK there is a syntax of query where these both steps can be performed at once, but also AFAIK not all SQL dialects support it.
err my bad i mixed it with monsterid (i meant to use lootable where i used monsterid since i use monsterid for something else but actually your remark made me think i could merge both)
anyway thanks for all the help cookies inc
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
