HELP! Best practice for lookup data and foreign key values?

[James E]

I have a question about best practices of how to deal with lookup data from
my C# apps. On a couple of occasions I have come across a problem where I
have to automate inserting a record into a table that has a foreign key
constraint that is linked to a lookup table. E.g. Take the following
database structure:


SQL-Server Database:

Table 1:
Name: CommunicationTypes
Columns: CommunicationID (Primary Key), Description


Table 2:
Name: Communications
Columns: CommunicationID (Primary Key), CommunicationTypeID (Foreign Key),
Sender, Recipient, etc.



...In this example, there is data such as the following in the
CommunicationTypes table:

CommunicationTypeID Description
------------------ ------------
1 Email
2 Letter
3 Phonecall
etc.....


My app needs to log details of communications, but in some cases,
auto-generates emails and letters. When this occurs I need to auto-insert a
record into the communications table. This means my app needs to know about
what the CommunicationTypeID is for the communication being 'auto-recorded'.
Should my app retrieve the CommunicationTypeID from the database by passing
the description? If the list is short, could I store the ID's in the config
file? Should I have a strongly typed class that has static methods to return
the ID from the lookup table? Other suggestions please!!

My question is, what is the best practice for situations like this, where:
a) You have a lookup table that will be added to over time
b) You need to insert a record into a table that has a foreign key related
to the lookup table, but the app needs to know the foreign key value without
the user selecting anything from a list.

Many thanks

JamesE

 

[Guest]

Hi James,

There are several answers to your situation - I use SQL Server stored
procedures in almost all instances like this.

I break down my sp's into common actions like inserting and updating (1 sp),
deleting, etc. For instance, if I wanted to insert a communication record I
would create an sp named "spInsertCommunication" with 3 parameters.
CommunicationType
Sender
Recipient

Within the sp I would lookup the CommunicationTypeID (and probably the
SenderID and RecipientID for that matter) like this.

Declare @CommunicationTypeID as tinyint
SELECT @CommunicationTypeID = CommunicationTypeID FROM CommunicationTypes
WHERE CommunicationType = @CommunicationType

....and while not always needed...
IF ISNULL(@CommunicationTypeID,0) < 1
BEGIN
INSERT INTO CommunicationTypes (CommunicationType) VALUES
(@CommunicationType)
SET @CommunicationTypeID = SCOPE_IDENTITY()
END

--Do your insert to the Communication table here.

There are a lot of examples so I won't bore you with the details, but check
out the System.Data.SQLClient.Command and Parameter objects. These objects
are the best way to communicate to sp's.

Hope this helps,
Mike