SQLite data storage

[Chris Sheffield]

Thanks everyone for the feedback. Sounds like the general consensus is to use a single table to store all the data, which is the direction I was leaning anyway. I just wanted to make sure performance wouldn't take a hit, but it sounds like that shouldn't be a problem.

Now I get to work on pulling the data from the spreadsheet into SQLite. Should be fun. :-)

Thanks,
Chris


On Mar 21, 2013, at 11:50 AM, Peter Haworth <pete at lcsql.com> wrote:

> Hi Chris,
> Difficult to answer without a little more information about your app.  In
> general, I wouldn't worry about the number of records - "thousands" won't
> be an issue for SQLite.
> 
> I'd say that if the columns are the same on each sheet, one table will
> probably be the way to go - it's not usually a good idea to have tables
> with exactly the same table layout and I'd guess there's some piece of data
> you can use as a key when selecting data to differentiate between what used
> to be on each individual sheet, if that's a requirement.
> 
> After that, it just becomes an issue of standard database design based on
> the data elements and the application requirements.
> 
> If you'd like to send me the spreadsheet offline, I'd be happy to take a
> look and give you some thoughts.
> 
> Pete
> lcSQL Software <http://www.lcsql.com>
> 
> 
> On Thu, Mar 21, 2013 at 9:14 AM, Chris Sheffield <cmsheffield at icloud.com>wrote:
> 
>> I hope nobody minds that I ask this here. While it's not specifically
>> about LiveCode, the database I'm asking about will be used for a LiveCode
>> app. :-)
>> 
>> I need some advice/pointers on how best to store some static "read-only"
>> data in a SQLite database. We're talking potentially thousands of records.
>> I've been given an Excel spreadsheet with 24 sheets containing data to
>> import. There are about 12 fields/columns. The data is separated into 24
>> sheets, but it could potentially all reside in one table in the database
>> (fields are the same on each sheet). The question is, should I do that?
>> Will SQLite bog down after a while? This new app we're working on will need
>> constant access to this database, probably via several open record sets at
>> once. I'm just trying to figure out if it would be best to store everything
>> in one large table, or to split each sheet of data into its own table.
>> Would that be more efficient? Or would it be even better to have each sheet
>> in its own file? Also, are there specific settings/properties I should set
>> on the db to help keep performance as optimal as possible?
>> 
>> Just brainstorming here. Would love to hear opinions, especially if
>> someone out there is a SQLite guru. :-)
>> 
>> Thanks,
>> Chris
>> 
>> 
>> --
>> Chris Sheffield
>> Read Naturally, Inc.
>> www.readnaturally.com
>> _______________________________________________
>> use-livecode mailing list
>> use-livecode at lists.runrev.com
>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>> 
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode