Proper database table design?

[Fangbeast]

A fair while back, I designed a recipe manager for myself and with InfraTec kindly doing the import routines for the various formats (Because I didn't have a clue), a reasonable program took shape.

However, 600,000 recipes later, the monolithic table design is seriously slower than Idle's distastefully abused sheep (I have the pictures!!). I mean, slower than the goat he tried to gene splice onto the sheep to give him more friction (hehehehehe).

Does anyone have an idea of better database design for databases of this size?

Never have done anything more complicated myself. But it's time I did!

[idle]

I can't help with databases right now but I'm currently ruminating about making an in memory db
but I'm still banging things round thinking about to support the unicorn nicely.

[Fangbeast]

I can't help with databases right now but I'm currently ruminating about making an in memory db
but I'm still banging things round thinking about to support the unicorn nicely.

That's the problem with unicorns, they are permanently HORNy and being a UNIcorn, they can't make much corn!

On a more serious note (That little pink one on the kitchen table), none of the free or commercial databases were fast after several thousand records, they all suffered from massive slowdowns.

When I added BEGIN TRANSACTION and COMMIT many years ago to all databases I make, the speed increase was phenomenal on inserting records but It doesn't work on queries.

I know I need to start using referred indexes rather than monolithic tables but I need some examples.

[Shield]

I know I need to start using referred indexes rather than monolithic tables but I need some examples.

It might be useful if you provide us with your existing table layout, maybe it can be restructured.

[Fangbeast]

I know I need to start using referred indexes rather than monolithic tables but I need some examples.

It might be useful if you provide us with your existing table layout, maybe it can be restructured.

Code: Select all

Structure RecipeData  My fields     		      ; RecipeFox fields    ; SQLite type
  Version.s           ; Recipe version 	      ;   		              ; TEXT
  Owner.s             ; Recipe owner		      ;                     ; TEXT
  Title.s             ;				                ; Title               ; TEXT
; Description.s       ;				                ; Description         ; TEXT
  Categories.s        ;				                ; Categories          ; TEXT
; Cuisine.s           ;				                ; Cuisine             ; TEXT
  Serves.s            ;				                ; Servings            ; TEXT
  Yield.s             ;				                ; Yield               ; TEXT
; PreparationTime.s   ;				                ; Prep. time          ; TEXT
; TotalTime.s         ;				                ; Total Time          ; TEXT
; Author.s            ;				                ; Author              ; TEXT
  Source.s            ;				                ; Source.s            ; TEXT
; CopyRight.s         ;				                ; Copyright           ; TEXT
; ServingIdeas.s      ;				                ; Serving ideas       ; TEXT
; Wine.s              ;				                ; Wine                ; TEXT
; ImageUrl.s          ;				                ; Image URL           ; TEXT
  Ingredients.s       ;				                ; Ingredients         ; TEXT
  Instructions.s      ;				                ; Directions          ; TEXT
; Notes.s             ;				                ; Notes               ; TEXT
; CookTime.s          ;				                ; Cook time           ; TEXT
  PictureName.s       ; Local picture name	  ;                     ; TEXT
  LastUpdate.s        ; Last time updated	    ;                     ; TEXT
  Favourite.s         ; Marked as a favourite	;                     ; TEXT
  Deleted.s           ; Marked as deleted	    ;                     ; TEXT
  Record.s            ; Record number		      ;                     ; INTEGER PRIMARY KEY AUTOINCREMENT
EndStructure

My database structure is patterned around RecipeFox, a recipe capture program and the list in the structure denotes fields that I have implemented and fields that I want to implement.

I have laid out the columns to indicate the separate fields used and unused to make it easier to read, as well as their type.

Structure this then:):):) And, leave Idle's sheep alone, it's inhumane what he does to them, but they like it!

[skywalk]

Inserting records does not require comparisons beyond rowid so that is fast.
Your table/structure is mostly string and 1 index. So that is a lot of slow string compares. You need to map out the most frequent queries and try to optimize the result set with integer indexes.

Change many of the fields to integer: Record, serves, cuisine, yield, preptime, etc.
Then those numerics point to another small table containing the text.

Also, provide your top 3 SQL query.

[JHPJHP]

Hi Fangbeast,

As skywalk was saying index's on commonly queried fields can provide a significant performance boost.

Code: Select all

DatabaseUpdate(0, "CREATE INDEX idx_recipes_author ON recipes (author);")

Another thing to consider is running [ a ] VACUUM against the database.
- https://www.sqlite.org/lang_vacuum.html

[Fangbeast]

Hi Fangbeast,

As skywalk was saying index's on commonly queried fields can provide a significant performance boost.

Code: Select all

DatabaseUpdate(0, "CREATE INDEX idx_recipes_author ON recipes (author);")

Another thing to consider is running [ a ] VACUUM against the database.
- https://www.sqlite.org/lang_vacuum.html

When creating indexes in SQLite, do they automatically get used or do I have to specifically reference them during searches?

As for vacuum, I have this turned on during database creation: "PRAGMA auto_vacuum = on"

@ SkyWalk Title, Categories, Source, Ingredients, Instructions, Notes are the most important search fields.

[JHPJHP]

Hi Fangbeast,

You don't need to do anything but create them, the database will handle the overhead.

As skywalk mentioned, Integer is faster then String, but test slowly by adding index's to your existing layout one at a time, later switching the data-type and testing again; just be sure to have a good backup.

Cheers!

[Fangbeast]

