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?
Please give me a database layout
Moderator: General Moderators
Re: Please give me a database layout
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
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
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.
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
only that?.... that is nothing.... and you can multiply that several times more and still being nothing....infomamun wrote: For 30days it needs 7500x30 rows. ...
answer is in my previous post