SQLite Database Philosophy Question


#1

Just musing out loud here.

What are the pros and cons, as you see it, of enforcing foreign key limits in complex sqlite databases?

Playing with whether to do that within LambTracker and can’t seem to find out much about why you would or would not use them. SQLite has FK constraints turned off by default but no one seems to explain why. Best info I’ve found is that it can make inserts slower as the constraints have to be tested each time. Best reason I’ve found for doing it is that it requires the database to maintain more integrity and you can’t quickly fix some errors without filling in all the dependent table data.


#2

I’m by no means an expert, but I have always though that maintaining referential integrity via foreign key constraints is one of the main reason to use an RDBMS for anything nontrivial?


#3

That’s sort of my understanding too. I’m wondering if because I’m running on a small mobile system whether the overhead of the indices is the reason it’s not usually done?

OTOH I can see cases where it would be faster to collect the data for an insert in the field and manage the links later. Sometimes when working the sheep you just need to get it done and fix the records after the fact. Case in point. LambTracker maintains a full record of all drugs given to a sheep. That’s fine when it’s something the farmer does. But we had a case where a vet came out in an emergency and treated a sheep. I ended up taking all the data on the drugs etc. down on paper and adding it in later because I couldn’t add a drug on the fly and then give it. Plus it really was a life or death situation, not the time to be struggling with accurate data entry. Sadly we lost the sheep anyway but it is an argument for not requiring FK constraints.


#4

In such a case as that, maybe the app could have a workflow (perhaps implicit/automatic) to add a new drug as part of its tracking?

I don’t do any (non-web) mobile development so I can’t speak directly to apps, but I get a bad feeling whenever I consider pushing integrity logic into the app layer. It’s a similar feeling as when I think, “This would be so much quicker to do with a global variable…” :innocent:


#5

One could use constraints to ensure integrity, but allow some flexibility by, for instance, having a dummy drug that indicates it should be fixed later.

I haven’t seen your app, but progressive search fields that allow adding items are nice. This would avoid putting off the chore of adding a drug, and maintain integrity as well.

SQLite can allow a FK to be NULL, also allowing one to postpone adding related records.

Finally, according to this FAQ the constraint is off by default for backward compatibility.


#6

Ah but global variable are so helpful in so many cases! :slight_smile: It just means you have to name them so you can’t screw them up later.


#7

Do you have an example of an app with progressive search fields in the sense you mean. My initial interpretation is search for a large set then a refinement search within results. But I’m not sure that’s what you mean.

In the sheep drug case I could first say search for an analgesic then within that for bute or something similar.

Currently in LambTracker while I do track the type of drug, because dewormers and coccidiostats are treated differently, I usually don’t care as the number of drugs available at any given time is so small that just looking at the entire list is usually ok. The federal regulations are focused on tracking dates given, dosage, slaughter or milk withdrawal data and lot number.

Still debating. I feel there is some underlying reason why in Android it seems no one has FKs enabled.


#8

I just felt a great disturbance in the Force, as if millions of Computer Science professors’ voices suddenly cried out in terror and were suddenly silenced. :laughing:


#9

I know but in the 70s we used them a LOT.


#10

Yeah, but you used gotos back then too :wink: (I’m teasing about the global variables, btw)


#11

The tagging system here on Discourse is a good example. You can begin typing a tag, and partial matches are listed. You also have the opportunity to create a new tag at the same place.

Admittedly, I don’t know the nuances or scope (number of drugs) of the issue, and this might be impractical.


#12

OK got it, The issue in the sheep drug case is that every drug has slaughter and milk withdrawal times. Most drugs are not approved for sheep so you must have a vet with a valid client and patient relationship to even use them and drug dosages are much more fluid. When giving a drug to any sheep there is an automatic hold placed on the animal based on the slaughter or milk withdrawal time. A prescription or at a minimum the vet prescribing the drug must be attached to each drug prescribed and if an approved drug is used at a non-approved dosage rate or frequency you need to document that as well. The lot numbers must be kept for every treatment as they can be required to be produced to any state or federal vet at any time.

