Using SQL transaction tables?

[Fangbeast]

I'm thinking that the proper design for my recipe database is 3NF using intermediate transaction tables as some fields are going to have thousands of duplicate data but don't know how to design it, write it or read it.

Just as the most basic structure:

Recipes ; Main recipes table
Title ; [Text] Recipe title
Recipeid ; [INTEGER PRIMARY KEY AUTOINCREMENT] Recipe record id

Category ; Category table
Itemcount ; [INTEGER] Items belonging to category
Categoryid ; [INTEGER PRIMARY KEY AUTOINCREMENT] Category record id

tr_Category ; Category transaction table
Recipeid ; FOREIGN KEY(Recipeid) REFERENCES Recipes(Recipeid) ON DELETE CASCADE)
Categoryid ; FOREIGN KEY(Categoryid) REFERENCES Categoryid) ON DELETE CASCADE)
Recordid ; [INTEGER PRIMARY KEY AUTOINCREMENT] tr_Category record id

How do you write sql to this sort of thing? Does my example look right?

IU have to do it as the database has grown to a sluggish 2GB and I have more recipes to feed it. It's unmanageable now.

[useful]

http://www.minimdb.com/minimono.html
http://www.minimdb.com/download/release-notes-1.26.html

Test of time (50 years from the date of the birth and 40 years since standardization) NoSql (mumps) to help you

[spikey]

I'm thinking that the proper design for my recipe database is 3NF using intermediate transaction tables

I doubt it, I really do. Most of the time people can manage quite happily with 2NF (maybe 2 and a bit). The only time that I've seen 3NF used in anger is in enterprise resource planning applications such as CICS or MRP. In these situations you need to keep track of a lot of data which is actually redundant for a significant amount of time but you can't avoid keeping track of it all somewhere.

As an example a factory's parts database will include finished products, subassemblies used to make them and discrete components used to make the subassembies. Assemblies and subassemblies will have bills of material (BOM), drawing lists (DL) and other documentation such as assembly instructions or test procedures etc, all of which will need version control.

However most of the time you won't want much of this data:-
1) There will be no BOM, drawing lists or test procedures for any discrete components - you'll buy them from someone else complete.
So storing BOMs for every part in the db is wasteful - you only want BOMs for assemblies and sub assemblies - and there will be far less of these than the components which are requried to build them.
2) Most of the time you will only be interested in the current BOM and DL, the only time you want old versions is to answer the questions like 'what did we change on that board five years ago?', 'how do we update the Y the way we did the X last year?' So when referencing an item you just need to know that there are multiple BOMs and DLs around - not their full contents.
3) Each discrete component will actually have two (or more) part numbers - yours and the catalogue number used by your supplier(s), however the only people who'll be interested in these latter numbers is the purchasing office (maybe stores too), nobody else will care. There will be far more of these other part numbers than your own if you have alternative suppliers for each component.

and so on... This IS a 3NF scenario.

I can't see that you are dealing with anything that truly falls into this area. A recipe with no ingredients is no use, nor is one with no method, a method with no quantities isn't going to help much... This is all met by 2NF though, I'm not seeing anything really warrants the extra design work of 3NF so far.

At each stage you need to ask yourself 'what advantages does doing it this way offer?':-

Recipes ; Main recipes table
Title ; [Text] Recipe title
Recipeid ; [INTEGER PRIMARY KEY AUTOINCREMENT] Recipe record id

How does separating out the Title from the rest of the recipe help you? It's not big and it just means you have to 'join' it back in again in a query every time you want it, making the query that bit more complex. Now if you were talking about a picture of the finished product or multiple pictures illustrating the method...

Category ; Category table
Itemcount ; [INTEGER] Items belonging to category
Categoryid ; [INTEGER PRIMARY KEY AUTOINCREMENT] Category record id

I can see that a lot of the time you won't want the 'Itemcount' but I can also see that a "SELECT COUNT(Categoryid) FROM Category;"
is not going to be arduous when Categoryid is an IPK. I'm inclined to think an index somewhere else might be more helpful though - probably on CategoryID. I think that separating the CategoryID from the name of the category will just be a nuisance.

tr_Category ; Category transaction table
Recipeid ; FOREIGN KEY(Recipeid) REFERENCES Recipes(Recipeid) ON DELETE CASCADE)
Categoryid ; FOREIGN KEY(Categoryid) REFERENCES Categoryid) ON DELETE CASCADE)
Recordid ; [INTEGER PRIMARY KEY AUTOINCREMENT] tr_Category record id

