Planning an events based app

[Dairy Farmer]

I am in the initial stages of planning a new app. It will be used to record animals and events pertaining to each

animal. The app will store data as events relating to an animal. Some events would have mulitiple occurances. i.e.

medical treatments.

My idea is to have an initial table with only 3 fields: GUID, DOB and Breed. These are the only fields that would

never change for an animal. At birth/purchase/lease an animal is assigned a GUID.

Next would be a series of tables that I will refer to as Global Tables that would store items that would get

assigned to a GUID at some stage. These tables would consist of a 1 or 2 fields only.

Lastly are Event Tables. These assign an event to a GUID. Every event tables would have a Date field.

Example:
An anmial calves and the calf is entered onto the system;
Table_Animal_GUID
GUID = 11001
DOB = 01/01/2011
Breed = Cross

Today I receive 3 RFID transponders and I enter them in the table;
Table_Global_Transponders
Transponder = AAF123456ABC
AAF123456ABD
AAF123456ABE

Two month later I insert a transponder into the calf and assign it;
Table_Animals_Transponders
Date = 01/03/2011
GUID = 11001
Transponder = AAF123456ABC

Would this be a viable route to take? I initially thought of using lookups, but very few fields would remain constant.

Ear tags change, transponders are replaced, the animal sex may change,...

WHAT an animal can chage sex? Yes they can. A heifer is preg checked by the vet and found to have no viable

reproductive organs. So the animal must be declaired a Queen. Bulls get, ouch, the snip and become Oxen. Or, more humiliating, become teaser bulls.

PS: There will be zero table lookups or table validation. All will be handled at form level.

 

[Steve R.]

Re: Planing an events based app

My initial reaction is that you are not capturing enough information, but then that may not be your intent. For example, you have date-of-birth, but not a date-of-death or date sold. Additionally you may want to record the "name" of the parents.

I initially thought of using lookups, but very few fields would remain constant.

How are you planning to use "lookups"? Filters and/or SQL are alternative forms of retrieving data.

Today I receive 3 RFID transponders and I enter them in the table;
....
Two month later I insert a transponder into the calf and assign it;

These tables can be combined. Need to have "logic" for whether the transponder is in-use or available. Technically, if it is inserted into an animal it would not be available. Again you may want to have fields for when the transponder was "trashed", is in working condition, and its transmitting code (assuming that your transponders have a unique RFID tag).

WHAT an animal can chage sex? Yes they can. A heifer is preg checked by the vet and found to have no viable reproductive organs. So the animal must be declaired a Queen. Bulls get, ouch, the snip and become Oxen. Or, more humiliating, become teaser bulls

For this situation, you need a table with all these options and use it as the source for a drop down list when entering the animals "sex".

 

[Lightwave]

Re: Planing an events based app

I am in the initial stages of planning a new app. It will be used to record animals and events pertaining to each

animal. The app will store data as events relating to an animal. Some events would have mulitiple occurances. i.e.

medical treatments.

My idea is to have an initial table with only 3 fields: GUID, DOB and Breed. These are the only fields that would

never change for an animal. At birth/purchase/lease an animal is assigned a GUID.

Next would be a series of tables that I will refer to as Global Tables that would store items that would get

assigned to a GUID at some stage. These tables would consist of a 1 or 2 fields only.

Lastly are Event Tables. These assign an event to a GUID. Every event tables would have a Date field.

Example:
An anmial calves and the calf is entered onto the system;
Table_Animal_GUID
GUID = 11001
DOB = 01/01/2011
Breed = Cross

Today I receive 3 RFID transponders and I enter them in the table;
Table_Global_Transponders
Transponder = AAF123456ABC
AAF123456ABD
AAF123456ABE

Two month later I insert a transponder into the calf and assign it;
Table_Animals_Transponders
Date = 01/03/2011
GUID = 11001
Transponder = AAF123456ABC

Would this be a viable route to take? I initially thought of using lookups, but very few fields would remain constant.

Ear tags change, transponders are replaced, the animal sex may change,...

WHAT an animal can chage sex? Yes they can. A heifer is preg checked by the vet and found to have no viable

reproductive organs. So the animal must be declaired a Queen. Bulls get, ouch, the snip and become Oxen. Or, more humiliating, become teaser bulls.

PS: There will be zero table lookups or table validation. All will be handled at form level.

Only point I would add is that you only need a separate table for the RFID chips if it is important to record the history of what the calf was tagged with. (it might well be)

If not then it would work just with a chip number field in the head cow table.

I haven't bothered with a sub table for chip numbers in an events database I set up, as competitors only have one chip for a race.

Useful Hint
If you do have this flat structure it's very easy to ensure that you don't overallocate the chips - (ie allocate the same chip to more than one cow).
Simply within the table design - field chip - set Indexed to Yes(No Duplicates). This would prevent Steve R's requirement for logic as the indexing prevents you allocating chips already in use.

After that if you try to allocate a chip that is already allocated to another cow it will trigger a warning refusal.

If you don't have that flat structure I guess if a chip is in a cow you could set it up so it is not available for selection within for example a dropd down.

Curious...
How reliable are your chips?
I take it they are passive?
Ours seem to regularly just stop working
Do you have gates that automatically track numbers in and out of things like the milking shed?
Does anyone except you require the chip numbers?
We have a further level of abstraction on our chips. They have internal hex reference (which looks very similar format to yours this is the reference picked up by the timing mats) and an external simple number which is less of a tongue twister.

 

[Dairy Farmer]

Re: Planing an events based app

There will be a lot of tables. I just gave an example.

