Seeking Expert Advice

[vaiamo]

Hi!

I have a problem with tracking unique ids through several tables in a database. This a DB that I am modifying which all ready had existing data.

These unique ids are unique animals that can have several things (4 in 2 general categories) happen to them.

1. The first 2 in order to have an id number assigned they can be processed and tagged. Processed means they have biological information collected on them and during this process they may or may not be tagged and tattooed. Tagged and tattooed animals are the only animals we have an interest in tracking. An animal may be caught and processed many times. They may also have the tag and tattoos numbers changed many times. And they may be caught and processed for 2 reasons – They are a nuisance, or as part of a scientific study. I have 2 tables to represent these activities:
- tblprocessing
PK idnprocessid
-tbleartags
PK idnTagId

2. The animal can also be killed either 1) as part of the hunting harvest or 2) as a result of a miscellaneous (ie hit by car). This obviously can only happen once to an animal but we do not have a kill record for every animal that was tagged. This is split into 2 tables:
- tblHarvestKill
PK idnHarvestID
-tblMiscKillI
PK idnMiscKillID



I have created a 5th table tblBears which assigns a unique id number to each bear that was processed. I have included a screen capture (very poor image quality- sorry but it was the only way to make it small enough)of the relationship window with these 5 tables to hopefully clarify the current relationships. There are as you may guess several problems:

1. Users need to be able to look up a bears ear tag or tattoo number and get a report that details all the processing events and when and how it was killed. Right now this does not work because currently tblEartags has a many to many relationship with tblprocessing, tblharvestkill and tblMiscKill and tblBears does not and cannot serve as a linking table (because it must store one unique id for an animal).

2. As you’ve probably noticed that the relationship between tblbears, tblHarvestkill and tblemiscKIll should be a one to one (since an animal cannot be killed more than once) but it is not serving as this because not every kill record is for a tagged and therefore tracked bear.

This is all complicated by the fact that we don’t have perfect data. For example: we have no processing info for some tagged bears (they may have been tagged in another state) and we need to have a record of that tag number but it has no associated processing record. So eartags must go in their own table.

I have been working on this for months and though I have diagrammed and drawn pictures and wracked my brain I am at a loss. I think I may have spent too much time staring at it and I am hoping some fresher and more expert eyes than mine may have a fix (potentially quite obvious) for this.

Any ideas are welcome. Thanks in advance for any advice. I can provide more details if needed. Sorry for writing a book.

Stephanie

 

[reclusivemonkey]

Erm, I've not quite got a grip on the whole thing here but I can give you a few pointers. Its hard as the table names don't really show up.

1. Use an ID for the top left table. You are linking by several fields, this could be done with one Unique ID in that table, then use this as a lookup in the other.

2. If you don't have all the data, you can just put "N/A" or something in the table until you get that information. Don't base the structure of the database on what data you have, make the structure "right", then worry about getting the data in there.

If you want to email me a clearer pic I'll have a look at the other issues. Just PM me.

 

[vaiamo]

Thanks for the reply and willingness to help!

I've attached a slightly better image but its a .snp (snapshot) format which I'm not sure if that's a universal format or not. Hopefully its better--Is there a way to attch things bigger than 100kb on this forum?

Anyhow:
1. Thats the hunter table which isn't terribly important or used very much --I have it currently in a multipart key because you have the some of the same hunters hunting every year and I'd rather not store the same person's name and info several times. So I use there FirstName LastName and BirthDate to prevent duplicates. That said that table will be dumped every 3-5 years into an archive and I'm not sure I care so much if there are duplicates in the table...

2. As for the missing data--we will probably never get it. But your right and that is a good thought. Thank you!

Stephanie

 

[The_Doc_Man]

Some issues in normalization seem to be blocking your progress.

First, whatever animal you are tracking should be recorded first in a master record in one and only one table to provide an ID number WHETHER OR NOT THIS NUMBER MEANS ANYTHING ELSE TO ANYONE EXCEPT YOU. The number becomes your prime key for that animal. I don't care whether you are talking bears or wombats. One table for an (arbitrary) number. I'll explain in a moment.

OK, in your tattoo and ear-tag and processing tables, since any of these events can occur more than once to the same animal, what you have to do is tie back each action to the prime key of the master record in your animal table. So when you have tag data, you can tie it back to the PK of the master record. Ditto, tattoo data. Ditto, any processing records.

Now, here is why you do this: If the animal:tag relationship is one-to-many, same for animal:tattoo, same for animalrocessing results, ... then NONE of the ID fields in any of those tables are valid candidate keys to identify the animal. Which is why you seem to be running into a many-to-many relationship. BUT, you see, you are looking at many-to-many from the viewpoint of the tattoo table or the tag table or the .... etc. etc. From the viewpoint of the master animal ID table, they are all one animal:many pieces of data IF you tie everything back to an arbitrary animal table.

In your dead-animal table, do you really need that much different data between "culled" and "killed" ?? 'cause you might be able to simply the heck out of your structure by adding the tables together and merging similar fields (and putting N/A in fields that don't have meaning for that method of killing), then add one more field, an encoded drop-down (combo box, maybe), to show animal status, with drop-down codes such as 'dead/culled herd', 'dead/roadkill', 'dead/found carcass', 'living/next door to Elvis', 'unknown/on the lam', etc. And maybe you take the easy way out and only record dead animals. Anything else must be living or lost. Or, see later suggestion for a place to put this.

Now, if you can tie it all to a master animal ID number EVEN IF ALL IT MEANS IS AN ARBITRARY RECORD NUMBER, you can write reports based on the master record ID and can do parent/child type things on the other tables linked via foreign keys to the master's prime key.

As a matter of fact, if the data you keep in the dead-animal table is not too much, it could become the master animal record, since as you correctly point out, the animal only dies once. (Unlike James Bond...{familiar guitar theme plays in background})

So in the master table, if the animal ain't dead, its status is either unknown or alive. If it is dead, you fill in the necessary blanks, pause for a brief moment of mourning, and go on to the next animal. Now, it SOUNDS as if I'm telling you to set aside huge chunks of your disk for animal records that are mostly empty until they die... but the truth is, most text fields (and ALL memo fields) are "sparse." They are just pointers to the "real" text strings. If there is no data in the field, all you have is the place holder that points to nothing in particular. So it doesn't take up THAT much space. Note I'm not says "NO space" - just "not that much."

Now, as to tying all this together, your tattoos and ear tags probably have similar data to be gathered. Processing, different story I'm sure. So maybe you could look at combining the tattoo/tag tables into a single table with a field that says "I'm a tag" or "I'm a tattoo." Then you record the number, where the number is (and obviously, for ear tags, the answer is "EAR"), where (geographically) and when it was applied, whodunit, etc. etc.

Just tossing some ideas your way. If they make sense, have fun with 'em. But will this database answer the age-old question, "Does a bear dump in the forest?"

 

[vaiamo]

Thank you Thank you Thank you! Lots to digest. You all are fabulous. Stephanie