Can anyone help with how to manage/store 3,000 to 5,000 daily pricing data. Should I use one very large table. I will ultimately need to collect info on one of the priced items over many days, months even years. I currently have a master table of identifiers (ID,symbol) and a second table with the following fields (ID,DateAdded, Symbol,Price), I probably don't need the ID field as the key field is really Symbol. Thanks for any help!
-
-
Hi,
Large table in terms of having many Fields isn't the best way to treat data... You can't find your records quickly and many of the fields aren't used in every record...
So when I create a table I evaluate % of charge on the respective Field in depend of full number of records...
The Fields in my tables are nearly 100% used except Fields like Description of operation or Memo field for comments that are usually empty!
So for hard use of your table do the some thing... This economise place on your HDD there is no unused things!
In term of organization, as ID don't use ID of type Text... It's very heavy to seek and search and order... Use only entier number! And the number is a few bytes till the text is much more!!! So this economize space on HDD and energy to treat your information... Imagine only bit per bit every big string passing trought your processors... And you waiting for results ready to beat your computer!
Be carefull!
Have a good luck!
:) -
Thanx for your help. If I will have 3000 to 4000 records per day of prices, would you maintain this all in ONE database or try to break it up? I could just append new daily data. Each record will be very small bytewise, but there will be many.
Thanks again for your help.
Originally posted by PEBComment
-
I would consider using only one database, using more than one (when not absolutely nessesary, I.E. for other product management, etc) is not nessesary, use one but use extensive tables/relationships to manage them and break up the info as much as you can, I.E. dont use duplicate records, or use them the least possible and just add relationships between columns.
This way your traffic of data shouldn't be that slow, remember to use a good interfase to manage the data, and if you program everything correctly you should get a good data-flow traffic.
Greetings,
MiffeComment
-
If your tables are well organized you can keep your data at least for a year no more than 1825000 /365 * 5000/ records! If those records are smaller then 2 Gb you don't have problems!
You have to be carefull with the used forms, indexed fields, relationships and the definied columns...
But those kind of constraints concern each system of database management!
:)Comment
-
My thoughts right now are to maintain a table for each year as long as they don't get too big! Thanks again for your help!
Originally posted by PEBComment
-
Yes this is the thing that I do with my current database...
For each year I have a database to which I connect if I have need!
Only the most important data is transmitted to the new year!
And the current year is the current period....
If you need to do more powerfull queries you can consolidate the years in one database but this wouldn't be your operationnal database!
It will be your database for statistical needs... And there your queries will run slow, slow
But you will know this! This wouldn't enerve your users!
:)Comment
-
See:
http://support.microso ft.com/default.aspx?sc id=kb;en-us;128808Comment
Comment