thanks for the response. I assume the schema would be like this
CREATE TABLE user( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL); CREATE TABLE main( t long, uid integer, price real, *FOREIGN KEY(uid) REFERENCES user(id)* ); Then I would first read thru my entire data file (about 3 million rows) to get the user and populate the user table and then I would populate the main table. When populating the main table, what is the best way to assign the foreign key, should I do a SELECT inside the INSERT? like INSERT into main (t,uid,price) values (1344343545,(SELECT id from user where user='vberry'),100.00) Is that the proper way of doing it or is there a more simple way? On Fri, Feb 24, 2012 at 8:08 AM, Oliver Peters <oliver....@web.de> wrote: > Am Fr 24 Feb 2012 13:56:47 CET schrieb Rita: > > I was wondering if its better to have a single table or multiple tables >> for >> something I am doing. I have close to 3 millions rows in a single table >> and >> here is how it looks: >> >> t,user,price >> 1330087935,vberry,180.00 >> 1330087935,mson,10.7 >> 1330087935,hpack,780.08 >> 1330087935,parj,80.02 >> 1330088033,vberry,173.00 >> 1330088033,mson,12.7 >> 1330088033,hpack,783.08 >> 1330088033,parj,80.02 >> >> >> I was wondering if there is a better way to normalize this data. And also, >> I will be doing queries like, what is the most recent price for the >> user,mson, and what is the average price for mson in the last 3 days, >> etc... >> >> >> >> >> > create a separate user table > i.e. > CREATE TABLE user( > id INTEGER PRIMARY KEY AUTOINCREMENT, > name TEXT NOT NULL); > > and use id (and not the name) in tables as a foreign key > > greetings > oliver > ______________________________**_________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users> > -- --- Get your facts first, then you can distort them as you please.-- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
