Hello Everyone!

I am developing a web based application for a fantasy stock market (trader game). It allows users to register their details and start the purchasing and/or trading of stock market shares. All users are given an initial amount of virtual cash to startoff with, and they will be playing with stock market shares from the 100 companies from the ftse 100.

I'm having a little trouble with the database design structure....it's very simple but I would just like some help on how I can improve or if I have made a mistake anywhere.

Table 1: User details
This is information such as title, first/last name, dob, address, email, password and etc

Table 2: FTSE 100
This table will contain stock quotes for al the companies of the ftse 100. Details such as epic code, name, high, low, change -p, change -%, open, close and etc

Table 3: Trade Portfolio
This table will hold details of users who have bought shares. Details such as username, epic code and its name, quantity of stocks purchased, purchase price and current price of the stock share and profit/loss.

MY TROUBLE IS WITH TABLE 3.
Do I create a table which will list ALL USERS that have bought shares? Or is their a way of showing all the trades that have been made by a specific individual user?

My Fantasy Stock Market Game will be developed, based on a WAMP (Windows-Apache-MySQL-PHP) platform

I would be most grateful to anyone who can guide me with my challenge and I would be also grateful to anyone who can give me hints and tips which I could use for other aspects of my project.

Thank you

Your table 3 should be for all your users who have bought stocks. It will be what is known as a conjunction table which links between the users table and the stocks table. It will have two main keys, one that is the user's account id and the second which is the stock's id. This will allow you to form a many to many relationship which will allow users to purchase as many different stocks as they want and provide the flexibility for things like a person buying the same stock at two different prices.

Users

1 - User1
2 - User2
3 - User3


stocks

1 - GM
2 - Motorola
3 - Microsoft

Portfolio

id.... userid....stockid.....stockprice....shares

1 1 3 34.32 40
2 3 3 35.15 100
3 2 1 10.00 20
4 1 3 35.10 60

This above data shows that user with id 1 owns 100 shares of Microsoft (40 at 34.32 and 60 at 35.10) and that user 3 has shares of microsoft also 100 shares right at 35.15. User 2 has stock in GM, 20 shares at 10.00.

So how do you get the portfolio for user 1? Simple, you query the portfolio and use userid 1



I hope this makes sense. Enjoy!

"At DIC we be stock trading code ninjas! Except being that we are secretive, we be like Enron also."

Thank you soooo much!! Much appreciated!...Didnt think anyone was actually going to help....but you have just saved me from a lot of agony and stress!!

'Keep doin wot u doin'! :-)

Thank you for the previous advice on my project.

I need a little help again with table 2....I've found a website which allows me to download free real (delayed) data of stock quotes for all companies in the ftse100: data is downloaded as a .csv file. I need to import this csv file and insert the data into table 2 of my database.

I have done research on the net and I have found out how to import the csv file into mysql database....but is there a script or a function that will automatically download the csv file from the site every 10mins and automatically update the table if changes occur???

the website is www.moneyextra.com who provide the T15 data as a csv file. the exact url for the file is http://www.moneyextra.com/stocks/ftse100/ftse100.csv

For automated/timed things you will need access to the cron.
see this post:
http://www.dreamincode.net/forums/showtopic32601.htm

The script that read the csv file (either directly from the remote website or after it is pushed to your system somehow) should be able to open the file and store all the data in your database.
Then the only thing you need to take care of is that it runs on a regular basis, see the refered post.

Thank you for the information!!! :-)

HI,

I'm looking for a script to create a Fantasy Stock Exchange.
Anyone know where can I find it ?

Thanks,
Julien