Updating multiple related tables via form/subform

[Sharkman1885]

Mark,
Unfortunately I donot have access to the original excel sheet and it is difficult to use size as a means of estimating date as sightings lack measurement data as the animal is not "worked-up"

 

[Sharkman1885]

Business Rules:

A. Scope
A.1 Crocodile information and tracking

B. Things of interest
B1. Crocodiles
B2. Biological measurements
B3. Location
B4. Reports
B5. Notes
B6. SNAP reference number
B7. Sex
B8. Alive
B9. Date/Time
B10. Identification

C. These things are related as
C1. A Crocodile can be associated with many locations at different Date/Times
C2. A LOCATION can be associated with many crocodiles at many Date/times
C3. A crocodile can have only one set of BIOLOGICAL measurements at one Location at one Date/Time
C4. Biological measurements are associated with only one crocodile at one time at one location (measurements may not change over time)
C5. A crocodile only has one set of identifiers (may change over time)
C6. Identifiers are only associated with one crocodile
C7. A crocodile is either alive or not
C8. A crocodile is either male or female or unknown sex
C9. A crocodile can be associated with one SNAP# at any Date/Time
C10. A SNAP# may be associated with many crocodiles
C11. Notes associated with only one crocodile at Date/time
C12. A crocodile may have many notes


I hope this helps and Im sure I may be forgetting something but this should be most of the big stuff. Let me know if there are any questions. I shall get to work implementing the suggestions already made and hopefully I can get these update forms to work properly by the end of all this.

Thanks all

 

[jdraw]

Thanks for the info.
A couple of things for clarity/confirmation.

What is SNAP#?
Seems like alive or not is associated with a croc "sighting" at Location and Date
Is there any requirement to identify "who" made the sighting info or Note?

measurements may not change over time

but don't crocs grow (new born/adolescent/adult sort of thing?)

And as Pat said in an earlier post --we don't know your business. So a brief description of "a day in the life of croc monitoring" would
be helpful to you and readers. It will tie your business rules into a context. You can include some of the reports and how often these are needed.

Good stuff.

 

[Sharkman1885]

Ok a description of the business:
I am the crocodile response coordinator for FWC (state wildlife agency) When the public sees what they deem as a nuisance crocodile they call the SNAP (Statewide Nuisance Alligator Program) hotline. After that a SNAP# is created and location information and information about the "complainant" taken. Then if needed a Crocodile Response Agent (CRA) is sent to the scene to investigate and sometimes relocate the animal. During relocation measurements are taken and, if not previously handled by FWC, tagged and clipped for future identification.

As far as measurements not changing over time, if the crocodile is an adult the growth rate is much slower. If an animal is measured twice, one month apart, there will be little to no change in the measurements. Two weeks apart there would not be any change at all. Two years apart and you will definitely see a change.

Alive or not I thought it made sense to associate that with the crocodile itself. I guess it would make more sense to associate that with a location though. there I go again thinking like a biologist and not a data manager.

The note or sighting is recorded in a different system completely separate from this database (SNAP) and is referenced by the SNAP#

[edit] The notes are copied into Access and handling reports are submitted by CRAs after handling a croc

 

[Sharkman1885]

Oh and as far as reports. The main official one generated is every fiscal year. The database will mainly be used for querying crocodiles based on limited information. Tag color, partial SCP (Scute Clip Pattern) seen, ect.. This information helps CRAs in the field and is used in management decisions

 

[Sharkman1885]

Pat,

Thanks for the suggestions!. I have just finished moving the Alive flag to the location table. As far as SNAP# that should be easy to move and shouldnt be a problem to do. However, I have changed the relationship to where biology and other info are linked to the Location as opposed to crocodile. Would this rectify the SNAP# location?

 

[jdraw]

What exactly is the table named "ID"? Is there a more meaningful name?

 

[Sharkman1885]

ID is a table full of Identifying characteristics. Those include the SCP (Scute clip pattern) ,the PIT tag (internal tag), and the cattle tag (external tag) info. I had it called Identification before but figured ID would be easier as long as I renamed any PKs , which I think I forgot to do with the "Otherinfo" table

 

[plog]

Year/Day/Month still 3 fields--should be one.

CrocID should not be in OtherInfo table nor Biology table

Location seems like a poor name for what data it stores. Seems that table is actually capturing the data of an Encounter. Location is part of the encounter and may deserve its own table. Further, seems Biology data is actually part of the Encounter, as might be OtherInfo.

 

[Sharkman1885]

everything is part of the encounter. That is basically what the database is. A database of crocodile encounters.

