January 14, 2011 at 11:20 am
Hello Everyone
I am working on a database for myself, it will be designed to store my recipes. My wife & I own a cake decorating company
What is the best way to store the recipe itself? I would like to be able to keep the lines separate from one another. So that if I use a web based front-end, the recipe will show like a normal recipe is supposed to look like.
I am open for any suggestions
Thanks in advance
Andrew SQLDBA
January 14, 2011 at 11:54 am
First thoughts, recipe header which contains the base record, contains name and other data. A recipe ingredients table related back to the recipe header. A recipe instructions table containing a list of instructions and providing a column to sort them into the correct order.
You could add an ingredients lookup table that contains all available ingredients and could then use a foreign key to relate these to the recipe ingredients.
You could add a measures table that contains all available measures (cup, teaspoon, tablespoon)
Very rough and I hope this isn't homework.
As far as best.. It depends..
CEWII
January 14, 2011 at 12:09 pm
Nope, not homework. I am a little old to be going to school. I like the ides that you gave for the design of the databases.
What I am wondering is do I store the instructions in a single row, or do I divide the instructions up into separate rows?
Then I am trying to picture the design of the front-end to allow entry of the instructions and ingredients. I am wondering if I should store the Break
or Paragraph<p> tags in one large record? Meaning, have the Instructions stored in one column that would be varchar(8000) or break up the instructions into small one sentence per row type of thing.
Andrew SQLDBA
January 14, 2011 at 12:18 pm
Have you thought about just storing the files in a column (varbinary)?
Or even use filestream if you're super ambitious. This would be good if you already have all your recipes in text files (or PDFs).
create table recipe
(
name varchar(50),
recipe varbinary(8000),
notes varchar(255),
createdate smalldatetime
)
something like that? A simple table with all the info you need.
January 14, 2011 at 12:55 pm
I would rather store the data as data, so I can query the table to find all recipes that have a certain ingredient or that takes under a certain time, etc...... just by looking at the text in the ingredients or the instructions
Thanks
Andrew SQLDBA
January 14, 2011 at 1:12 pm
AndrewSQLDBA (1/14/2011)
Nope, not homework. I am a little old to be going to school. I like the ides that you gave for the design of the databases.What I am wondering is do I store the instructions in a single row, or do I divide the instructions up into separate rows?
Then I am trying to picture the design of the front-end to allow entry of the instructions and ingredients. I am wondering if I should store the Break
or Paragraph<p> tags in one large record? Meaning, have the Instructions stored in one column that would be varchar(8000) or break up the instructions into small one sentence per row type of thing.
Andrew SQLDBA
I would avoid splitting up the lines into single sentence entries like the plague. I did it once as an exercise in curiousity and will never, ever, deal with that again. Editing and modifying things mid-stream was horrendous, and keeping the sorting order straight was a nightmare.
VARCHAR(MAX) is your new best friend for something like the instructions field.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 14, 2011 at 1:12 pm
AndrewSQLDBA (1/14/2011)
Nope, not homework. I am a little old to be going to school. I like the ides that you gave for the design of the databases.What I am wondering is do I store the instructions in a single row, or do I divide the instructions up into separate rows?
Then I am trying to picture the design of the front-end to allow entry of the instructions and ingredients. I am wondering if I should store the Break
or Paragraph<p> tags in one large record? Meaning, have the Instructions stored in one column that would be varchar(8000) or break up the instructions into small one sentence per row type of thing.
I had thought of the instructions as seperate distinct entities. you certainly could do the instructions as a single big column but I am partial to rows, I can see an argument either way. I see each step as a single line and I like to store them that way. But thats me..
Some of the other posters had good ideas too.
CEWII
January 14, 2011 at 2:14 pm
Have you thought about going the easy route and buying a software package? It looks like there are several nice ones for around $30-$35. It's your choice but I know it would take more than $30-$35 of my time to design/build/maintain such a program and it probably still wouldn't be as good as the commercial ones.
January 14, 2011 at 2:19 pm
This is part of our website, and we want to be able to store the inventory, and recipes in one single database. Not having to try to keep multiples up to date. And we can get to our data no matter where we are in the world.
Andrew SQLDBA
January 14, 2011 at 3:30 pm
You can have the receipe/instructions broken down by line number and store them as separate row.
Leave the formatting to the presentation layer.
January 15, 2011 at 5:22 am
AndrewSQLDBA (1/14/2011)
This is part of our website, and we want to be able to store the inventory, and recipes in one single database. Not having to try to keep multiples up to date. And we can get to our data no matter where we are in the world.Andrew SQLDBA
I can appreciate that. Well, I would suggest that you also do some research into all "outlier" recipes. By this, I mean that recipes that don't fit the standard format, in whole or part. I have seen some recipes that have two ingredient lists and instructions, like a recipe that has parts for a cake and the frosting separate. Do you want to force this into just one list of ingredients and instructions, enter as two separate recipes (and maybe link together somehow), or build in to your table structure the ability to have mutliple lists of ingredients and instructions for a single recipe?
Another possible issue is the ingredients. It seems that ingredients can come in almost any format. Typically, they are numbers/fractions, but sometimes you get amounts like "Dash" or "Pinch". I've also seen amounts like "1 cup plus 1 Tablespoon". I mention this because it was previously mentioned to have a lookup for the measure. It's not that I'm advocating against (in fact I would try to incorporate this), but that as you design the database it would be good to keep these oddities in mind so you can decide early on how you will deal with them.
On having the instructions as one VARCHAR(MAX) field or individual records for each sentence, I would tend towards one VARCHAR(MAX) field. It would be easier to implement and manage and I cannot think of a reason why I might want to query the instructions and have just part of them. I tend to think of the instructions as a whole, not a collection of individual sentences.
I don't know how much this helps but I wish you well in your efforts.
January 15, 2011 at 5:43 am
I would store the ingredients as rows in a separate table, keep the recipe name and instructions in the recipe table. This makes the data model more flexible. As you said, you can do searching by ingredient more easily. But one day you might also want to have some information about ingredients. Nutritional information, for example. So your structure would look something like this:
create table ingredient_units (
unit_code char(4) not null primary key,
unit_dscr varchar(32)
-- maybe a conversion ratio to a base unit like grams?
)
insert ingredient_units select 'SLC', 'slice'
create table ingredients (
ingredient_name varchar(32) not null primary key,
default_unit_code char(4) not null foreign key references ingredient_units (unit_code)
-- other columns if you ever want to add more info about ingredients
)
insert ingredients select 'bread', 'SLC'
create table recipes (
recipe_name varchar(32) not null primary key,
recipe_instructions varchar(max) not null
)
insert recipes select 'toast', 'Put bread in toaster. Wait'
create table recipe_ingredients (
recipe_name varchar(32) not null foreign key references recipes (recipe_name),
ingredient_name varchar(32) not null foreign key references ingredients (ingredient_name),
one_serving_quantity decimal (6,2) not null,
quantity_unit_code char(4) not null foreign key references ingredient_units(unit_code),
)
insert recipe_ingredients select 'toast', 'bread', 1, 'SLC'
With something like this you can easily do things like multiply the serving size, allow users to convert to different display untis (metric/imperial/whatever), and so on.
January 15, 2011 at 2:21 pm
Although not a cook or chef, heck might wife tells me I can burn water.
But intrigued by the thought of what you are attempting to do. Now I shudder at attempting to edit a VARCHAR(MAX) string to insert a instruction at the correct point and so in playing around came up with a very simple table as:
CREATE TABLE [dbo].[Instructions](
[RecipeNr] [int] NULL, --This should be a foreign key to a table of recipe names
[SeqNr] [int] NULL, --This is the order in which individual instruction lines should be presented for the reader
[Step] [varchar](100) NULL --This is the explicit instruction to the Chef or Cook
) ON [PRIMARY]
Creating an insert statement for this type of table is simplistic, but the thought of "correcting" instructions already entered became the key to making the system simple. So I devised the following T-SQL
CREATE PROC [dbo].[AddtoExistingRecipe]
@RecipeNr INT = 0,
@Step VARCHAR(100) = '',
@After INT = 0
AS
IF @RecipeNr = 0 OR LEN(@Step) = 0
BEGIN
PRINT 'Missing required input item'
PRINT 'Run as AddtoExistingRecipe ''Recipe Number'',''Instructions'',''Insert after step'
PRINT 'For exampe AddToExistingRecipe, 1,''add table spoon of salt'',6'
RETURN
END
IF NOT EXISTS (SELECT Step FROM Instructions WHERE RecipeNr = @RecipeNr)
BEGIN
PRINT 'Recipe number ' + CAST(@recipeNr AS VARCHAR(3)) + ' Does not exist'
RETURN
END
IF NOT EXISTS (SELECT Step FROM Instructions WHERE SeqNr = @After)
BEGIN
PRINT 'step number '+ CAST(@After AS VARCHAR(3))+' Does not exist in recipe '
+ CAST(@RecipeNr AS VARCHAR(3))
RETURN
END
UPDATE Instructions SET Seqnr = Seqnr + 1
WHERE Seqnr >= @After AND recipeNr = @RecipeNr
INSERT INTO Instructions (RecipeNr,seqNr,Step)
VALUES(@RecipeNr,@After,@Step)
Since you would not be running the above too frequently, I included some T-SQL to remind you of what input is required. Note if you insert a new step, all susequent steps are re-numbered to retain the correct presentation order.
Now I do not pretend that this is the only or best method for accomplishing what you need, but hope that it will spur your creative juices so that you wind up with what best fits your needs.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply