Hi guys,

Im making a website back end for a flash game - so far so good

1 x db called test, one table called players one called transactions. Basically I want to track withdrawals / deposits into their player account.

Player table has (field/Example):


id (primkey) 1
plyr_name Fred
real_name Joe Bloggs
access_lvl Banker / Player

------------------------------
transaction table has
id (primkey) 5
Plyr_id(foreignkey?) 1
Trans_type Deposit
Amnt 100
Date 18:00:00 101108
status Complete
Id_Banker(foreignkey?) 2
comments

e.g. So the Id_banker is just the playerID of the banker who completed the deposit, when the player logs in a list of transaction is displayed in a table, instead of Id_banker how can I link it to show the player name of the banker, do i need to create a third table using 'joins'??? not quite sure
Im not quite certain if I am going the right way about this please advise

Thanks, Spike

Just a thought, how about moving the 'access_level' to the transaction table, and eliminate the idea of 'banker id'? The access level could then be used to indicate whether the player_id's transaction was made as a banker or player. It seems silly (to me anyway) to duplicate the 'plyr_name' and 'real_name' fields in the Player table, with the only difference being two id's, and two different access_levels.

That would probably be a quick-and-simple suggestion, but there are other [more involved] ways, of course...

Another way would be to make the access_level a table (see side-note, below), and then make an association table between the Player and access table called player_roles, or something. The player_roles table would have three fields as a triple primary key of an id, player(id)[FK], access_level(id)[FK]. Then the player_roles(id) could be used in the transaction table instead of the player's id field as the foreign key, and this id could be used to retrieve both a player's name, and their access, but [as expected] more joins would be required.

On a side note, I can also see where three fields have the potential to become foreign key lookup values to other tables of their own:
1. access_lvl
2. Trans_type
3. status

HTH. Any questions about this, post another reply.

__________________
Use the force...read the source!!
WYCIWYG -- what you code is what you get!

Just a thought, how about moving the 'access_level' to the transaction table, and eliminate the idea of 'banker id'? The access level could then be used to indicate whether the player_id's transaction was made as a banker or player. It seems silly (to me anyway) to duplicate the 'plyr_name' and 'real_name' fields in the Player table, with the only difference being two id's, and two different access_levels.----------------------------------------

Thanks for the help - Im sorry I dont quite follow - the player table lists all accounts from my game if they are a banker then access-lvl is 'banker' (they have permissions to authorise withdrawals / deposits) a access_lvl 'player' can only put on a request and waits for a banker to process the request.
The banker_ID is the playerid of an account with access_lvl 'banker' who is processing the request from an access_lvl 'player'.

e.g.
Player table stores all user accounts from game - each account either has access_lvl Banker or Player (it is not listed twice??).

If a player logs in he can make a request for a cashout - this is added to the transactions table with 'pending status' currently banker_id is empty as no bankers have authorised.

A banker then logs on and sees the transaction from player clicks to authorise it (this puts his player_id into the transaction table as banker_id so I can see who has processed this players request)
When compelted status is changed to complete.

------------------------------------------------------

I thought about doing seperate tables for these 3 below, and did at first - but - what is the advantage apart from being able to add additional transaction types etc easily? - is it worth adding these tables as theres only going to be a couple of trans types / statuses and 2 access lvls.
1. access_lvl
2. Trans_type
3. status
-----------------------
Im unclear how to reference data a table from a primary key in another table for instance:

the player table has a player_ID so in the transaction table when a transaction is made player_id is inserted -I then use PHP to display the transaction how do I reference e.g. the players name from his player_id in the trans table. Do I use joins to create a new table?



Another way would be to make the access_level a table (see side-note, below), and then make an association table between the Player and access table called player_roles, or something. The player_roles table would have three fields as a triple primary key of an id, player(id)[FK], access_level(id)[FK]. Then the player_roles(id) could be used in the transaction table instead of the player's id field as the foreign key, and this id could be used to retrieve both a player's name, and their access, but [as expected] more joins would be required.----------------------

Based on the info. in the first post:
...I perceived that to imply:
...where the player and real names would be listed twice. Am I missing something?

Well the whole notion of (your word) 'easily' -- is always a plus for me. This is also an area of preference, that's why I said 'potential' earlier. As it seems to stand now, it's probably not such a big deal. It's all up to you -- this is only a support forum, after all, not an international-standards committee. Also, a good upfront design can greatly reduce the effort(s) of rework/refactor/maintenance later.

Other than easier, know that as the table(s) grow, so will their space. A FK reference ID is four bytes of storage (assuming type INT), whereas a VARCHAR(x) field would need to be as large enough x [plus 1 or 2 bytes depending on length see this] to at least hold the largest text data stored. (Anything shorter than x is ok, for it'll only use what space is required, plus the 1 or 2)

So, taking a small example from the transaction table data in the first post, status=Complete (8 bytes + 1) + Trans_type=deposit (7 bytes + 1) (not counting the other field sizes) is 17 bytes. If those were INT [FK] fields, that would only need 8 bytes total for the references -- or less than half of what was needed -- and that only considers one row!
Joining is not another table, but rather a linking notion. The basic query for those two tables to include the name would be:
HTH. Sorry it took awhile responding. Post another reply if needed.

__________________
Use the force...read the source!!
WYCIWYG -- what you code is what you get!

Thanks for the info helped a lot - sorry if my post sounded agressive - its just he way its written.
Thanks again.

No aggressiveness taken, just a difference of perspective (or points-of-view), that's all!

Thanks for visiting GID!

__________________
Use the force...read the source!!
WYCIWYG -- what you code is what you get!