My windows app requires reading data loggers/sensors. I have site information, attributes, coefficients, etc. I am contemplating using a SQLite db instead of a flat file format. Currently, I have many combo boxes, text fields, etc. to hold site data. I write this data to a XML file, where I can recall it, etc. My loggers collect data, and when read by the user, a .csv file is created (“site info” date/time, xxx.xx, xxx.xx) for reporting. My folder structure currently looks like this:
City of Washington (Folder)
City of Richmond (Folder)
------>Site 1 (Folder)
----------->XML Site attribute file (id, location, gps coords, logger attributes, sensor attributes, etc)
----------->.csv Data File (stored data from logger)
----------->Site Map (image)
----------->Site Photo
----------->PDF File (charts and text reports from stored data and site attributes)
------>Site 2
------>Site 3
City of Redding (Folder)
My question; would you use a SQLite DB for this or stay with a flat file format. If SQLite, then how would you go about keeping the projects (each city) separate? Would you create a discrete database for each project (City)? The flat file format as outlined above keeps things separate, portable, and human readable but leads itself to errors (i.e., files missing, user error, etc.)
All of the data is time based on an interval and therefore is not really arbitrarily transactional. I am just wondering if a DB would be an advantage for data handling/reporting in this particular case. If so, why?
City ( washington etc)
each city has a unique ID
city name
Sites
each site has a unique ID
each site also holds the CITY ID that it is in
it also has location, gps coords, logger attributes, sensor attributes, etc
Site Documents
has "document" has a unique id
has the SITE ID
tag (is this the photo, map image, PDF or something else)
image data or PDF
It mimics the structure you had, could easily be exported as XML and should be easy to find, insert & update data
Now this is a "quick & dirty forum DB and there may be reasons to split one or more of these into more tables
However IF you want “human readable” (ie/ is that a design criteria or not?) then the XML and folder is not horrible
But it does lend itself to the mistakes you noted
Thanks Norman. I do not need human readable. Its just funny how (often) something’s biggest advantage tends to be it’s biggest disadvantage as well. So, my question is… I am providing services for City A, B, C. I have one big database. City A wants their “database”. How is this done (in general) without just exporting .csv files, pdf files, etc. for them.
What problem are you trying to solve by moving to a database solution? Given that you already have a working file structure, have you considered using a VirtualVolume instead?
I am new to XOJO and have put in considerable time building what I have. However, I have no problem moving to a DB if it makes sense. The reasons i would move to a DB:
File handling - User will not be able to save a photo, data, or other file into a random folder on their computer. I currently default each of their SAVES to a specific path, but it does not prevent them from changing the path.
Speed - Handling many thousands of records and quickly performing calculations and/or drilling down into specific date/time ranges.
Creating Charts and Text Reports - I just assume it will be handled easier in a DB
Robust Queries
After studying up on SQLite, it seems to be a lightweight, fast solution that begs to be used in an application like this. However, I am not an expert…
Here are my thoughts. a) A database will almost always be faster. b) If you ever need to filter or query your data a database will most definitely be faster. c) The database solution does not involve you ever having to figure out how to read and read the data , maintaining any referential integrity, record ID’s, etc, because that’s what databases do exceptionally well. Thus resulting in less code for you to maintain.
I’ve been doing Xojo coding for many years now and I can count on one hand where the project was better off using a flat file rather than a database and even that project it could have used a database.
[quote=168228:@John Marsh]I am new to XOJO and have put in considerable time building what I have. However, I have no problem moving to a DB if it makes sense. The reasons i would move to a DB:
File handling - User will not be able to save a photo, data, or other file into a random folder on their computer. I currently default each of their SAVES to a specific path, but it does not prevent them from changing the path.
Speed - Handling many thousands of records and quickly performing calculations and/or drilling down into specific date/time ranges.
Creating Charts and Text Reports - I just assume it will be handled easier in a DB
Robust Queries
After studying up on SQLite, it seems to be a lightweight, fast solution that begs to be used in an application like this. However, I am not an expert…
John[/quote]
OK, sounds good. The performance of the database will heavily depend on the design, so I’d suggest you take some time out from coding and bone up on database design principles as well as do some prototyping of the database - meaning populate your database with lots and lots of data to see how it will perform.
[quote=168175:@John Marsh]Thanks Norman. I do not need human readable. Its just funny how (often) something’s biggest advantage tends to be it’s biggest disadvantage as well. So, my question is… I am providing services for City A, B, C. I have one big database. City A wants their “database”. How is this done (in general) without just exporting .csv files, pdf files, etc. for them.
Again, Thank you,
John[/quote]
For any city you could export a separate database (duplicate then remove everything not theirs or create a new one & move only their data into it)
Personally I’d keep it in one big db rather than one per city since you will still be managing “files” otherwise
And it makes it impossible for YOU to try & find trends, similarities and other commonalities between cities if you wanted to
Thanks guys! All makes sense. Now, (like @James Dooley said); I have to bone up on SQLite and DBs in general … Damm!tt Taking apart Eddies Electr., or other examples and working with them may be a good start.
@Peter Fargo : Yes easy read, thanks for link. This is the first cut of the DB I created to play with. If anything looks goofy, please let me know. It is all logical (to me). The “data” table is the .csv file I will bring in from the logger. I will populate it with data outside of XOJO, play with it, then go to XOJO and tweak as necessary.
FWIW… reduce the risk of corruption by keeping defined length fields separate from the variable length fields. That is to say the BLOBs should be in a table of their own, and even if there is only a one to one relationship. It will save on a few fronts, but most importantly reduction of risking corruption. So a table for site and another table for site_photo (with MapPhoto and SitePhoto fields moved across from site to site_photo) for instance.
Taking apart Eddies Electr., or other examples and working with them may be a good start.