So there will be a table for the plastic ear tag. This is the number that we refer to for the name of the cow. These are changed everytime an animal calves. The colour changes but the number stays the same. Sometimes we buy in animals and they may have the same number as one that I use. In this case the number is changed. So I need to record: Date, GUID, Number

Other tables will be: Metal Ear Tag, Lactations (calvings), AI/Heat/Service, Medical Problems, Medical Treatments, Groups, Culls/Sales/Deaths, Inter Farm Movements, Preg Tests, and so on. All will retate back to the GUID.

I do plan to have calves automatically have a GUID assigned when the Dame calves. i.e: 2011-0001 (DOB - sequence of calf number). Now I will have a reference to that animal in the calvings table. Because it will be related back to the Dame GUID, there will be references back to the AI/Service table, which will retale to the Sire table. So the whole progeny will be accessable via the GUID.

Table_GUID
2000-0025
2000-0026
2000-0027

Table_Global_Transponders
AA00001ABA
AA00001ABB
AA00001ABC

Table_Transponders
06/02/2000 AA00001ABA 2000-0025
12/02/2000 AA00001ABB 2000-0026

Table_PlasticEarTag
07/02/2000 20025 2000-0025

Table_Global_Sex
Heifer
Cow
Queen
Bull
Ox

Table_Sex
06/02/2000 Heifer 2000-0025
06/02/2002 Cow 2000-0025

Global tables will hold info such as: Breed Types, Sex Types, Service Types, Medical Problems, .....

On lookups, what normally happens is the table/form will have fields; Name, Sex, Breed. When you go to enter sex or breed you get a drop down from another table to enter your choise. This is fine if the field never changes. I need to have a dated record of the change. So it will actually be done in a "sub form".

The same with the chip. I need to have a dated record of which chip was used where. Say I use a chip on an animal. Later that animal dies or is sold. I can reuse that chip. But I will have dated records of when that chip entered the dairy. I need to refer to the which animal had that chip on that date.

We are just in the process of implimenting chips. It is my job to pilot the project. So far I am looking at the button type that goes in the ear, or is the button on the plactic ear tag. Passive LF would be my choise as they sound to be the most reliable. I have no need to write back to the chip.

I plan to have a either a high traffic gate on the walk way into the dairy, or 2 gates where the cows enter the milking string (palor is a 24 point swing over). The advantage of the gate before the dairy is that I can see what animals have come in for milking before milking commences. There would also be a hand held wand for spot checks.

So farms, especially those with rotaries, have necklace chips. These are better for automated feeders. I do know that these are notoriously unreliable and exensive. Mainly because they are read/write and have the added hardware. We do not use automated feeders though, so it will be urely for head counts.

 

[Lightwave]

Re: Planing an events based app

Interesting... When I said passive I meant they don't emit a signal. Apparently they use active chips with tour de france. Each rider has a RFID chip with battery that emits a signal.

The receivers/transmitters are built into the road and the timer comes along and hooks up to them.

The chips we use are passive and get their energy from the transmitter / which also receives the signal once the chips have been charged.

Got me thinking wonder whether we could use cow chips on our timing system!!!

 

[Dairy Farmer]

Re: Planing an events based app

You get Active and Passive RFID chips.
Passive chips are activated by the reader.
Active chips broadcast at timed intervals.

You also get Read Only and Read/Write.

 

[Steve R.]

Re: Planing an events based app

There will be a lot of tables. .... So there will be a table for the plastic ear tag. .... Other tables will be: Metal Ear Tag, Lactations (calvings), AI/Heat/Service, Medical Problems, Medical Treatments, Groups, Culls/Sales/Deaths, Inter Farm Movements, Preg Tests, and so on. All will retate back to the GUID. ... Now I will have a reference to that animal in the calvings table. Because it will be related back to the Dame GUID, there will be references back to the AI/Service table, which will retale to the Sire table. ...

Based on a quick reading, you may not have tables that are "normalized". It all depends on how you are using/relating your tables. For example you propose separate tables for "metal" and "plastic" ear tags. Unless there is some compelling reason, these tables could be combined into one table and differentiated through the use of a "material" field (assuming you need to differentiate by material.). You may even be able to integrate the RFID chips into such a table.

it also appears that you may have separate tables for tracking an animals birth/death/sale/sex. These would be specific properties that belong to an animal and would therefore all be located in one table. (The sex table would be used as the data source for a drop-down list.)

What you are putting together is complex and I don't know the nuances of how all this fits together, so I hope my advice is not off-base.

 

[Dairy Farmer]

All tables would relate back to a GUID which will be stored in the "main" table. That table will only have fields that will never change. i.e. GUID, DOB and Breed. All other info is subject to change or may have multiple entries. i.e. An animal may have more than one RFID in its lifetime, or a RFID may be assigned to more than one animal during the RFID's lifetime.

Metal ear tags are not always the same number as the plastic ear tag. There is also a good chance of duplication, mainly where animals are bought in. Some farmers will start at 0001 and keep going to 9999. Then start again. Others use a 4 or 5 digit number. The last 3 being the order a calf is born in the year. The prefix will represet the year. I use 4. So the first calf born this year is 1001. In 2021 the first calf will be 1001. Cows do remain productive over the age of 10. But generally become pies after 12 or 13 years. It is easy to see the difference in a 2 year old and a 12 year old. Much the same as the difference between a 25 year old woman and a 80 year old woman. Gravity!

A lot of info is caputred from hard copy. These are filed. In the meantime things change. If we need to look back a year or two, I need to know that, on that date, an animal had that number.

 

[Steve R.]

Good use of Access. Good Luck!