EFFECT (ID, TYPE, VALUE1, VALUE2, VALUE3)
for example
123, TYPE.RESTORE_HEALTH, 15 (over 15 seconds), PERCENTAGE, 15%
when you pool out effect, you simply check its TYPE, and then do needed stuff with VALUES.
Well, that's at least what i usually do.
oh i see now, so if i have multiple possible values, i dont have to have them one by one on the table, like poison and stuff, i just have them as values and use those values according to the type, very interesting.
just a quick example of what i need to be clear:
lets say i have these tables:
users:
pk iduser
name
roles:
pk idroles
role_name
priviledges:
pk idpriv
priv_name
alright so i have an user: iduser=0, name=test
i have roles: 0=admin, 1=moderator, 2=member, 3=guest
so i would have another table to state a relationship:
user_role:
pk iduser_role
fk iduser
fk idroles
up until this point its pretty easy, each user will have one role, but then mr. priviledges joins the party.
we have these priviledges: 0=view, 1=post, 2=edit, 3=delete.
so now we need another relation table and this one is the one i dont quite understand, because using your method, i would have to do the following:
role_priviledge:
pk role_priviledge
fk idrole
fk idpriv
so if i want to give admins full rights, i would have to add 4 elements to that table, like
pk role_priviledge=0
fk idrole=0
fk idpriv=0
then one for fk idpriv=1, then one for 2 and one for 3.
this is the way that role_priviledge table would work, so if possible, i'd like it to work on a more efficient way, like admin has rights 0,1,2,3, all in one element.
the reason why i feel this is a need, is because if i had 100 roles, and 1000 priviledges, the role_priviledge table would be a hell to query and to understand.
I'm not quite sure, it just feels more efficient if u could virtually link a user with his own item table, instead of checking one by one every item the user has gotten on a database lots of users are going to use, lets say i have 1000 users and 1000 items, whenever i wanna see items for user 123, i would have to loop through the entire character_item table, to check for his CID, i am fairly new to the database subject but that just feels unefficient, i guess i'm just thinking like object oriented.
That's why you use a MySQL database. I'm not sure why you're looping through the entire characther_item database anyway?
Joining tables are actually the how relational databases are implemented. Again I recommend reading up on the subject. They are the efficient method of associating tables together when using a database.
Example Scenario
At no point do you have to iterate through the character_item table?
i thought SELECTs were like looping through the entire database, well all your help should help me be on the way, now i suddendly dont feel stucked, and i can go on and keep working on my project, thanks a lot
They have given you the correct answers. Based on your posts it seems you do not know much about Relational databases.
Character (Table)
character_id
name
item (Table)
item_id
name
character_item (Table)
character_id
item_id
amount
Example Entries:
Character
1, Juicebox
2, Rupert
Item
1, HP Potion
2, MP Potion
3, XP Potion
Character_Item
1,1,5 - Row 0 (Character 1 has HP Potion x5)
1,2,10 - Row 1 (Character 1 has MP Potion x10)
2,3,15 - Row 2 (Character 2 has XP Potion x15)
2,1,3 - Row 3 (Character 2 has HP Potion x3)
Executing this SQL:
SELECT * From Character will bring back information for all characters (rows 0 and 1 in Character table)
SELECT * FROM Character WHERE character_id=1 will bring back row 0 in Character Table "Juicebox"
SELECT * FROM Item will bring back all rows in t he item table
SELECT * FROM Item WHERE name='HP Potion' will bring back 1 row for every item named HP Potion
SELECT * FROM Item WHERE item_id=1 will bring back 1 row for item_id 1 (Row 0 - HP Potion)
SELECT * FROM Character_Item will bring back all rows
SELECT * FROM Character_Item WHERE character_id=1 will only bring back row 0 and 1.
SELECT * FROM Character_Item WHERE character_id=2 will only bring back row 2 and 3.
SELECT * FROM Character_Item WHERE character_id=2 AND item_id=3 will only bring back row 2 (you can run something like this to see if a character has a certain item ID)
Here is some basic SQL:
Note: SQL Server and MySQL both use SQL - there may be slight differences in syntax / keywords, however, the concepts are the same.
I advise you to pickup mysql and mysql workbench and work on creating tables and writing queries such as:
SELECT queries
INSERT queries (http://www.techonthenet.com/sql/insert.php)
DELETE queries
i thought SELECTs were like looping through the entire database, well all your help should help me be on the way, now i suddendly dont feel stucked, and i can go on and keep working on my project, thanks a lot
Selects return a "ResultSet" Which is 0 or more rows based on your select statement.
You can add a "WHERE clause" to your select to limit t he data being returned.
SELECT * FROM Character WHERE active=1 <<<Gets all Active Characters
Lets say you had 50 characters, and 10 of these were "banned". The above statement would return 40 rows! Only the # of rows matching the criteria.
With this resultset you can do what you will.
Loop through it in your game, check values, etc etc etc
Steps I believe you should take:
Download MySQL and MySQL workbench
Practice Creating tables and then performing sql commands on those tables (SELECT, SELECT with where clauses, SELECT with inner joins, DELETES, INSERTS) etc etc.
Once you are COMFORTABLE with the above. Then you can start looking into how to execute sql and loop through resultsets in whatever language you are using.
Here is a small PSEUDOCODE example
ConnectionObject Creation
Open Connection
Execute Statement
(Optional)Loop Through Reader & Process Rows
Close Reader/Connection
im not quite sure, it just feels more efficient if u could virtually link a user with his own item table, instead of checking one by one every item the user has gotten on a database lots of users are going to use, lets say i have 1000 users and 1000 items, whenever i wanna see items for user 123, i would have to loop through the entire character_item table, to check for his CID, i am fairly new to the database subject but that just feels unefficient, i guess im just thinking like object oriented.
SELECT * FROM character_item WHERE character_id=123
This is very efficient. Database engines are blazing fast.
SELECT * FROM character
[code to programatically loop and check for user 123]
^INEFFICIENT
SELECT * FROM character WHERE user=123 (this will only return x items. where x is the # of items user 123 has)
[code to programatically loop and assign the returned resultset to user 123's inventory]
^EFFICIENT
http://www.w3schools.com/sql/sql_where.asp Keep hitting next chapter.
Good luck!
i thought SELECTs were like looping through the entire database, well all your help should help me be on the way, now i suddendly dont feel stucked, and i can go on and keep working on my project, thanks a lot
I would advice befriending joins. Main workhorse of RDBM.
--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
--For example, select all character names that have healing potions, by item id
SELECT CH.NAME FROM
CHARACTERS CH
INNER JOIN CHARACTER_ITEMS CHIT ON CH.ID = CHIT.CID
WHERE CHIT.IID = 5
--by name
SELECT CH.NAME FROM
CHARACTERS CH
INNER JOIN CHARACTER_ITEMS CHIT ON CH.ID = CHIT.CID
INNER JOIN ITEMS IT ON CHIT.IID = IT.ID
WHERE IT.ITEM_TITLE = 'Healing Potion'--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
--Select all character names that have no items overall
SELECT CH.NAME FROM
CHARACTERS CH
LEFT JOIN CHARACTER_ITEMS CHIT ON CH.ID = CHIT.CID
WHERE CHIT.ID IS NULLI wont explain difference between joins, so you would go and study them on your own, but have in mind, you shouldn't abuse * to much, select only whats needed and not everything. Don't be afraid to use indexing and avoid doing searches by text as much as possible on classified fields. If you are looking for a Sword of Uberness don't go for "WHERE X.NAME = 'Sword of Uberness'", its slow, make sure application passes classifier ID, because searching for 1337 on indexed not null int field is far faster that doing varchar search.
