Results 1 to 9 of 9

Thread: Database design advice

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2004
    Location
    India
    Posts
    517

    Post Database design advice

    Hello Everyone,

    I am just started using SQLite database system for my application. The application is related to stock market data. It will read from a text file and update data in database. Another program will read data from database and do some work.

    The current scene is I have created 1 database for each company. Each company can contain from a hundred thousand records to over three hundred thousand. The number of companies can be around 500. Thus there are 2 scene:

    1) create 500 or whatever number databases and read and write to them. The pro is in case one gets corrupt the others will work. Plus we only need to manipulate records for that company so the number of rows will be around the figure I mentioned above. Con: Most of time I need to read a bunch or all of them so I need to open connection read or write then close and repeat the process.

    2) Have only 1 database and put all the data in it. Pro: managing is easy and we only need to open 1 db connection rather than each connection for scene above. Con: The database can have over fifty million records and that's a good number. Reading or inserting may take time (I ain't sure).

    Currently I open a connection for a db, read or write then close. This happens in loop as each company is a db. Is this method ok?

    Any advice will be appreciated.

    Thank you,

    Cheers,
    GR

  2. #2
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,458

    Re: Database design advice

    A properly designed database shouldn't have an issue with 50 million rows and personally I would much rather work with a single database rather than 500 separate databases.

    If you are needing to deal with all the rows for a particular customer then using a WHERE clause on your SQL will filter it to just the records you need, if you have appropriate indexing this should be a fast operation.

    Unless you are hitting the limits of a single database I wouldn't go the multiple database route as it will add a lot of complexity without many benefits.

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2004
    Location
    India
    Posts
    517

    Thumbs up Re: Database design advice

    Quote Originally Posted by PlausiblyDamp View Post
    A properly designed database shouldn't have an issue with 50 million rows and personally I would much rather work with a single database rather than 500 separate databases.

    If you are needing to deal with all the rows for a particular customer then using a WHERE clause on your SQL will filter it to just the records you need, if you have appropriate indexing this should be a fast operation.

    Unless you are hitting the limits of a single database I wouldn't go the multiple database route as it will add a lot of complexity without many benefits.
    I guess I can try it. By default sqlite creates index automatically on primary key. However in this case primary key will be a combination of 2 columns. Will SQLite also auto create index in this case?
    Last edited by greatchap; Jan 19th, 2017 at 11:21 AM.

  4. #4
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,458

    Re: Database design advice

    I really don't know SQLite that well but I would imagine it is fairly easy to create indexes on columns, http://www.sqlitetutorial.net/sqlite-index/ might be worth a look.

  5. #5

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2004
    Location
    India
    Posts
    517

    Question Re: Database design advice

    Quote Originally Posted by PlausiblyDamp View Post
    I really don't know SQLite that well but I would imagine it is fairly easy to create indexes on columns, http://www.sqlitetutorial.net/sqlite-index/ might be worth a look.
    Thank you and I did read about it. However I have two questions.

    1) Before I create an index I need to check if an index exists or not
    2) Once I create an index when do I need to re-index it. Or does index gets updated automatically as data is added.

  6. #6
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Database design advice

    Quote Originally Posted by greatchap View Post
    I guess I can try it. By default sqlite creates index automatically on primary key. However in this case primary key will be a combination of 2 columns. Will SQLite also auto create index in this case?
    Yes.

    Olaf

  7. #7
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Database design advice

    Quote Originally Posted by greatchap View Post
    I am just started using SQLite database system for my application. The application is related to stock market data. It will read from a text file and update data in database. Another program will read data from database and do some work.

    The current scene is I have created 1 database for each company. Each company can contain from a hundred thousand records to over three hundred thousand. The number of companies can be around 500. Thus there are 2 scene:

    1) create 500 or whatever number databases and read and write to them. The pro is in case one gets corrupt the others will work. Plus we only need to manipulate records for that company so the number of rows will be around the figure I mentioned above. Con: Most of time I need to read a bunch or all of them so I need to open connection read or write then close and repeat the process.

    2) Have only 1 database and put all the data in it. Pro: managing is easy and we only need to open 1 db connection rather than each connection for scene above. Con: The database can have over fifty million records and that's a good number. Reading or inserting may take time (I ain't sure).
    I'd go with 1), if your DBs have a simple Schema (not more than 5 Tables) and
    if the cases are rare, where you have to do a Select across tables of two or more Companies
    (although the latter issue can be solved by attaching other DB-Files to an existing Connection).

    Quote Originally Posted by greatchap View Post
    Currently I open a connection for a db, read or write then close. This happens in loop as each company is a db. Is this method ok?
    Opening a Connection with a simple Schema (< 5 Tables) takes about 1-2msec,
    a midsized Schema 5-30 Tables with a lot of Columns, Indexes, Views, and Foreign-Table-Definitions takes about 5-20msec -
    and really huge DB-Schema-Definitions (DBs with more than 50 Tables) will need perhaps 50-100msec at the DB-Open-stage.

    This is the only thing you will have to consider.
    (in your case, I think the 1-2msec will apply - just do your own tests, and maybe time your
    "loop over the Company-DBs" when all you do in it, is just the Connection-Open-call - just to be sure.

    Olaf

  8. #8
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Database design advice

    Quote Originally Posted by greatchap View Post
    Thank you and I did read about it. However I have two questions.

    1) Before I create an index I need to check if an index exists or not
    2) Once I create an index when do I need to re-index it. Or does index gets updated automatically as data is added.
    Of course an index will be automatically updated when Data is added
    (and therefore too many un-needed Indexes can slow down your "Bulk-Insert-performance").

    As said, in case you have defined your important Columns already as a Primary-Key,
    you don't need to create any extra-indexes for them.

    If those Primary-Keys are a combination of Fields, or in case of single Columns, are not defined as "INTEGER PRIMARY KEY",
    you can optimize further (sparing the RowID-automatic-indexing of SQLite) by using " WITHOUT ROWID" at the end of your Create Table statement.
    https://www.sqlite.org/withoutrowid.html

    Olaf

  9. #9

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2004
    Location
    India
    Posts
    517

    Question Re: Database design advice

    Thank you for your reply.

    My database has 1 table with fields: symbol text ,dt datetime,open real,high real,low real,close real,vol int

    The primary key in on 2 fields i.e. Symbol & Dt. In such a case do you think I should create an index because the primary key is on 2 fields and not one. Plus the row id thing you mentioned, does it apply here (Without RowID).

    I am currently creating 1 index on Symbol & Dt (primary keys).
    Last edited by greatchap; Jan 23rd, 2017 at 01:24 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width