How To Avoid Using Three Field Composite Key

[Lady_Jane]

Hi everyone,
Attached is a screen cap. of my ERD. The table I am dealing with is tbl_SamplingInfo at the top.
The fields I am worried about are UID, SampleYear, SampledBy and the ones below it.
One site (represented by UID) can be sampled in multiple years and one site can be sampled by different people in the same year. This means that my primary key right now needs to be a combination of UID, SampleYear, and SampledBy. I would like to avoid having to use 3 fields and a composite key, but I can't seem to figure out how to appropriately split up the tables.
I want to make sure that end end result is easily searchable by UID, SampleYear, and SampledBy.
Any suggestions?
Thank-you!

[jzwp11]

Since you say that a UID can be sampled multiple times in a year, if you set up a composite key with the 3 fields (UID, sampleYear and sampledby) that combination would have to be unique for each record. What happens if the UID is sampled 3 times in a year by the same person? Access will prevent you from adding the second and third records because they would have a duplicate composite key relative to the first record added. My preference is to have an autonumber primary key field (I have one in every table). I would also record the actual sample date; you can extract the year using the year() function. UID would still be your foreign key. You should be able to construct a query to be able to search by any combination of the 3 fields whether or not the 3 fields are part of a composite key or not.

[boblarson]

I don't use composite keys as a rule. It simplifies things to not do it. I use a surrogate key (autonumber) for each table and then store that in the child table. If I want constraints enforced I set a multi-field index on the table (which accomplishes what a composite key as far as no duplicates). See here for how to set one up:
http://www.btabdevelopment.com/ts/mfi

[rpeare]

bah as I was typing jwzp wrote almost exactly what I was going to say. stupid slow fingers.

Each table should have it's own PK which you don't have. It looks to me like your table tbl_CatchmentsCSL is your 'main' table every other table you have should have a parent/child relationship with that table (most of yours do except the table you're talking about) if you do as jwzp suggested you can have an infinite amount of items in the table you're concerned with and not have to worry about overlapping composite keys.

[NTC]

if UID is repeating, and not unique in the Catchments table? looking at the relationship map this is very surprising. Really it probably should be. To join to the other tables you need to make 3 lines as you note.

consider to add a SuperID field that is autonumber to the Catchments table...and a new XRefKey field to all the other tables. If this is a brand new table then you can reset the relationships from other tables to it now. If there is already data in the tables then it will be a tricky little project to set those XRefKey values correctly - but doable.

Hope it helps.

[Lady_Jane]

Thanks for all the replies everyone,

UID in tbl_CatchmentsCSL is unique, it does not repeat.
jzwp11, there are two things we are sampling here, benthos and chemistry. Those fields are filled in with either "yes" or "no". There is one sampling outing for each. Usually it will be the same person sampling both, but occasionally it is not. This is why I say multiple sites can be sampled by multiple people in the same year. I will never have a situation where the same site is sampled by the same person during the same year that requires a new record. Once benthos and chemistry are set to "yes" that won't change no matter how many times the site is visited, no new record required.

Hope this makes sense.
I also don't know the exact date for a lot of the samples.

The attached image shows what I am thinking of doing. Please let me know what you think.
A couple of fields which actually have a 1 to 1 relationship to UID have been moved to the Catchments table, and SiteYear is a combination of UID and Year.

[jzwp11]

There is one sampling outing for each.

That constitutes 2 sampling events and thus 2 records. You should designate which type (chemistry or benthos) with a field

tblSamplingActivity
-pkSampleID primary key, autonumber
-UID foreign key to tbl_CatchmentsCSL
-dteSample
-fkPeopleID foreign key to table that holds people info
-fkSampleTypeID foreign key to tblSampleTypes


tblSampleTypes (2 records: chemistry, benthos)
-pkSampleTypeID primary key, autonumber
-txtSampleType

[jzwp11]

There is one sampling outing for each.

That constitutes 2 sampling events and thus 2 records. You should designate which type (chemistry or benthos) with a field

tbl_SampleActivity
-pkSampleID primary key, autonumber
-UID foreign key to tbl_CatchmentsCSL
-dteSample
-fkPeopleID foreign key to table that holds people info
-fkSampleTypeID foreign key to tblSampleTypes


tblSampleTypes (2 records: chemistry, benthos)
-pkSampleTypeID primary key, autonumber
-txtSampleType

SORRY FOR THE DOUBLE POST; I'M HAVING TROUBLE DELETING THIS FOR SOME REASON

[Lady_Jane]

That is interesting and makes sense.
I am just wondering, if I have a people table and a sample type table and both of these only have a code field and a description field, what is the point? Why would I not just use the description in the first table? I don't have any additional info about people or sample types.
I also have a permission field. This is unique to each site and year combination. If I include that information in the sample activity table then it has the chance of being redundant because the permission level will be the same regardless of what is being sampled. Where should this field go?
Thank-you!

[jzwp11]

The people table will typically have more than just a description field, you would have first name, last name, address info etc. As to just having a description in the table, it helps the user in that they do not have to type the description over and over again for each new record. It also eliminates spelling errors; just think how may ways someone can misspell the word "chemistry". If you try to do a search for the word chemistry and it is misspelled, you will not pick up those records.

I also have a permission field. This is unique to each site and year combination.

Since the permission is relative to the site (and not necessarily to each sampling event) and you can have many of them (one for each year) that describes a one(site)-to-many(permissions) which should be in a separate but related table.

tblSitePermissions
-pkSitePermID primary key, autonumber
-UID foreign key to tbl_CatchmentsCSL
-dtePermission (or a field to hold the year for which the permission is valid, your choice)
-other fields needed to describe the permission.

[Lady_Jane]

what does dte mean?

date I guess... nevermind

[jzwp11]

correct

I typically use prefixes that identify the data type of the field

txt=text
dte=date
long=long number integer
pk=primary key
fk=foreign key
log=logical (yes/no)
sp=single precision number
dp=double precision number

Having the prefixes helps me out when working with queries and code.

[Lady_Jane]

Thanks for your help everyone, I've got my database set up in a way that seems to be working. Still having one issue though:
tbl_SampleActivity looks like this:
SampleID (pk)
UID
Year
SampleType
Sampler
etc...

Although SampleID is my primary key, I would still like to prevent any duplicate combinations of UID, Year, and SampleType.
I was going to add a field that is automatically populated with a concatenation of those three fields and not allow duplicates on that field. But I can't set a default value as a calculation, apparently.
Is there another way to do this?
It would be great if I didn't have to add code to populate that field as well.

[jzwp11]

As Bob suggested in his post, you can still setup a multi-field index and set that index as unique.

[boblarson]

As Bob suggested in his post, you can still setup a multi-field index and set that index as unique.

Yep, and as a reminder, and so you don't have to go up to previous posts to find it - here's the link to how to create a multi-field index:

http://www.btabdevelopment.com/ts/mfi