Page 1 of 1

Please give me a database layout

Posted: Mon Aug 16, 2010 11:14 pm
by infomamun
Hi there
suppose I have a site which show stock quotes. We only have 3 companies in our stock exchange for illustrating the query .
The three companies are Bank-A, Bank-B and Bank-C. So every day we have to show the data like this format and at the same time have to store in database for displaying them on user's search at later:
=======================================
stock quotes on 01.08.2010
company | Last Price | Open | Low | High
Bank-A | 1200 | 1000 | 900 | 1250
Bank-B | 2000 | 2500 | 1800 | 2050
Bank-C | 1500 | 1800 | 1400 | 1600
=======================================
stock quotes on 02.08.2010
company | Last Price | Open | Low | High
Bank-A | 1300 | 1200 | 1250 | 1350
Bank-B | 2500 | 2000 | 1900 | 2550
Bank-C | 1300 | 1500 | 1400 | 1300
=======================================
stock quotes on 03.08.2010
company | Last Price | Open | Low | High
Bank-A | 1400 | 1300 | 1200 | 1600
Bank-B | 2600 | 2500 | 2500 | 2650
Bank-C | 1350 | 1300 | 1200 | 1200
======================================


Those were the combined display of all companies on each date. But I want to show individual company's price date wise also. So from those tables I have to pull out data like this:
========================================
Company Name: Bank-A
----Date---- | Last Price | Open | Low | High
01.08.2010 | 1200 | 1000 | 900 | 1250
02.08.2010 | 1300 | 1200 | 1250 | 1350
03.08.2010 | 1400 | 1300 | 1200 | 1600
========================================
Company Name: Bank-B
----Date---- | Last Price | Open | Low | High
01.08.2010 | 2000 | 2500 | 1800 | 2050
02.08.2010 | 2500 | 2000 | 1900 | 2550
03.08.2010 | 2600 | 2500 | 2500 | 2650
========================================
Company Name: Bank-C
----Date---- | Last Price | Open | Low | High
01.08.2010 | 1500 | 1800 | 1400 | 1600
02.08.2010 | 1300 | 1500 | 1400 | 1300
03.08.2010 | 1350 | 1300 | 1200 | 1200
=======================================

What should be the database table layout so that using only one format of table, I can show both combined display of all company's price and date wise individual company's price separately?

Re: Please give me a database layout

Posted: Tue Aug 17, 2010 10:38 am
by mikosiko
database/table modeling doesn't have nothing to do with the way you decide to display the information... in your case you will have only one table with fields:

date, company, lastprice, openprice, lowprice, highprice

how to display the stored information is your choice and you can do it in many different ways

Re: Please give me a database layout

Posted: Tue Aug 17, 2010 6:59 pm
by infomamun
I meant what will be the best way to store the data so that it will create least pressure and costs as least as possible cpu usage while processing to fetch the data?

Because in practical, there are more than 250 companies instead 3. So when you store the data for one day in a single table and will query for the datewise data of a particular company (e.g table# 4, 5 & 6 of my example) it has to do a lot of work if I want one month data for a company. For fetching only one date, it has to search more than 7500 rows. For 30days it needs 7500x30 rows. So, whatever you consider the best way, please give me one example according to your choice.

Re: Please give me a database layout

Posted: Wed Aug 18, 2010 9:07 pm
by mikosiko
infomamun wrote: For 30days it needs 7500x30 rows. ...
only that?.... that is nothing.... and you can multiply that several times more and still being nothing....

answer is in my previous post