creating relationship involving multiple-field keys

[DanC]

Hello everyone,

I'm in the process of designing my first database. I've been doing a
good bit of research and studying, but I haven't been able to find a
solution to my current dilemma. The short version is that I have one
table with a two-field primary key that should have a 1-to-many
relationship to another table with a three-field primary key. The two
fields for the first key are both in the second key. I can create the
relationship, but cannot enforce referential integrity. When I try to
do so, I get an error saying, "No unique index found for the
referenced field of the primary table." I don't understand why this
is happening or how to fix it.


The longer version, with table definitions:

The database deals with grant accounting. I work in a department that
does medical research that is funded by federal grants. Some grants
(but not all) are split into "cores", which are basically projects.
We hire on a number of contractors to assist with our research, but
some contractors work on more than one grant, and some work on more
than one core in the same grant.

The four tables most relevant to this issue are below:

t_Consultants
*EntID (PK)
*ConsFirstName
*ConsLastName
*ConsDateCreated
*ConsDateUpdated

t_ConsAlloc
*EntID (PK)
*GrantID (PK)
*CoreName (PK)
*ConsStartDate
*ConsEndDate
*ConsAllocDateCreated
*ConsAllocDateUpdated

t_Cores
*GrantID (PK)
*CoreName (PK)

t_Grants
*GrantID (PK)
*GrantName
*GrantYear
*GrantStartDate
*GrantEndDate
*IDC


What I want to do is create a 1-to-many relationship with referential
integrity enforced between the GrantID and CoreName fields, from the
t_Cores table to the t_ConsAlloc table. When I try to do this, I get
the error mentioned above (no unique index found...).

If I had to guess, the fact that CoreName can be null in t_ConsAlloc
is the big issue here. I can't find a good way around it (aside from
defining a nonexistent core for each grant that doesn't have cores)
because EntID and GrantID aren't enough to define a unique entry, yet
many grants don't have cores. Also, the related fact that there will
be grants in t_ConsAlloc that are not in t_Cores may also be an issue.

Thank you to anyone who read this far. I appreciate any help in
advance.

--DanC

 

[mscertified]

There may be other ways to do it but I would have your t_Cores table have its
own primary key, eg
ID (Key)
GrantID
CoreName

Then I would use this ID in your relationship with t_ConsAlloc.

-Dorian

 

[DanC]

If I'm understanding correctly, I don't believe that would work when
the consultant was working on a grant without cores. The only way to
account for both grant types then would be to have two different
foreign keys in t_ConsAlloc (one to GrantID in t_Grants, one to the
hypothetical CoreID in t_Cores), where only one was relevant to each
record.

Is my logic correct here, or am I misunderstanding something basic?
I've noticed that, while learning, about a third of my problems are
real issues while two-thirds are just due to being new to database
design. I think I'm dealing with a real issue this time, but could be
mistaken.

Thank you for your help.

--DanC

 

[mscertified]

I'm not familiar with your application but I assumed the purpose of the
t_cores table was to list all the unique combinations of Grant and Core. If
there are no cores, there will still be a record in this table but with the
CoreName blank. There will be a one to many between t_Grants.GrantID and
t_Cores.GrantID
t-Cores.ID will be related to t_ConsAlloc.ID
t_ConsAlloc.EntID will be related to t_Consultants.EntID

-Dorian

 

[DanC]

Okay, I see what you mean now. I had only added grants with cores to
the t_Cores table, but your solution does look like the most
straightforward approach.

Thank you for the help!

--DanC

 

[mscertified]

You just have to make sure you add the t_Cores blank record whenever you
create a new Grant. Then you have to cater for that when you add the first
Core (replace the blank record instead of adding a new one).
Like I said, there may be other ways to do it, but that's the only one that
comes to me right now.

-Dorian