sql server (alternate): I'd appreciate any help please.

I'm designing 3 tables Employee, Dependant, & Claim for insurance
purposes. An Employee can have multiple Dependants and claims. (The
employee owns the insurance)

I thought it might be a good idea to have a claim have a foreign key to
Employee.ID. This is fine but I'd also like to know if the claim was
for the employee's dependant instead of the employee himself.

So is this a good idea? Add a field in claim called dependantID too.
And if its NULL the claim was for the employee if not the claim was for
the dependant w/ id = claim.dependantID.

If this is a good idea, how would you make a stored procedure that
queried all claims and displayed the name of the person who the claim
was for (Which could come from the Employee or Dependant table)?

I'm trying to do this is a stored procedure so a crystal report or
program doesn't need to deal w/ the logic.

Thanks for any help.

Instead of an Employee and a Dependant table, how about a Claiment
(sp?) table that contains both; you could then do a self-join to
determine the relationship between Employees and Dependants. At it's
simplest, a Claiment table would have the following characteristics:

ID: a uniqueidentifier
Name: obvious
DependantOf: indicates that this record is a dependant of some other
claimant
PolicyHolder: used to indicate that the record holds the policy

Your claim table then would only have to look in one place to get the
ClaimentID for each Claim.

Does that make sense?

It does make sense. The reason I didn't do that was because I'd end up
w/ redundant info. Dependants don't really need to repeat the policy
info that could all be stored in a single employee record. Any change
to the employee would have to be replicated in all dependants.

This can be done of course. And maybe its the way to go. I just didn't
think it was very normalized. You think this way is better though?

Without knowing what all of the entities are, it's hard to tell. You
have a lot of options, though:

1. I'm assuming that your policy information is split out into a
seperate table, so the only redundant info would be the key column
(which could be null for your dependants, since they don't really have
policies).

2. You could do a 1 to 1 join between your claiment table and an
employee or dependent tables. These extra tables would hold
information that is only relevant to each of these entities (e.g,, your
claiment table talks about who the claim is for, but the employee table
gives information about when the employee was hired, etc).

3. You could keep the seperate tables, and build a view for claiment.
You could then use the view as a lookup tool to get the information you
needed for your stored procedure.

Just bouncing around some ideas.
Stu