You don't need to do anything but create them, the database will handle the overhead.

Ah, good to know, thanks.

As skywalk mentioned, Integer is faster then String, but test slowly by adding index's to

As far as I know, SQLite stores all data as string/text so if I converted some members of my database structure to integer, i'd still have to convert to string to INSERT it wouldn't I?

At least, that's the way I remember it. Must have a look at the SQLite pages again.

[skywalk]

I did not use SQLite 2.0 but from what I read, that version stored all data as Ascii.
3.0 and greater improved the db performance by applying type affinity per column such as TEXT, INTEGER, REAL, BLOB.

The use of indexes are handled by the db engine. But, you could "design them in" yourself by creating entity relationships and splitting the table into several others. It makes the query more complicated, but way faster and the db size will drop.
Instead of storing [categories] or [recipes] by themselves. Put them in a separate table with an integer key. Then the main table will find them way faster by matching keys.
This way, "salt" and "sugar" is only stored once in the [master ingredients] table.
Then each recipe is calling out integer indexes from [master ingredients].

[Fangbeast]

I did not use SQLite 2.0 but from what I read, that version stored all data as Ascii.
3.0 and greater improved the db performance by applying type affinity per column such as TEXT, INTEGER, REAL, BLOB.

The use of indexes are handled by the db engine. But, you could "design them in" yourself by creating entity relationships and splitting the table into several others. It makes the query more complicated, but way faster and the db size will drop.
Instead of storing [categories] or [recipes] by themselves. Put them in a separate table with an integer key. Then the main table will find them way faster by matching keys.
This way, "salt" and "sugar" is only stored once in the [master ingredients] table.
Then each recipe is calling out integer indexes from [master ingredients].

I was afraid of that. You are talking about database "normalisation, something I've never done. Did some research on it recently and it was very complicated.

[Fangbeast]

The use of indexes are handled by the db engine. But, you could "design them in" yourself by creating entity relationships and splitting the table into several others.

Yes, this is called database 'normalisation' and I created an example someone on this page of how my database 'should' look based on that but I didn't know how to generate the complex insert statement to handle the data save (Still don't)

Went to a generic coding forum and asked for help on that and they all told me to search for 'database normalisation' which is why I asked them the question in the first place!

It makes the query more complicated, but way faster and the db size will drop.
Instead of storing [categories] or [recipes] by themselves. Put them in a separate table with an integer key. Then the main table will find them way faster by matching keys.

Yes, I realise that by seeing examples but the queries aren't as bad as I thought once I studied them.

I think this will require a massive amount of learning from me as it's beyond my current level of knowledge so I am looking all over the net for concrete examples of how to make complex inserts to save the data to this sort of table arrangement first.

Plenty examples of queries to get the data back but not how to insert it all to this arrangement (Unless I missed something) so will keep looking.

P.S I am up to 250,000 recipes in one SQLite database until the program I made just doesn't respond any more:):):)

[fsw]

Fangles, I know you can do it

[Fangbeast]

Fangles, I know you can do it

In my dreams maybe!!

Had to drop back into the NABBLE SQLite forum and ask for help (Where I should have been in the first place, but I forgot).

Sorted out how my normalised table structure has to look and I have to learn what a "Foreign Key" is before I can start to insert things this way. In my case, the FK will be the linked ID from the sub-table to the main table.

My tables are ready for "Foreign Key" setup as linked tables pointing to the main table are already setup with linked ID's just for this purpose.

The insert sequence is going to be a bitch. Check each step of the insert, check whether they returned success and all to be done in the right sequence. But, it was pointed out to me that the entire sequence need not be slow, all can be wrapped in BEGIN/COMMIT.

This is how the new table will look at the start. Needs a bit more normalising, especially when you think that the owner, author, source and perhaps copyright could potentially have thousands of duplicates.

But, it's a start

PK is primary key
FK is foreign key

Code: Select all

  recipes table                recipe_id             ; Unique record number
                               recipe_name           ; Recipe title
                               recipe_version        ; Revision number
                               recipe_owner          ; Recipe owner (home reference)
                               recipe_author         ; Who wrote the recipe
                               recipe_source         ; Where the recipe was sourced from
                               recipe_copyright      ; Owner isn't necessarily the seller
                               recipe_lastupdate     ; Last time this was updated
                               recipe_favourite      ; Marked as a favourite
                               recipe_deleted        ; Marked as deleted, to be purged
                               recipe_description    ; Specific recipe description
                               recipe_notes          ; General notes
                               recipe_servings       ; Number of servings
                               recipe_peparationtime ; Preparation time
                               recipe_cookingtime    ; Cooking time
                               recipe_totaltime      ; Total time
  ;---------------------------------------------------
  ingredients table            ingredient_id       PK; 
                               ingredient_name       ; 
                               ingredient_price      ; 
  
  recipe_ingredients table     recipe_id           PK; 
                               ingredient_id       FK; 
                               ingredient_amount     ; 
  ;---------------------------------------------------
  recipe_instructions table    recipe_id           PK; 
                               recipe_step           ; 
                               step_description      ; 
  ;---------------------------------------------------
  category table               category_id         PK; 
                               category_name         ; 
  
  recipe_category table        recipe_id           PK; 
                               category_id         FK; 
  ;---------------------------------------------------
  picture table                picture_id          PK; 
                               picture_name          ; 
                               picture_url           ; 
  
  recipe_pictures table        recipe_id           PK; 
                               picture_id          FK; 
  ;---------------------------------------------------