1. Third Normal, as in "Database normalization." (Database 101) No offence intended, you said you are a beginner.
2. Right. My "unlimited" stock and prices advantage. No creating a table for each new stock. No creating a table or adding columns (Both time and logically intensive.) for each new stock or price. And just generally FLEXIBILITY without extreme pain! 3. The date and time the price was quoted. 4. Exactly. A good book: Oracle, "Case Method, Entity Relationship Modeling." Good luck! Fred -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of cstrader Sent: Tuesday, August 19, 2008 12:24 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] 2 Questions from a newbie OK, this seems really helpful, but I'm afraid you've lost me a bit. 1. Third normal??? :) 2. Your idea would be to have one table that's relatively static, with the names (and perhaps other data) of the stocks, a 2nd table that holds prices? I'm not sure I see the advantage of this approach. 3. What's "Instance DateTime?" 4. Are you saying that I could use "GROUP BY" to get daily, monthly, yearly, etc? Thanks, very helpful...just what I'm needing! > Third normal says: > > Table: Stock > ID Integer Primary Key, > Symbol Varchar(n), > ... Other stuff .... > > Table: DaylyPrice (I'd call it "Price") > StockID Integer (Foreign key: Stock:ID) > Instance DateTime > Price Integer (Price times 100/1000/10000, Your choice) > > Then you can have virtually unlimited stocks with virtually unlimited > prices. Price sampling can be done at any rate (i.e. seconds, hourly, > daily, weekly, etc.) Your queries would determine "closing price" hourly, > dally, monthly, etc. > > Thoughts to ponder: How to handle stock "splits", Mergers, and ? > > Fred > > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of cstrader > Sent: Tuesday, August 19, 2008 11:53 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] 2 Questions from a newbie > > > OK, cool idea... let me try that. > > > ----- Original Message ----- > From: "Jeff Hamilton" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]>; "General Discussion of SQLite Database" > <sqlite-users@sqlite.org> > Sent: Tuesday, August 19, 2008 12:51 PM > Subject: Re: [sqlite] 2 Questions from a newbie > > >> If all 2,400 tables have the same 9 columns you could try using a >> single table and adding a column for the ticker of the stock and then >> add an index to that column to allow quick lookups based on the >> ticker. >> >> -Jeff >> >> On Tue, Aug 19, 2008 at 9:44 AM, .:UgumugU:. <[EMAIL PROTECTED]> wrote: >>> Hi cstrader, >>> >>> Just send some table structure and the explain what you are trying todo. >>> In my opinion it is not a good way to deal with 2400 tables :) >>> >>> ugumugu >>> >>> cstrader yazm?s,: >>>> I'm just starting with SQLite (from vb.net) and could use some advice. >>>> >>>> I have a set of 2400 (could get substantially higher) tables in a >>>> single >>>> database (each table contains daily stock prices for a different >>>> stock). >>>> Each table has 9 columns (all text for now) and some several thousand >>>> rows. >>>> So far so good. >>>> >>>> I need to read those tables sequentially and operate on them. It takes >>>> about 18 seconds to open each of the tables in sequence. (i.e. loop >>>> through 2400 tables with a select command opening each one) >>>> >>>> First question... does that sound about right in terms of speed? Is >>>> there a >>>> way to store the data that might be faster? The data are primarily >>>> single >>>> precision -- I'm using text format, but perhaps that's not best? >>>> >>>> Second question: >>>> >>>> When I open each table I need to add some blank columns to it. So I >>>> include >>>> some "0 as NewCol" lines to create the new columns with initial 0s. >>>> >>>> However, adding 20 new columns in this manner increases the total time >>>> for >>>> the loop from 19 seconds to 49 seconds. This seems like a long time. >>>> Is >>>> there a faster way to get these empty columns in? >>>> >>>> More questions later I'm sure... >>>> >>>> Thanks in advance >>>> >>>> cs >>>> >>>> _______________________________________________ >>>> sqlite-users mailing list >>>> sqlite-users@sqlite.org >>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>>> >>>> >>> >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