Location was separate but many other tables, as was explained to me, were based on the actual location of the encounter hence the new relationships. As far as the date fields It will take some time to correct that and make sure the query will still work right. rest assured though I will get it done. When I finally have this thing properly normalized I can get started on the entry forms or adding data.

 

[jdraw]

ID is a table full of Identifying characteristics.

Any specific record in that table refers to 1 and only 1 croc, right?
If so, then the relationship would not be 1 to Many. It would be 1 to 1, and could be collapsed into the croc table. (I'm just being devil's advocate here to make sure we're all on the same page).

Table and field names should be meaningful--- especially if you want to pass this off to someone else to maintain.

Seems that Sighting (or Encounter) better describes what you are calling Location. (eg when and where did you sight/encounter the croc)

 

[Sharkman1885]

Currently you are correct in that each specific record in the ID table refers to only one crocodile. However, it is theoretically possible that one of the identifiers could change. A tag could break off and need to be replaced, injury in the wild could change the SCP or the PIT tag could cease to function.

I wanted to go ahead and deal with that possibility now rather than have to deal with it in the future

 

[plog]

As far as the date fields It will take some time to correct that and make sure the query will still work right.

You're trying to change a flat tire while still driving? You need to open a blank database and set up your tables going forward properly. Then when that structure is correct you rebuild your queries on top of it, then your reports, then forms to interact with the data.

Then, lastly, you move the existing data to the new system and start using it.

 

[Sharkman1885]

Pat,
I think I understand. Because of the "handling" "magnets" and alive fields there is more than just location information in that table so the table name should reflect that. Locations can in fact repeat though GPS data prevents the location overlap.
As far as the SNAP# it is generated externally and then when data is entered into the database it is included for easy reference in the SNAP database (internal database not controlled by myself) The SNAP reference number is unique for each location as it refers to the original person who called in about the crocodile.

With regards to the ID, if I put the info under crocodile then I could have a duplicate croc ID if any identifiers change. I could potentially add it to sighting info or even use a sighting FK similar to how biology and Other info is set up. If that will work. Though if that works, then I will have duplicate FKs in the table. I have 376 entries and only 151 individual crocs and thusfar only 151 identifiers. Am I thinking about this wrong?

plog,
I was afraid of losing data in the transfer and I wanted to test things while I changed them to make sure the query was working properly as everything was originally in one huge table which was imported from excel. I dont mind taking the extra time to make sure the data is right. Might not be the correct way to do things but I havent had any major issues with it yet.

 

[jdraw]

Sharkman,

You mention crocs, but don't you handle the odd rogue 'gator?

 

[Sharkman1885]

jdraw,
This database is for crocodiles only. Alligators are monitored via a completely different set of databases, and regulations.

Pat,
The measurements do in fact change over time. Should the biology table actually go in the sightings table? or should the biology table just have a FK from the sightings table? Same question for the "Otherinfo" table that includes notes ect. If I were to put all this data in one table would the table then become a bit larger than what is ideal?

 

[isladogs]

This database is for crocodiles only. Alligators are monitored via a completely different set of databases, and regulations.

In that case the SNAP acronym, whilst it is 'snappy', is misnamed:

SNAP (Statewide Nuisance Alligator Program)

 

[Sharkman1885]

ridders,
LOL yes the name is a misnomer, however it was originally designed for alligators exclusively. Once the agency noticed that crocodiles were also being called in as nuisances the agency just piggybacked the crocodile calls into the same call center. Also the acronym SNAP sounds better than SNACP anyways The SNAP database is for all our nuisance crocodilians (alligators, crocodiles, and caimans) just alligators and caimans are managed differently

 

[jdraw]

This database is for crocodiles only. Alligators are monitored via a completely different set of databases, and regulations

.

The SNAP database is for all our nuisance crocodilians (alligators, crocodiles, and caimans) just alligators and caimans are managed differently

Hmmmm. You might want to review/google information silo.

As suggested throughout, it is important to have a defined requirement. This will put a scope/focus on your project. And getting business rules and processes within that scope described and vetted is critical to database design.

Good luck with your project.

 

[Sharkman1885]

jdraw,
The only thing this (the one I am working on) database does is hold information on crocodiles and allow the user to query the information to find a specific crocodile and allow a user to input information on new crocodiles or new sightings of a crocodile. All other information in the SNAP database is in a separate program, not access, and housed at the main headquarters or wherever their servers are. I am only using the SNAP ref# to allow for a query of the snap database if the user so chooses. This will also allow the user to see how many SNAP reports are made for a given crocodile.

I am unsure where the confusion lies in the scope and business rules.