I’ll have to think whether I can implement such a search system within the context of the links needed to all the various records that get added and updated whenever a drug is given. At a minimum there has to be some enforcement of finishing filling out the drug details within some specified time period. But if you do that how do you go back and edit the sheep record so that the alert for an animal on a slaughter withdrawal is active? things to ponder…

As an overall issue with LambTracker I do not do enough (read any) proper data validation on inputs. This has resulted in some spectacular failures/bugs in the field. A sheep with a ’ in their name fails on a name search due to the fact that Android uses ’ in multiline SQLite queries to denote new lines.

In many ways foreign key constraints are part and parcel of the overall issues with the fragile nature of LambTracker data input verification.

Going forward there needs to be much more robust data validity checking. When we get into things like adding registry data to register new lambs one critical thing is to check for gestational errors (gestation periods that are not normal for the species, or a lamb being born before the sire or dam was born, possibly indicating wrong sire or dam identified or more likely an error on entering in the birth date of the sire or dam). Pedigree errors, tag errors and tracking are also places where data needs to be verified before entry. This isn’t a problem for lambs born into the LambTracker system where the dam is scanned and the sire calculated from the records of both gestation period and the ram the mother was exposed to or the AI insemination date and time but is a problem when entering in historical data into a LambTracker system to complete the records. I’ve run into that in my own flock. Before LambTracker I used spreadsheeps and copying and pasting or re-entering in the data to be imported into LambTracker resulted in some errors. I probably should document the workflows I developed to identify and handle those situations and add them into the LambTracker manual.

This is EXACTLY the sort of programming discussion I am missing and really appreciate.

Thank you for continuing to discuss this here.


#13

My lab is starting soon…

Perhaps a different perspective would help: rather than beginning at sheep and attaching meds, begin at meds, and select the sheep. The sheep, in this case, would be the easier lookup entity (and more invariant), rather than the complexities of drug administration you detailed above.

Instead of noun-verb, the interface (or an interface) can be verb-noun. What are you doing, whom are you doing it too.


#14

The actual order of lookup is more a function of how shepherds actually work with the flock than it is the software.

For example, when I am setting up to give a vaccine, I can select the vaccine from a list of the ones that are available in the sheep management task. That data (which vaccine) is saved between sheep. As I scan a sheep in the chute, I can select a checkbox to show whether that animal was given the suggested vaccine or not and save the data. The same thing works for wormers and other drugs. What happens behind the scenes when I save that data (which includes the date and time of administration, location and the specific lot number I used because it read that from the drug table when I set it up) is that the program does a lookup on the drug, finds the relevant withdrawal data and sets an alert on that individual animal as required. Alerts are a concept of a popup screen that happens any time you look up a sheep that must be dismissed before you go on. It sounds cumbersome but in practice it’s a very user/field friendly way to track critical information.

I use them to set up sorting alerts, so right now as I decide that one or more ram yearlings has proven that he belongs in the freezer I add a Butcher alert to his record. In 3 weeks when I am sorting our sheep to take to slaughter I can run the entire flock of ram lambs in and quickly pull into one pen the animals with butcher alerts. From that subset of sheep I can decide who goes on that trip, by picking the ones with the worst behavior and the largest ones. The alert is designed so that it should be easy to integrate an automatic sorting gate if they ever become inexpensive enough for a small flock.

Drug administration is a difficult corner case for the FK concept because while for standard things like a vaccine or dewormer, I can enter in the drug details when I buy the drug, in emergency situations you can’t do it easily. I have in a few cases when the drug was the same but the lot number differed, used the old drug record and then later duplicate that record, change the lot number and update the sheep drug table to point to the new copy. Corner cases are where the FK stipulation falls apart in several basic management tasks.