Greetings!
I have two tables: OpenProjects and OpenJobs. OpenJobs have jobs that
belong to one unique project (ProjID). OpenProjects are projects that have
one project fathers a bunch of jobs. The design below is found in a localDB
on the user's PC and also on a SharedDB file in a server that another 8
users also connect. The SharedDB handles the uniqueness of the project ids
as well as the job ids. Also, please excuse my bad design, as this started
out of frustration and need to keep track of projects, etc.
The tables are created this way:
CREATE TABLE OpenProjects (id integer primary key, ProjID integer,
createDnT, unique(id));
CREATE TABLE OpenJobs (id integer primary key, ProjID integer, Task,
unique(id));
OpenProjects has data as such,
1,1,'2011-10-31 23:24:14'
2,2,'2012-12-27 10:56:43'
...
...
123999,123999,'2013-11-26 10:08:53'
lots more data, but, just showing you a little bit...
OpenJobs has data as such,
1,1,'a'
2,1,'b'
3,1,'c'
4,1,'d'
5,2,'z'
6,2,'c'
7,2,'f'
8,1,'g'
9,2,'h'
...
...
1000222,123999,'a'
Lots more data, but just showing you an idea of the sample...
I know I can do,
select * from OpenJobs where ProjID = 2;
and get all the OpenJobs ids that belong to project id 2. But, this is
taking a long time. Long time means people are complaining and, it should
be much faster, as this is a very small DB (less than 2G). What I would
like to do, but it does not seem possible or I don't know how to do, is to
have a table that has just the Jobs that belong to one project in one
record. Now, the number of records in a project varies, so it is not static
(as this may involve multiple languages that have the same tasks, but the
uniqueness is the job id and the language). I know I can create a table for
each project and push the ids to that table, i.e..
CREATE TABLE p1 (id integer primary key, unique(id));
INSERT OR REPLACE INTO p1 VALUES(select id from OpenJobs where ProjID=1);
CREATE TABLE p2 (id integer primary key, unique(id));
INSERT OR REPLACE INTO p2 VALUES(select id from OpenJobs where ProjID=2)
...
etc.
but that would make the DB with lots of tables and I don't know if Sqlite
can handle millions of tables. So, is there a way to have one table where
one id (record) has multiple integers as well as variable in number?
Thanks for your help.
josé
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
