current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite
1

I am developing a small application in C++ and using PostgreSQL as back-end database. Along with other tables in my database in have a "projects" table. Based on each primary key of this table a new table is dynamically added in my Database.

Example:

Suppose the projects table contains following 3 rows:

--------------------------------
| Id |Other Columns Goes here   |
--------------------------------
| 1  |                          |
--------------------------------
| 2  |                          |
-------------------------------- 
| 3  |                          |
--------------------------------

So in this case i also have following three more table Table1, Table2, Table3

Now you might notice that the table names are generated by appending projects.Id at the end of fixed string i.e "Table". It might also be possible that for some projects no table is generated.

Example:

Suppose the projects table contains following 3 rows:

--------------------------------
| Id |Other Columns Goes here   |
--------------------------------
| 1  |                          |
--------------------------------
| 2  |                          |
-------------------------------- 
| 3  |                          | 
--------------------------------

So in this i might found only following two tables in my database: Table1, Table3

Now i simply need to get all the valid projects. For this currently i am using following algo:

//part1
SELECT * FROM Projects Table
get the projects info one by one from the results of above query and store them in new instance of my Custom Class Project
Store the above instance in some contianer e.g Vector say vProjects
//part 2
For each Project p in vProject
if (TableExist(p.Id))
Store p in a new container say vValidatedProjects
Note: The TableExist() method execute the following query:
SELECT COUNT(*) FROM pg_tables WHERE tablename = 'Table"+ p.Id + "'"

Now every thing is working fine as expected but !!! the program executes very slow just because of the second part of above algo, if we have one thousand projects the TableExist() method is also called thousand time and each time this method is called a new query is executed which slow downs the program :(

The solution in my mind is some such thing

//part1
SELECT * FROM Projects Table
WHERE a table exist angainst projets.Id
get only those projects info for whom a dynamic table exist. From the results of above query and store them in new instance of my Custom Class Project
Store the above instance in some contianer e.g Vector say vProjects.

Now in this way only one query did the job for us rather than N+1 Queries (Where N is no of rows in Projects Table) But i don't know how do i write such a query that returns the above results. Please help me in acheiving this.

share|improve this question
5  
"Based on each primary key of this table a new table is dynamically added in my Database." - Sounds like a bad design. Adding more data shouldn't require creating new tables. Could you change the design or is that not an option? –  Mark Byers Sep 29 '11 at 8:06
    
@MarkByers : I totally agree with you, but i have no other option because i am working on some one else design and i have to live with it :( –  Jame Sep 29 '11 at 9:21

2 Answers 2

up vote 2 down vote accepted

Changing the design would be the best solution.

If that is not an option, then you could change the second part:

//part 2
For each Project p in vProject
if (TableExist(p.Id))
Store p in a new container say vValidatedProjects

Note: The TableExist() method execute the following query:
SELECT COUNT(*) FROM pg_tables WHERE tablename = 'Table"+ p.Id + "'"

by, first adding a new boolean column in projects table (lets name it projects.TableExists )

Then, run your current TableExist() function one and populate that column. In addition, chnage the code that creates table for a project, to also update that column and the code that deletes a table to also update the column accordingly.

Then your second part would be:

//part 2
For each Project p in vProject
if (p.TableExists)
Store p in a new container say vValidatedProjects

Note: The TableExist() method will not be used any more
share|improve this answer
up vote 1 down vote

I would rather have one table with project_id in it and do all selects with where project_id = .... That would result in better table statistics and the table optimizer will make a better job.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.