In my design world, I would use a lookup table with a FK constraint as
follows.

CREATE TABLE dbo.SkillLevel (
SkillLevelID int IDENTITY (1,1) NOT NULL PRIMARY KEY CLUSTERED,
SkillLevelDescription varchar(50) NOT NULL
)

INSERT dbo.SkillLevel (SkillLevelDescription) VALUES ('Unknown')
INSERT dbo.SkillLevel (SkillLevelDescription) VALUES ('1st Level')
INSERT dbo.SkillLevel (SkillLevelDescription) VALUES ('2nd Level')

CREATE TABLE dbo.Recipe (
RecipeID int IDENTITY (1,1) NOT NULL PRIMARY KEY CLUSTERED,
RecipeName  varchar(50) NOT NULL,
RecipeDescription varchar(50) NOT NULL,
SkillLevelID int NOT NULL,

CONSTRAINT FK_Recipe_SkillLevel FOREIGN KEY (SkillLeveLID) REFERENCES
dbo.SkillLevel(SkillLevelID)
)

CREATE TABLE dbo.Components (
ComponentID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
ComponentName varchar(50) NOT NULL,
)

CREATE TABLE dbo.RecipeComponents (
RecipeID INT NOT NULL,
ComponentID INT NOT NULL,

CONSTRAINT FK_RecipeComponents_Recipe FOREIGN KEY (RecipeID) REFERENCES
dbo.Recipe(RecipeID),
CONSTRAINT FK_RecipeComponents_Components FOREIGN KEY (ComponentID)
REFERENCES dbo.Components(ComponentID)
)

Rick Sawtell

For all the discussion about NULLs it really isn't that big a deal. It
actually can work quite well for missing information, which is what
you appear to have.  Assigning a value that represents the same thing
is another perfectly valid answer.  I dislike the idea described that
0 actually have two very different meanings depending on the flag, so
I would be looking for some other approach.

If the recipe skill type means a recipe is only associated with one
skill then it is really the same question.  If, on the other hand, a
recipe can be associated with N skills I would suggest a "junction
table", a table with two columns, the PK of Recipes and the PK of
Skills, which together make up the PK of the RecipeSkill table.  In
that case instead of NULL or some special code you simple don't have
any row for a specific recipe.

I have to mention that the idea of using a bigint to represent what
sounds like a skill level, well that seems a bit extreme, but of
course I don't understand the requirements.

Roy Harvey
Beacon Falls, CT

Hello, Elkestra

If the level is unknown, I would use NULL in the Level column, so it will
not appear in any level. If you want to say 'always available', I would
use 0 in the Level column. Therefore, both values are acceptable, but with
different meanings. When the data entry is complete, you may want to check
for null values and assign them to a certain level or to 0 (always
available). And maybe change the definition of the column to disallow
NULLs thereafter. Anyway, I would not use a "LevelUnknown" bit column.

I assume that the skill_level has a range, like 0 to 10 or 100. Why
are you using BIGINT??? That is more than the number of atoms in the
Universe!  You are inviting garbage data.  And, yes, zero is the right
way to go, not a NULL.

Yes it is, but why do you have a recipe without a skill_type -- BBQ,
baking, sauces, deserts, vegetables, fish, etc. ought to be known at
the start.  Maybe I don't understand the problem.

Get a copy of my book SQL PROGRAMMING STYLE and read chapter 5 on the
design of encoding schemes.

Hi Elkestra / Ann-Marie,

I have to disagree with Rick Sawtell and Joe Celko, and agree with
Razvan Socol. Forget what you have read about NULLs being bad, or hard,
or whatever. They are not.

What *IS* hard, is dealing with missing information. You have to find
some way to represent that in the table; then yoou have to make sure
that all queries properly understand and deal with that representation
(for instance, a query for the average level should not take into
account the recipes with 0 if you use 0 to represent unknown difficulty,
otherwise your result is skewed).

In my experience, NULLs may not be the best solution for the missing
information problem, but all other solutions are far worse, making NULLs
the answer of my choice to this problem. Unless, of course, I can tackle
it at the root and ensure that no information will be missing - but that
is in many cases way beyond the control of the lowly DBA or database
developer.

Most of the problems people experience with NULLs fall in either of
these two categories:

1) They are actually problems with missing information. Using some magic
value to represent the missing information instead of NULL will not
solve the problem.

2) They are caused by improper design (and esp. improper use of NULLs in
the database design), or by insufficient understanding of what NULLs are
and how they affect query results.

If you use NULLs to represent the fact that some specific bit of
information (like, for instance, the level of a recipe) is missing in
your database, you should just be fine - as long as you make sure that
you know how NULLs affect query results.

<shameless self-plug>

Recommended reading:
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/07/06/null-ndash-the-databas
e-rsquo-s-black-hole.aspx
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/07/17/the-logic-of-three-val
ued-logic.aspx
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/09/22/dr-unknown-or-how-i-le
arned-to-stop-worrying-and-love-the-null.aspx
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/09/30/what-if-null-if-null-i
s-null-null-null-is-null.aspx

</shameless self-plug>