Yes I'd agree with this one offering advantages:
1) this way a single recipe can belong to multiple categories by having mutiple records for a single Recipeid.
2) it will support searching or retrieving by categories.
I'd still want indexes though on both Recipeid and Categoryid. You don't need the two prior normalisations to implement this one.
ON UPDATE CASCADE will be far more important that ON DELETE. If the IPK gets changed for either of the foreign tables and it doesn't get updated here this will foul up the whole record rendering it useless, or worse pointing to something else entirely, but keep the ON DELETE too because otherwise that would become an administrative chore. (It's still 2NF not 3NF though!)

IU have to do it as the database has grown to a sluggish 2GB and I have more recipes to feed it. It's unmanageable now.

2Gb is not big for a database. There are lots of databases kicking around with single tables bigger than 2Gb. SQLite's quoted max is 140 terabytes. The biggest problem with database performance at the design stage is failing to set up the right indices to support the implemented queries. The easiest improvement for database performance at the operational stage is to add a useful index for the problem queries.

[collectordave]

Hi

Not a chef or cook just enjoy eating so.

Just looking at the structure of your database for the recipies.

Have just a couple of questions.

From the title I guess the Recipes table is the master table. In this you have categories and subcategories fields. many others as well.

Dealing with these; Can a recipe belong to more than one category? Also can a subcategory belong to more than one category? Do you see your users wanting to search for recipes based on subcategory, category or both?

Both category and subcategory are also TEXT fields? These can be shortened to integer fields with some organisation this would allow just one entry in the whole database for each category title.

Looking at the ingredients table. I can see that, say the humble potato would be used in possibly thousands of recipes and can be prepared in many different ways. Is the preparation of the ingredient important to the ingredient or to the recipie? Also would the user search for recipies based on ingredients?

If you are answering yes to any of these then I guess 3NF is the way to go.

However even in 3NF it is not neccessary to build new queries all the time, some can be embedded in the database and of course you are using PB so some can be more or less hard coded leaving only the where clause for you to operate on when dealing with a user search.

If I can help just contact me.

Kind Regards

cd

[percy_b]

Hi Fangbeast,

Two quick question for you. Can you have multiple categories for a Recipe? Is that the purpose of the "tr_Category" table to facilitate a many-to-many relationships between Recipes and Category?

Anyway, here is a rough example of querying your data:

Code: Select all

select r.recipeid, c.itemcount, r.title
from recipes r
join tr_category t on t.recipeid = r.recipeid
join category c on c.categoryid = t.categoryid
order by r.recipeid;

[Fangbeast]

Hi Fangbeast,

Two quick question for you. Can you have multiple categories for a Recipe? Is that the purpose of the "tr_Category" table to facilitate a many-to-many relationships between Recipes and Category?

Anyway, here is a rough example of querying your data:

Code: Select all

select r.recipeid, c.itemcount, r.title
from recipes r
join tr_category t on t.recipeid = r.recipeid
join category c on c.categoryid = t.categoryid
order by r.recipeid;

To percy_b, yes. It's common practice for a recipe to belong to multiple categories.
To spikey, the table was just an example, it is not the full structure of my recipes database.

[collectordave]

Hi Fangbeast

Talking to wife I am told that not all ingredients are single ingredients some are actual recipes in themselves. A little more non trivial.

However for a typical SQL statement and use, for Sqlite you can take a look at my movie database. Statement reproduced below:

Code: Select all

;Database Search Variables
Global DBSelect.s = "SELECT DISTINCT ID,Title,Rating,Year,Plot,DVDLocation,FileName " +
                    "FROM Movies LEFT JOIN MovieGenre ON Movies.ID = MovieGenre.MovieID " +
                    "LEFT JOIN MovieActor ON Movies.ID = MovieActor.MovieID " +
                    "LEFT JOIN MovieDirector ON Movies.ID = MovieDirector.MovieID "
Global DBWhere.s = ""
Global DBOrder.s = " ORDER BY Title ASC"

I keep the three separate like that to allow me to build the where clause on user selection. The final query submitted is DBSelect + DBWhere + DBOrder. It also means that I can play around with the main select query without worrying about actual selection or ordering when designig the query. The query can then also be embedded in the database so selection from my programme would simply be SELECT * FROM MyQuery WHERE dependent upon preference.

Hope this helps.

Regards

cd