recipe - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

recipe

Author

Topic

gokalp
Starting Member

2 Posts

Posted - 2012-04-08 : 14:00:40

Hello

I am a newbie. I have the following recipe structure. There are raw materials composed of materials in steps and preperation steps like mixing, waiting etc. e.g.
for raw material1
1st step starch, code e.g.1 (it is a material), unit (kg)
2nd step mixing, code e.g.11 (it is a process mixing), unit (minutes)

there are recipes that you can select the steps from different raw materials freely
e.g.

recipe e
1st step starch, %50
2nd step mixing,

jobs
1st step recipe e, 1000kg (individual amount is calculated from recipe e and raw material)
2nd step recipe f, 500 kg

What will be the tables primary key, foreign key etc.

Regards

Gokalp

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-09 : 03:37:26

From what i understood...you could have <Step_id> in <recipe> table as primary key.
<Job Id> could be primary key in <Job> table.

But, I can't say about Foreign Key. You need to provide more info on how the tables are related to each other ie: which fields are related.

Maybe if the <Job> Table has a field called <Steps> then it could reference the <recipe> table's <Step Id> field as Foreign Key.

But you still need to provide more info about your tables and the relationship between them.

jtm
Starting Member

3 Posts

Posted - 2012-04-10 : 02:55:07

Hi I am working on a project that looks the same as golkap and would really appreciate some help.
The aim is to have a system that works as the Buy One Get One Free specials. I have two tables: Stock and Recipe and here is how the look:
Recipe
Code Ingredients Scanned UniqueId
LUNCH COKE 1 1
LUNCH CHIPS 1 2
SNACK COKE 1 3
SNACK CHOCOLATE 1 4
SNACK SWEET 1 5

STOCK
CODE PRICE UNIQUEID
SNACK 10 1
LUNCH 12 2
COKE 5 3
CHIPS 3 4
...
What i basically need is to be able to step forward and backward in the Recipe table to spot all the items that are sold at the point of sale that is why i have the Column Scanned in the Recipe table.if for example one coke,one chocolate,one chips and one sweet is scanned i can only get one special since for me to have the two specials i need two cokes one for each special.
I understand that using cursors is not really a good solution into reading through a table
please help

JTM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-10 : 15:33:26

I am working on a project that looks the same as golkap

are you studying in same class?

Now reg your question not clear on what is that you're expecting as output

can you post what your expected output is?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gokalp
Starting Member

2 Posts

Posted - 2012-04-10 : 16:34:31

Hello

I do not know the the tables to be used. Here is structure.
I have raw materials. Each raw material composed of steps. 1st step can be a material, 2nd step can be a process like mixing, 3rd step can be another material, 4th step can me a process like wait and so on. In raw material we keep the following data as an example below.
Raw Material1 is as follows.
Material/process, Code (shows if it is material or a process), Unit
1.Starch,1,kg
2.Mixing,10,minute
3.Wait,11, C degree for temperature

I have Recipes that you can freely copy a step from Material1, Material2 etc.

In Recipes we keep the following data
Material/process, % amount for materials or unit amount
E.g. Recipe1 is
1.Starch, 50 (%50)
2.stg
3.Mixing,20 (minutes from Raw Materials)
4.Wait,40 (C degree for temperature from Raw Materials)

In Jobs we select from Recipes
Here is the data kept
Recipe,Amount
1.Recipe1,3000kg
2.Recipe2,1000kg

So if we start Recipe1,
Process starts with 1st step of Recipe1, Multiplies 3000 %50 and takes the unit kg from Material1 and takes 1500kg Starch from Silos.Passes the 2nd step then goes to 3rd step understands it will mix 20 units looks to the Material1 learns the unit as minute and mixes for 20 minutes.

Please explain which relations exist in this structure. Is it many to 1, many to many etc. Primary and foreign keys.

Regards

Gokalp

jtm
Starting Member

3 Posts

Posted - 2012-04-11 : 02:32:21

No i am not in the same class as him not even in the same country i guess.
the outpout should be either LUNCH and chocolate and Sweet or SNACK and chips and Coke

JTM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources