> Confucius say: "Man who kisses girl's behind gets crack in face."
Hi All !
I made a virtual stock exchange using PHP + MySQL. I'm not a programmer, so I
don't know how well the sistem behaves itself when it comes to performance.
Now, the project:
2 databases: stock & players
Tables in stock:
------------------
CREATE TABLE ring (
id smallint(5) unsigned NOT NULL auto_increment,
data datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
initiator varchar(8) NOT NULL,
titlu char(3) NOT NULL,
cant smallint(5) unsigned DEFAULT '0' NOT NULL,
curs mediumint(8) unsigned DEFAULT '0' NOT NULL,
partener varchar(8) NOT NULL,
status enum('N','D','A') DEFAULT 'N' NOT NULL,
tip enum('V','C') DEFAULT 'V' NOT NULL,
dataex datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
suma int(11) DEFAULT '0' NOT NULL,
PRIMARY KEY (id),
KEY initiator (initiator),
KEY titlu (titlu)
);
This table includes the transactions between players.
CREATE TABLE titluri (
id smallint(5) unsigned NOT NULL auto_increment,
nume char(3) NOT NULL,
valini mediumint(9) DEFAULT '0' NOT NULL,
contracte smallint(6) DEFAULT '0' NOT NULL,
PRIMARY KEY (id),
UNIQUE nume (nume),
KEY nume_2 (nume)
);
This table includes the stocks that are exchanged between players and their
value.
CREATE TABLE users (
id smallint(5) unsigned NOT NULL auto_increment,
nume varchar(8) NOT NULL,
pass varchar(30) NOT NULL,
fullname varchar(30) NOT NULL,
tip enum('U','A') DEFAULT 'U' NOT NULL,
PRIMARY KEY (id),
UNIQUE nume (nume),
KEY nume_2 (nume)
);
This includes the list of players in the game, their passwords, fullname
Tables in players:
--------------------
CREATE TABLE user1 (
id smallint(5) unsigned NOT NULL auto_increment,
data date DEFAULT '0000-00-00' NOT NULL,
suma int(10) unsigned DEFAULT '0' NOT NULL,
stock1 int(10) unsigned DEFAULT '0' NOT NULL,
stock2 int(10) unsigned DEFAULT '0' NOT NULL,
...
stockX int(10) unsigned DEFAULT '0' NOT NULL,
PRIMARY KEY (id)
);
there are also user2 ... userN
Each player (included in stock.users) has a table in players, named after
their name (stock.users.nume). Each player's table has, in the last row, his
current financial situation - money and stock.
The the name of the columns in the tables corresponding each player, (those
named FYI stock1 ... stockX) are taken from stock.titluri.nume.
When a player (playerX) wants to buy / sell stock, I write-lock
stock.ring and players.playerX and after that INSERT / UPDATE the
necessary data. The sistem works. I expect about 100 players. I don't know
how good in terms of performance is this structure, so please tell me your
opinion.
Another question is about expanding the game. If I want to include another
stock in the game, I might be in trouble. For this I have to do something like
this:
1. add a new row in stock.titluri
2. LOCK TABLES stock.users WRITE
3. SELECT nume FROM stock.users
4. UNLOCK TABLES
5. LOCK players.* by building a PHP string "LOCK TABLES stock.users WRITE,
user1 WRITE, user2, WRITE ... userX WRITE";
6. ALTER each table in players, adding a new stock column (the one I added on
step 4).
7. UNLOCK TABLES
The problem I anticipate here is: what if a new player registrate himself
between step 4 and 5? His table won't be included on step 6.
I see the following solution: LOCK stock.users and players.* in one step, but
I can't / don't know how to lock an entire database. Suggestions here?
Thanks
--
Ciprian
> He who laughs last, thinks slowest.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
