I’ve worked with databases on and off for a long time but in general I’ve been accessing previously defined data tables or had very simple needs (DynamoDB kind of forces that).

I’m at the point in my sandbox work that I’m about to define a substantially more complex schema than I’ve used for anything in the past. Finding that my command of SQL DDL and the various bits involved in creating MySQL users and setting permissions is taking some doing.

I still can’t seem to get a database on a remote system (boojum, my little test machine). I am not getting a ‘no connection’ response but an authorization failure so I expect that the identit(ies) I created on the NUC aren’t quite right Always details to be dealt with…

I am finding that tables proliferate. Everything needs a unique ID (and auto increment columns help enormously here). Any sort of one to many relationship (keywords, validation) winds up with a new table containing the source object key and the targets. In the past I’ve used the DB as an index for more complex storage (DICOM-3 pretty much forces this with its many 1..n fields, DynamoDB encourages this as well with bulk information in S3).

In the current case I really want to keep everything in the DB rather than spilling items overboard. If I get to thumbnails I’ll see how the BLOB types work. For now I expect that I’ll be ok with the 64K per row limit on MySQL InnoDB tables. I expect that BLOB and TEXT aren’t stored in their respective tables so they should permit ‘stretch’ operations.

Current working notes are a bit chaotic 🙂

I am sticking with the MariaDB fork of MySQL to avoid Oracle entanglements. Once the basic database definitions are in place I expect to code the front end in C# as I need to polish up that language and environment a bit at the moment. Not sure whether I’ll go with WPF or some sort of web interface for the UI yet. Got to get the innards a bit better defined first.

Looks like I was just messing up my command line for remote mysql access. Once I got things straightened out, I can talk to the database instance on boojum from chaos. Should make things a bit cleaner as I can keep the data in one spot and access it from any system in the house. Now I just have to finish with the create table statements and see if they build what I need. Short digression into users and permissions first (and probably a quick look at alter table) to see what I should be doing for remote access.