Keys, foreign key?

I need to know if this is required? I have products, with the help of their business account numbers, are naturally categorized numerically. I want to create a product category table and a product account table.

Example :
tbProductCategories
TypeCode | Description
1000 | Cups
2000 | Plates

tbProductAccounts
Account | Description
1001 | Mug
1002 | Glass
2001 | Plate
2002 | Saucer

With the above tables (which are made up :) ), would you include
a foreign key in tbProductAccounts indicating the type code?

What would the stored proc look like without it?

Create Procedure usp_GetProductAccounts

@.iTypeCode int

AS

SELECT tbProductAccounts.Account,
tbProductAccounts.Description
FROM tbProductAccounts
WHERE tbProductAccounts.Account - iTypeCode > 0 AND < 999

Would this work? Or should the foriegn key always be included?

Mike BIn my opinion, you should always, always, always include the foreign key. What happens when someday the boss demands a part number of "clyde" for a cup with a clydesdale on it because he promised it to a vendor?

-PatP|||Originally posted by Pat Phelan
In my opinion, you should always, always, always include the foreign key. What happens when someday the boss demands a part number of "clyde" for a cup with a clydesdale on it because he promised it to a vendor?

-PatP
Then that would not be clyde, but possibly 1010?

lol, I understand what you are saying. I think I knew that already, especially after I placed the table in the diagram an noticed there was no "true" relationship!

Thanks
Mike B|||Another reason to keep the foriegn key I guess is also to ensure data entegrity without having to add constraints in the stored proc or elsewhere.

Mike B|||another reason to support foreign keys is the benefit of merge joins in multi-table queries.
when you have to sorted columns in two tables and they are the columns in the join predicate
(on c.col1 = p.col2)
SQL Server will perform a merge join.
this is one of the fastest join processes available.
to encourage this you should always create non-clustered indexes on your foreign key columns.

{BOOKS ONLINE}Understanding Merge Joins

just another angle on this question seein' as all of the good points were taken.