Searching Multiple Tables And Displaying The Results.

[Jarris]

Hi.  I'm now getting way too much data in one table (800K+ records) and I'm looking for the best way to have multiple tables (all with the same headings and data types) that can all be searched via the same Datapage.  I've looked at "Views" and "Relationships" in the on-line instructions but can't get my head around either.  Any help would be greatly appreciated.

[Jan]

Hello Jarris,

 

Welcome to Caspio forum!

 

As far as I know, it is better to use one table, from many points of view.

Is it very slow?

 

I can propose some solutions, but it seems they are not very good.

[Jarris]

Thanks for responding Jan.  Yes, it is getting a little slow - around 30 seconds for a search result to come back and I expect that will increase as more data gets added - I add around 150K records a month and that will continue indefinitely.  

My issue is this; if some incorrect data gets uploaded and appended to the original data, then I have two options.  Correcting the mistake manually in the Caspio table (not very viable) or delete ALL the data and upload ALL the corrected data again.  I do Export the data regularly as an MDB file (450MB reduces to around 25MB), but with the data growing daily, I can see this becoming an issue.  Uploading data can take a while.

It would be much simpler if I could have say, a separate table for each three months of data and for the DataPage to search all those tables to return a result.  If an error occurs, then I can simply replace 3 months of data rather than have to upload all of the data into one table again.

[DataCobalt]

Have you thought of using SQL statements?

 

Without knowing more about your data it would be difficult to know what you needs are for searching this database, but if it is a similar item on a regular basis you could use SQL statements and have them pull up items from each table.

 

Such as a tabular report that would have Field_A displayed from each table (each one containing three months worth of data)  It would take some serious playing around to use virtual fields, but with them you could alter the SQL statements to be able to search using them, instead of being stuck with a set field permanently. It would essentially just be picking which field you wanted to pull information from.

 

May not work as a long term solution if you will need to add a new table every 3 months and search all old information. After about 5 or 6 years it would become one LONG tabular report. 

[Jarris]

Thanks DataCobalt.  

Firstly, I must admit to being a total novice when it comes to databases, which is what attracted me to Caspio in the first place.  It took a bit of playing around, but I managed to get a Table and DataPage set up whereby people could use two search criteria on my website, which would then return them nine columns of info where the two search criteria matched two of the defined columns/rows.  So, all the tables will have exactly the same nine columns of data types.   

My problem (as mentioned) is going to be data volume - I'll reach 2M records by the end of the year.  If, for example, some incorrect data gets appended to the one and only table, it will be very problematic to fix.  Despite my best efforts, errors can and have occurred.

Thanks again DataCobalt for the SQL Statements suggestion.  It sounds like that might work.  I'll have to have a hunt around to see how that might be done, as I said, I'm a novice, or find someone that can do it for me.

Cheers