Can you do stored procedures in SQLite, so that when you update a row it autocalcs the species based on the other two when the row is updated? That gives you both fast queries and the maximal possibility of forgetting how the field ever gets updated. 
1 Like
It’s been a long time since I’ve done anything significant with a RDBMS, but I remember two basic things:
-
It’s quite likely that your small, perhaps experimental, database design will work adequately but databases almost inevitably morph into more complicated beasts and it’s hard to design a database that will grow gracefully.
-
Accomplishing #1 while maintaining efficient joins is hard.
4 Likes
This would mean your table does not conform to the third normal form - Third normal form - Wikipedia - and therefore you risk update anomalies (see Database normalization - Wikipedia). In the case of your database, the two fields won’t change often but if the application is going to be used by others they may make changes that result in inconsistent data (for example if they misidentify a breed and change it without updating the species).
Sorry if you already knew this, but it does mean there is less scope for errors in the results later down the line.
Having more complex queries is better than having inconsistent data, and you can solve this also by using JavaScript/PHP (or whatever language you’re using) to display the correct species, rather than having it in the database.
2 Likes
The original design was that each species would be in its own separate copy of the database. Turnes out users with more than one species on the farm & veterinarians both want a single database with multiple species in it.
The species table has the 6 species I support with the common name, the species generic name, the family, sib-family and then the genus species. So the record for sheep is, Sheep, Ovine, Bovidae, Caprinae, Ovis aries
Sex does directly correlate with species. Each species has 4 sexes Male Female Castrate and Unknown The Sex table has the proper name for each sex and a foreign key into the species table. The fields are sex_name, sex_abbreviation, standard sex name standard abbreviation, display order and speciesid which is a pointer into the species table. So an intact male sheep has a sex of 1. The key for the record for sheep the sex entry that is ram, R, Male, M,1,1 with the last one being the species foreign key. This is necessary because there are duplicated names for the castrate. Wether is the castrated male of either a sheep or goat and gelding is a castrated male of either horse or donkey.
So with the animal record having a pointer into the sex table I can get the species. An animal with a sex of 18 is a Jenny Donkey, one with a sex of 23 is a Barrow Pig and so on.
Where it’s coming into play is during disease traceback there are several pieces of information the official vets need to know. For every animal they need to know the sex, species and all the locations where it has been. For every location they need to know which species are present or have been present there during the timeframe of interest. Since some diseases cross species and some don’t knowing that info allows vets to prioritize their efforts on the locations where the potentially affected species are present or have been present. Some species are carriers of diseases but unaffected by them and then there is the whole issue of fomites. So knowing the ins and outs is also important.
In the practical sense. I am writing code to do a male breeding soundness exam. What options I display for breed depend on which species we are working with. Breeds are their own table but there is also a separate table for animal breed because some animals are crosses between breeds. From a registration standpoint any breeds that allow grading up require knowing the percentage of blood of the pure breed during the various generations. Eventually, in grading up breeds, you can get a registered “purebred” animal by continually breeding to the pure line. So the system has to track where in the process any given animal is. But for disease traceback needs there are only a relatively few “standard” breeds for each species and animals in the grading up process are all considered crossbred.
This doesn’t make any sense to me. Everything has to be in the database. I do not know or use PHP or JavaScript so I’m probably missing something important in why you mentioned those. I’m using Android Java and Python. I still have to query the database for animals and get their species. If I do it the way I am doing it now I have to join the animal and sex table and then the sex and species table to get the names for the species of a particular animal. The advantage of a direct link would be to avoid the intervening link. My green line vs the red one in this picture.
I see now. The important thing is that there are no transitive dependencies in the table (so if you change one field then other contains invalid data).
I mentioned a programming language because I thought you could use a decision structure to find the species, that assumption was wrong.
And it’s going up.
“Beginning September 24, 2022, new pricing becomes effective for all Claris new and renewal customers. Licensing costs will increase by 10 percent on all Claris products.”
What I really didn’t like about FileMaker was the upgrade treadmill I felt I was on, mostly having to do with compatibility issues between the different versions but also for the costs associated with keeping up.
Filemaker will get a name change to ClarisPro this fall. It will be a free service for one user. Will cost for deployment to more people.
Here is the link to the announcement (it’s long).
1 Like
I used FM in the early 90’s to create an H.R. database and remember it being fairly inexpensive. I didn’t need approval to purchase 4 or 5 copies. I thought about using it for a personal project a few years ago and immediately ruled it out.
Does that mean one user on all platforms? ie can you access your file/db on macos and ios/ipados?
I only know how it works now. I have FileMaker Pro on my Mac and FileMaker Go on my iPad. I can only create a database on the Mac. I can save the file to the iPad and use it on the iPad. However, it does not synch. I would then have to transfer the file back to the Mac if I wanted to use it there or if I wanted to make changes to the database structure. It’s almost like a sneaker-net situation. I have since changed to AirTable to easily have access to all of my databases anywhere. As I recall, in Filemaker, you can’t even save the file to iCloud Drive or something like Dropbox and share the file that way. I’m not sure if this will change this fall but I would hope so. I think they should have a less expensive plan for individual users.
3 Likes
FWIW QuickBooks doesn’t conform to the third normal form either. It’s amazing how many pieces of data are outright duplicated in multiple tables. Theoretically their software keeps it all in sync, and I’m guessing that it’s for the same reason @OogieM is contemplating it - faster queries.
1 Like
In my case the actual data are only in one place, but the foreign keys can be duplicated all over as required.
Looking at your ER diagram, the red arrows are red arrows are the better way of maintaining referential integrity. Were you to remove the link between sex and species you could end up with an animal that has a sex inconsistent with its species. Equally, however, you implement the green animal to species link, there is a risk of inconsistency between the two routes to species.
The question is how to retain the consistency of you original (red links) design whilst taking advantage of the simplicity of animal having foreign keys to both species and sex. My suggestion is that animal should be a view, not a table.
Normalise all the base tables in which data is actually stored, then build views through which data is retrieved. You can, of course, build views on top of views. That makes queries easier, albeit at the expense of slightly more difficult inserts.
General databases can’t insert directly into a view in the same manner as a table. What you can do, however, is attach an INSTEAD OF INSERT trigger to the view. Instead of trying to do the insert (which would fail as an illegal operation) the database engine calls the trigger function. That function (which you have to write) can pull apart the Colin’s of the input (view) record and put them into the correct underlying tables. You can even go so far as having the trigger set appropriate defaults; you could perhaps have a Ram view over Animal that doesn’t have FKs to Sex or Species (as they’re implicit in an animal being a ram) but sets them using its instead of insert trigger. Probably, in fact, Ram should be a view over Sheep, which is itself a view over Animal.
What I’m really saying is that views and triggers are handy for simplifying what a database user sees and hence the complexity of queries they need to make. If you’re not using them it’s probably worth spending a little time investigating.
2 Likes
This is a big reason I left FMP for tapforms. As Sparky says, my data is no good to me if I can’t access when I need it.
1 Like
Does Airtable’s pricing seem to be a little “nickel and dime” to anyone else? For the $10 month plan, there’s a 5,000 record limit. That’s not a lot really. And the 3 extension limit seems to be inadequate given that a lot of these things are just included with most database applications (eg csv import, chart view, summary fields, pivot tables).
I have not used any views at all because views are read only in SQLite. While I don’t do deletes I do updates and you can’t do that in SQLite on a view.
So I had basically eliminated views from my thinking.
Id love to talk more about that as that is a very different thnking. To me animal has to be a table. But as I said I have not really investigated or used views ever. As far as I know views are only availabel on Android if you are using the Room system which I am not. The project has been in use for 10 years and this conversion to a systme that handles multiple species and documenting pedigrees is the first real database redesign in that time.
The users in my system don’t really make queries. I tightly define the functions and fields I allow to be searched and queried. As the programmer I write the queries that get called my my program. There isn’t a plan for a general way for a user to edit or query the database. Now, because it’s just a simple open SQLIte file a smart user can but if they do then they ar eon their own in terms of making it all work correctly. If they are running from within AnimalTrakker then I can control all the queries so it’s a once time task to get them right.
Maybe “columns” and not “Colin’s?” Sorry to be so picky. Especially since your level of database understanding exceeds mine! 
P.S. It’s perfectly acceptable to reply “damn autocorrect!”
I couldn’t agree more: After “normalize obsessively”, “use more views than you think are necessary” is something that I wish I’d have been advised to do much, much earlier on. Views create an abstraction layer between the data consumption parts of an application and structure of the database. That’s invaluable for anything beyond the most trivial of applications.
1 Like
Damned autocorrect, coupled with failure to proof read when using my iPhone. There’s a moral there somewhere about desktops….
1 Like