Thank you to everyone who answered my request for SQL resources.
I’ve a good set to choose from.
Time to get to work …
2 Likes
Is there an advantage in learning SQL and using an app like Tableplus over using something like Ninox or Filemaker?
1 Like
I use to have a copy of FileMaker Pro and loved it! I found it to be quite intuitive: I taught myself.
Is SQL Studio have a big learning curve? Because it would get rather expensive. $16/mo, $99 per year.
1 Like
If you are just looking to learn SQL then something like the free SQLiteStudio would be an option.
Of the resources suggested above I started with (Getting Started with SQL by Thomas Nield. And it uses SQLiteStudio for its examples.
Good luck and have fun.
1 Like
Thank you ever so MuCH, Mevet!
But it cannot be used as a “Developer has not been verified”. ??
If you trust the developer then hold down the control key and click on the app’s icon. Then select “Open” from the resultant menu. You’ll get a dialog asking something like do you really want to do this. You do. And then you’re good to go.
Here’s a link to a site that walks you thru this.
1 Like
Time vs. money is a useful consideration. Filemaker is crazy-expensive compared to most SQL-based apps, but the SQL stuff would probably take more time to throw together, both design-wise and learning-wise.
The upside of learning SQL is that you’ll be able to use dozens of apps, as well as having some skills that help if you’re doing other kinds of programming.
Basic SQL is not that hard, until you get into weird edge cases. Basically it’s just a SELECT, UPDATE, or DELETE command, followed by what you want to get from the database, update in the database, or delete from the database.
SELECT * FROM customers WHERE firstname LIKE 'Helen'
UPDATE customers SET lastname='Smith' WHERE firstname LIKE 'Helen'
DELETE FROM customers WHERE firstname LIKE 'Helen'
The first gives you every database row where the first name is Helen. The second updates every Helen’s last name to Smith. The third deletes all the Helens from your database.
When you start getting data from multiple tables at once then it gets a little trickier, but still not that bad. Basically, if learning SQL scares you, don’t let it. 
5 Likes
One suggestion don’t delete anything in the database for a while. SQLite can handle large datasets. If you never delete and only insert or update things are not too bad and you can almost always recover easily. BTDT
Also, when you do start doing complex queries it helps to format it so you can read the thing later. It may look silly but the leading commas are a whole lot easier to locate when you have a spare or are missing one and your query crashes and you have no idea why. Make the tables clean, clear and the fields obvious as to what they are. Future You will not remember abbreviations and in SQL it doesn’t matter much if your table names and fields are descriptive. There is a big debate on both numbers of tables and long table names. Yes, it can maybe hit you performance wise but not until you get to hundreds of thousands of records at a time. When your data is that big you will know how to fix it and Moore’s law may make the effort irrelevant anyway. So when in doubt add a table.
Here is a query I run all the time and adapt to get 90% of my reporting needs done. I just comment or uncomment the appropriate fields.
-- Gets whole list of current sheep properly including sheep without a sire or dam or without EBVs
-- This version only gets sheep located at Contact number 1 which is us at Garvin Mesa with our federal premise id
SELECT
sheep_table.sheep_id
, (SELECT
tag_number
FROM id_info_table
WHERE
official_id = "1"
AND id_info_table.sheep_id = sheep_table.sheep_id
AND (tag_date_off IS NULL OR tag_date_off = ''))
AS fedtag
, (SELECT
tag_number
FROM id_info_table
WHERE
tag_type = "4"
AND id_info_table.sheep_id = sheep_table.sheep_id
AND (tag_date_off IS NULL OR tag_date_off = ''))
AS farmtag
-- , (SELECT
-- tag_color_name
-- FROM tag_colors_table
-- INNER JOIN id_info_table ON tag_colors_table.id_tagcolorsid = id_info_table.tag_color_male
-- WHERE
-- tag_type = "4"
-- AND id_info_table.sheep_id = sheep_table.sheep_id
-- AND (tag_date_off IS NULL OR tag_date_off = ''))
-- AS farmtagcolor
-- ,(SELECT
-- tag_number
-- FROM id_info_table
-- WHERE
-- tag_type = "2"
-- AND id_info_table.sheep_id = sheep_table.sheep_id
-- AND (tag_date_off IS NULL OR tag_date_off = '')
-- AND ( id_info_table.official_id IS NULL OR id_info_table.official_id = 0 OR id_info_table.official_id = ""))
-- AS eidtag
-- Can add or delete items here by adding or removing -- in front of the item.
-- , flock_prefix_table.flock_name
, sheep_table.sheep_name
-- , codon136_table.codon136_alleles
-- , codon141_table.codon141_alleles
-- , codon154_table.codon154_alleles
, codon171_table.codon171_alleles
-- , sheep_evaluation_table.trait_score11 as weight
-- , sheep_evaluation_table.age_in_days
, sheep_ebv_table.usa_maternal_index
, sheep_ebv_table.self_replacing_carcass_index
-- , sheep_ebv_table.ebv_birth_weight
-- , sheep_ebv_table.ebv_wean_weight
-- , sheep_ebv_table.ebv_post_wean_weight
-- , sheep_ebv_table.ebv_hogget_weight
-- , sheep_ebv_table.ebv_adult_weight
-- , sheep_ebv_table.ebv_post_wean_scrotal
-- , sheep_ebv_table.ebv_number_lambs_born
-- , sheep_ebv_table.ebv_number_lambs_weaned
-- , sheep_ebv_table.ebv_maternal_birth_weight
-- , sheep_ebv_table.ebv_maternal_wean_weight
-- , sheep_ebv_table.ebv_lambease_direct
-- , sheep_ebv_table.ebv_lambease_daughter
-- , (
-- sheep_table.birth_type
-- + sheep_table.codon171
-- + sheep_evaluation_table.trait_score01
-- + sheep_evaluation_table.trait_score02
-- + sheep_evaluation_table.trait_score03
-- + sheep_evaluation_table.trait_score04
-- + sheep_evaluation_table.trait_score05
-- + sheep_evaluation_table.trait_score06
-- + sheep_evaluation_table.trait_score07
-- + sheep_evaluation_table.trait_score08
-- + sheep_evaluation_table.trait_score09
-- + sheep_evaluation_table.trait_score10
-- ) AS overall_score
-- , sheep_evaluation_table.sheep_rank
, cluster_table.cluster_name
, sheep_table.birth_date
, sex_table.sex_abbrev
, birth_type_table.birth_type
, sire_table.sheep_name as sire_name
, dam_table.sheep_name as dam_name
, sheep_table.alert01
FROM
(SELECT
sheep_id, MAX(movement_date)
, to_id_contactsid
, id_sheeplocationhistoryid
FROM sheep_location_history_table
GROUP BY
sheep_id)
AS last_movement_date
INNER JOIN sheep_ownership_history_table ON sheep_table.sheep_id = sheep_ownership_history_table.sheep_id
INNER JOIN sheep_table ON sheep_table.sheep_id = last_movement_date.sheep_id
INNER JOIN codon136_table ON sheep_table.codon136 = codon136_table.id_codon136id
INNER JOIN codon141_table ON sheep_table.codon141 = codon141_table.id_codon141id
INNER JOIN codon154_table ON sheep_table.codon154 = codon154_table.id_codon154id
INNER JOIN codon171_table ON sheep_table.codon171 = codon171_table.id_codon171id
LEFT JOIN birth_type_table ON sheep_table.birth_type = birth_type_table.id_birthtypeid
LEFT JOIN sex_table ON sheep_table.sex = sex_table.sex_sheepid
LEFT JOIN sheep_table AS sire_table ON sheep_table.sire_id = sire_table.sheep_id
LEFT JOIN sheep_table AS dam_table ON sheep_table.dam_id = dam_table.sheep_id
LEFT OUTER JOIN sheep_cluster_table ON sheep_table.sheep_id = sheep_cluster_table.sheep_id
LEFT JOIN cluster_table ON cluster_table.id_clusternameid = sheep_cluster_table.id_clusterid
-- LEFT JOIN flock_prefix_table ON sheep_table.id_flockprefixid =flock_prefix_table.flock_prefixid
LEFT OUTER JOIN sheep_ebv_table ON sheep_table.sheep_id = sheep_ebv_table.sheep_id
-- INNER JOIN sheep_evaluation_table ON sheep_evaluation_table.sheep_id = sheep_table.sheep_id
-- Edit the date to be the most recent run of EBV data by changing the date below
-- Modify this to be the latest EBV run date. Should be a way to get this automatically .
AND sheep_ebv_table.ebv_date LIKE "2021-10%"
WHERE
sheep_table.death_date = ""
-- Set to be us as the owner and location but can be edited to handle other places and owners.
AND sheep_ownership_history_table.to_id_contactsid = '1'
-- 1 is Black Welsh Mountain
AND sheep_table.id_sheepbreedid = 1
AND last_movement_date.to_id_contactsid = 1
-- If want to add evaluations add this and uncomment the last INNER JOIN above
-- AND eval_date LIKE "2021-11-%"
-- To get only a single sex add this in the WHERE clause
-- Edit for sex by making Ram sex = 1 Ewe Sex = 2 Wether sex = 3
-- AND sheep_table.sex = 1
-- To get no butcher or sell add this in the WHERE clause
-- AND (sheep_table.alert01 NOT LIKE "%Sell%"
-- AND sheep_table.alert01 NOT LIKE "%Butcher%")
-- To get only butcher or sell add this in the WHERE clause
-- AND (sheep_table.alert01 LIKE "%Sell%"
-- OR sheep_table.alert01 LIKE "%Butcher%")
-- To get only a specific alert add this in the WHERE clause
-- Change the text between the % to be what alert you are looking for.
-- Common ones include Sell, Butcher, Keep and so on.
-- AND sheep_table.alert01 LIKE "%Sell%"
-- AND sheep_table.alert01 NOT LIKE "%Ship%"
-- To get only older sheep not current year lambs add this in the WHERE clause changing the year as required
-- To get only this years lambs change the < to a >
-- To get a specific year change the < to LIKE
-- AND sheep_table.birth_date < "2021%"
ORDER BY
sex_table.sex_abbrev ASC
, cluster_table.cluster_name
, sheep_ebv_table.self_replacing_carcass_index DESC
, sheep_table.birth_date ASC
4 Likes
Aeon Timeline 3 has generalized the previous version’s database capabilities. There’s a free 14 day trial. https://timeline.app
This app started with time and built on it, so it’s not a typical database app but is definitely refreshing and different. If there’s anything that makes up for losing Bento, it could be this.
3 Likes
I grew up with 4D. I really love it. But they do not seem interested any longer in the hobbyist market so their offerings are expensive. Having learned it at some point, it is a fabulous tool.
1 Like
Thanks @webwalrus and @OogieM
My requirements are rather meagre and certainly won’t run into 100,000 records.
My reason for asking is that database apps always seem to have their own scripting language that is required for anything rather than a rudimentary database. I use databases on websites and understand very basic commands, but thought if I have to learn scripting anyway why not learn SQL? Plus as you say database apps are expensive and monetising every aspect of their database which now includes syncing, api access, bandwidth and the like.
Is TablePlus a recommended way to go on macos and ios?
Roughly how long would it take to learn SQL at 5 hours a week?
1 Like
My experience is that SQL is pretty straightforward to start with, but then turns into an arcane syntactic mess. Recursive queries — any part-part of structure for example — are a classic example of this. It takes a lot of headspace to get to grips with how it’s implemented, even more to get the desired results and then you find that the query isn’t portable between database engines (eg SQLite and PostgresQL).
5 Likes
My best recommendation for learning SQL is a paid interactive course, https://www.sqlhabit.com/ - with 5 hours a week, you will have the basics you need to get going in a month. (I’m not affiliated at all to this)
2 Likes
I would also suggest learning SQL - it is an established and hugely spread technology. You are not locked into one application, but can try multiple and find out what suits your need.
My favourites:
- https://postgresapp.com/ (free) to run Postgresql databases on your Mac
- Postico 2 (paid) to manage your Postgresql databases
- https://sqlitebrowser.org/ (open source) to manage SQLite databases
Tableplus is a nice tool as well, I prefer the two solutions above however as they are specialized for the respective SQL flavour.
1 Like
SQLIte for rudimentary stuff can be picked up and working for simple databases in about 8 hours of dedicated time. You can be running with simple things in less than that but the 8 hours will cover you for basic joins.
IMO Skip any paid for courses and jump right to buying a single decent basic book. Then just start playing with it.
Save the $ for when you are beyond the basics and need to go deeper or hit a wall with the database design.
2 Likes
What an interesting connection to make! I haven’t looked at this app in a while and didn’t see it at first, but then I noticed the spreadsheet view. Is that what you are talking about?
Edit: I just upgraded to version 3 of Aeon Timeline and notice that they have also added a Relationship tab next to the Spreadsheet tab. Sounds even more like a database now. I’ll have to see if I can make a Bento-like app out of it. 
2 Likes
If that’s the case, then yes - definitely learn SQL.
If you do your own programming / scripting on your Mac, and enjoy tinkering, you could even look into SQLite libraries for your language of choice. Those libraries are almost guaranteed to be free, and that basically lets you do straight-up SQL against a file on your disk, rather than via some fancy database engine. The downside is that you’d have to write the code to create the database, etc. - but you’d definitely know what you were doing on the other side.
I agree with Oogie that you’d be up and running with basic stuff in a pretty short period of time. As for learning methods, pick whatever works best for you historically.
If you learn best by video, and you live / work somewhere where your library / university / whatever has Lynda (now LinkedIn Learning) courses available for free, look into that. And of course if money is at all an issue, your local library likely has a number of books about learning SQL.
Best of luck!
1 Like
I’ll add one note of caution: Mistakes made in the design of a database can haunt you for years (decades, even) and the longer you use a database the more difficult it becomes to correct early mistakes. In the early going, experiment a lot and be willing to throw things away early and often. Normalize obsessively and have your application touch the database in as few places as possible. Also, have fun!
5 Likes
YES!!! The biggest issue I’ve had taking my LambTracker database schema I did 9 years ago to the new AnimalTrakker one is that I failed to normalize some of the tables sinceI am now dealing with multiple species. There were things in the sheep_table (now called animal_table) that related only to sheep and not to cattle or pigs or horses. So I had to pull all that stuff out into separate tables.
2 Likes
There are also free interactive tutorials, if that suits your learning style. https://sqlbolt.com/ is my favourite. And most probably, it will be enough and no paid course is needed indeed.
1 Like