Data model advice asked for

#1

I have a Filemaker app that tracks artworks, exhibitions and consignments. I can not afford the subscription to upgrade flemaker and the laptop on which I run this database has a blown battery and may get trashed (it is a 2015 model Macbook Pro). The database tracks about 1500 artworks at present, growing about 100 works per year.

I am thinking about porting the app to TW but have some conflicting ideas as to how to implement the data model.

The FM database has a pretty normal data model, tables for each of the entities with mapping tables to implement many-to-many links between records in the tables (eg. many artworks can link to many consignments, etc).

Now, TW can implement such relationships a number of ways and I am seeking advice and guidance from the experts on this forum before I commit to one way or another. I will not spell out what I’ve been thinking so as not to bias your thoughts.

My reasoning for using TW is that it just runs on just about everything irrespective of OS versions and that is very appealing to me.

Thanks in advance for any of your thoughts.

bobj

#2

I’m definitely not an expert, but I do have thoughts about this, which I’ve been discussing in another thread, and also discussed quite a bit in an earlier thread and partially implemented in a demo wiki.

2 Likes
#3

thank you @Scott_Sauyet , I will follow your links

#4

@Bob_Jansen
I’m am not a DBA, though I have worked on database design teams. A relational database has many various implementations beyond TW capabilities.

Simple database designs (each of us will have meaning of this) can work in TW. The challenges I have worked with are:

Primary Keys: this can be the tiddler titles, but does not need to be. We could create an ID field. I have not tried this.

Foreign keys: I have used custom title list fields referencing tiddler titles for this. Again, would an ID field be better here?

Intersections tables (I suspect you referred to this as mapping tables): The obvious is to create a tiddler for it. Eg. I have a person (table/tiddler type) and an event (table/tiddler). My intersection is an event-role (table) which has foreign keys to both person and event, plus another foreign key to a role (it enables 1 or more persons to be assigned 1 or more roles at an event). In the end I have a person tiddler, an event tiddler, a role tiddler, and an event-role tiddler (the intersection). Any other approaches?

Cascading deletes: databases are great at manging this for us. In regards to renaming tiddlers relink plugin provides solutions when title lists are referencing tiddler titles. If IDs are used that issue is a non issue. In my example above deletion of a role or person or event needs a cascade delete to remove the event-role. Is there a plugin, like relink, but for deletion? ie a upon the removal of tiddler automatically removes tiddler that has a title list field that references the removed tiddler. Optional, we may just want the removal from the title list field (depending on the tiddler type or there are other tiddlers listed in the title list; eg. I delete a person, the event-role remains because there are others with this role at the event, otherwise the event-role is removed.

What other relational database concepts are concerning?

#5

In my work, I’ve stuck with the titles for these, and used caption for display version. That’s clearly the smoothest path with TW. But arbitrary fields are absolutely possible.

No. That’s exactly what I do, and I haven’t had any issues with it. I often have questions about whether such tiddlers belong in the main namespace or somewhere inside the system ($:/) namespace, but that’s about my only issue here. If there’s a small list (anything up to a few hundred) for any of my main entities, I will likely add a FieldEditorFilter with a dropdown to select that entity when creating such an intersection tiddlers. To my mind, the intersection needs its own dedicated tag, and naming it can get surprisingly tricky.

On the other hand, I’ve had no luck at all with this. And nothing gives me hope that anything is coming along soon to help. If we can limit deletion to only happening from a dedicated button, we have some ability to ensure data integrity. But that’s hard to do with all the different ways users could delete the tiddler for an important entity.

3 Likes
#6

Well, is this a start? I put this JS in a tiddler with Type: application/javascript and module-type: startup

Sorry, its a bit simple. This simply logs to the console when it detects the deletion of a tiddler, then reviews all the RELINK configuration settings (yes, I assumed you have RELINK plugin installed) looking for all TITLE and LIST configurations. When found, it logs to the console to remove the reference from the list or title field. This console log code needs to be replaced with code to update the list/title field. Do you know how to do this?

Then what would we do with the other RELINK fields? What about wikitext?

$tw.hooks.addHook("th-deleting-tiddler", function(tiddlerToDelete) {
  console.log("MK detects deletion of " + tiddlerToDelete.fields.title);
  const tiddlers = $tw.wiki.filterTiddlers("[prefix[$:/config/flibbles/relink/fields/]]", null, '');
  if (tiddlers.length > 0) {
	tiddlers.forEach((tiddler) => {
		if (tiddler !== undefined) {
			var fieldtiddler = $tw.wiki.getTiddler(tiddler);
			if (fieldtiddler !== undefined) {
				if (fieldtiddler.fields.text !== undefined) {
					if (fieldtiddler.fields.text == "list" || fieldtiddler.fields.text == "title") {
						const parts = fieldtiddler.fields.title.split("/");
						const fieldName = parts[parts.length - 1];
						const targetTiddlers = $tw.wiki.filterTiddlers("[all[tiddlers]contains:" + fieldName + "[" + tiddlerToDelete.fields.title + "]]" , null, '');
						if (targetTiddlers.length > 0) {
							targetTiddlers.forEach((targetTiddler) => {
								console.log("remove " + tiddlerToDelete.fields.title + " from field " + fieldName + " in tiddler " + targetTiddler);
							});
						}
					}
				}
			}
		}
	});
  }
  return;
});
#7

@Bob_Jansen is this data append-only? or do you also remove records/many-to-many (M2M) relationships?

do the M2M relationships (artwork-consignment) have additional data, specific to the relationship itself?

#8

That’s not just a start. That’s a fantastic start!

Maybe a year ago, I tried using addHook (for a different reason) and failed miserably. I put it aside as something to look into another day and promptly forgot about it.

I have a wiki I’ll be working on in a month or so which could use such a treatment. I look forward to trying it.

For my case, Relink is not part of the consideration. I use it extensively, but here I would probably only delete very specific tiddlers. For instance, I have Membership tiddlers, which intersect Member and Organization. I would delete all related Memberships (and similarly Candidacies) if I removed a Member or an Organization.

I don’t know about other relink fields, but I would not worry about wikitext. Most of my display is in templates of one sort or another, not in text fields, but for instance if the bio text for Member A mentioned that

... she has been married to [[Member B]] for eleven years

then I would not want to change that if I deleted the tiddler for Member B. Sure, now it’s a link to a non-existent tiddler, but that’s better than trying to automate this process. Of course if your scenario is very different, you might have to figure this out.

1 Like
#9

@jerojasro The activity is essentially append-only, add new work, exhibitions and consignments. I guess, consignments may have some delete activity during the initial add phase but once any of these objects are created, they may be edited but not destroyed.

As for additional data, each relationship has no additional data. Any such ‘additional’ data is a property of the object itself

#10

Thanks everyone for a very enlightening discussion so far. It seems I am not the only one working in this area.

bobj

#11

Since the relationships have no data, I’d suggest adding an artworks field to your consignment tiddlers, and listing there the related artwork tiddlers.

In the consignments, you can list the artworks easily with:

<$list filter="[enlist{!!artworks}]">
<$link/>
</$list>

In each artwork, with:

<$list filter="[<currentTiddler>listed[artworks]]">
<$link/>
</$list>
#12

Thanks. Let’s move this discussion to a new thread at:

I suspect we are highjacking this one.

I have a working piece of code, that updates Title and List values and then decides if the tiddler being updated should also be removed. This logic is generic. In relational database models, we configure cascade deletes in the database foreign keys. With that in mind, I thought then each entity must be configured to support a cascade delete. In my example I previously provided, I think if any list field in an event-role was empty (after an update) it would mean the tiddler should be removed. A role-event tiddler is not valid if the people list field is empty, or if the role list field is empty or the event list field is empty. Therefore, TW filters can be used to determine if a tiddler should be removed. E.g.

[tag[event-role]!has[people]]
[tag[event-role]!has[role]]
[tag[event-role]!has[event]]

The user creates one filter for each rule. So, I can create one or more rules for any given entity. I could do anothers for role, people and events. All the rules get executed, and if any 1 of them becomes true, the tiddler is deleted.

In my app there will be entities like event-role that can be cascade deleted, and others that cannot be by definition.

1 Like
#13

Try https://tabulator.info . The simple table method may solve the problem.

An outline view can be implemented. Batch editing is possible. Drag and drop is possible. Attributes can also be entered. The downside is that the user interface has limitations.

tw5-locator plugin. You can use two views to represent bidirectional one-to-many. One view is also possible. Similar to excel slicer. The disadvantage is that you need to use the commander plugin command line to batch modify.

#14

I am following, with great interest, the discussion on this thread. Please do not branch onto a separate thread, all your contributions only add to the knowledge communicated.

Anyway, having digested much of the discussions so far, I have come up with the following plan.

Four tiddler types:

  • Artwork, title is an unique asset number, ie. YB1234, and having various fields for artwork properties, such as artwork_title=‘Blue Poles’ (artwork_titles can not guaranteed to be unique as in ‘untitled’)
  • Exhibition, title is a unique exhibition number, ie. EX1234, and having various fields for exhibition properties such as exhibition name, gallery name, date start, date end, status, etc
  • Consignment, title is a unique consignment number, ie CO1234 and having various fields for consignment properties such as consigned_to, transported_by, date_transported, status, etc
  • Relationship, title is a unique relationship number, ie RL1234 and having three fields for artwork_title, consignment_title, exhibition_title.

The relationship tiddler can have artwork_title and one of the other two title fields completed, ie, RO1234, artwork=YB1234, exhibition=EX2345

This will allow the documentation of each artwork and where it has been exhibited or consigned to.

The discussions about cascade deletion, although relevant, are not too much of an issue here since

  1. artworks once added can not be deleted as their asset numbers are never re-used. They can have a status of ‘lost’ or ‘destroyed’. Their artwork_titles, however, may change as the artist wishes.
  2. Similarly, exhibitions are not deleted since they are usually added after the exhibition has opened and thus the exhibition is unable to be removed in real time.
  3. Consignments are created as part of moving one or more artworks. Once created, they are usually not deleted if the movement has occurred. The only deletion event can be prior to the actual movement and I can see a simple filter on the relationship tiddlers can extract all artworks for an consignment and then do a delete action on those relationship tiddlers.

There will be various viewtemplates to display required information and also an add template for adding artworks, exhibitions and relationships.

@jerojasro, your idea did occur to me but this then enshrines that a relationship can have no properties which may be a limiting factor in future. My idea is that relationship properties can be added subsequently if required.

@tomzheng, I will have a look into tabulator and see what it can do for me.

All of this is a thought bubble only. Now to begin and trial TW stuff.

If you are interested, I have a TW of artworks already online at http://cultconv.neocities.org/YvonneBoag/YBTW.html. This TW is updated via a JSON extract from the current Filemaker Pro database for the artworks list excluding exhibitions, consignments, etc.

thanks one and all and keep these good ideas coming folks!!

bobj

#15

I have been thinking further on a suitable data model and have resurrected a model I developed back in my PhD days. This was use to develop an extension to data dictionary technology to cater for knowledge based systems objects such as rules, frames, etc (t was called a Knowledge Dictionary). It was developed using Hypercard. There are papers published online for anyone interested (Dr. Bob Jansen's Publications)

The model has three entities,

 attribute >---------- entity -------------<   relationship
                              -------------<

The entity --< relationship is using a bill-of-materials structure, ie. owner_of, member_of links.

This is general purpose enough to allow the recording of the artworks system, as well as many others.

For validation, you can have a mirror structure that lists allowed entity types, allowed properties and allowed relationships, with appropriate links to the entity, attribute and relationship objects (which is what the Knowledge Dictionary has). This can be used to create popup menus of allowable values when adding content objects.

It would mean lots of filters to manipulate the stored objects but they could be re-used across implementations. I will have to check out filters within filters though cause I can see simple actions that would require such structures (similar to the discussion regarding cascade deletes).

Again, just a thought bubble but I will start with this I think.

Hopefully, if any of the TW experts see potential issues with this, they can add their comments.

bobj

#16

I’m curious as to whether you plan to start from this abstraction and try to build out your art collections model atop an abstract model, or if you were planning on building the specific model, simply keeping the generic one in mind as you go.

Obviously TW has a simple built-in Entity/Attribute model in its Tiddlers/Fields. And many of the discussions swirling around here the past few weeks are discussing various ways to handle Entity/Relationship models using Intersection tiddlers or various forms of data dictionaries. It’s also relatively easy to imagine a UI to allow us to construct any of our known Entity tiddler types based on some generic template, using lists of fields stored in a model. So I can see at least the outline of how to do this generically.

But, personally, I prefer to work from the specific to the general, refactoring as I note commonalities that can be handled generically. So for a wiki keeping track of board and commission memberships, candidacies, elections, etc., I create entry mechanisms for entities like People, Organizations, Elections, etc., and then have relationships like Membership, Candidacy. I’ve done a number of wikis with similar ideas. But I’ve never even started to ponder how I might make this more generic. So far the differences between them are great enough that I haven’t seen the benefit.

It would be interesting to see someone approach it from the other direction.

#17

@Scott_Sauyet my thoughts are to build it on top of this abstract model. I managed this very well in the Hypercard Knowledge Dictionary application. However, I’m sure there will be difficulties, hopefully none too onerous to void the work to that date.

One thing in my favour is that transaction rates will be relatively low and essentially simple, eg. new artwork, new consignment, new exhibition. Deletions will be rare, as mentioned before. Changes will usually be changes at a single object level but might, I guess, be at object collection level for some.

bobj

bobj

#18

That’s very interesting. The main problem with the linked PDFs is that they miss some figures. Especially at https://turtlelane.com.au/Papers/AAAI88.pdf Chapter 2.3 – The figure 2.3.1 is only referenced as “Insert figure 2.3.1.”

What I would have been interested in the “meta-meta” view of the data model as discussed in the 3rd paragraph with a reference to (Parle 87)

I did only skim the rest of the paper because it describes functions that are based on the missing figure.

Many of the PDFs of the early papers can not be fully read with the browsers PDF reader. Some figures are cut off. – I’ll have a closer look at them using an external PDF reader. (TODO)

#19

@pmario Sorry about that. It seems most of my PDF’s suffer from the same problem, even those uploaded to researchgate.net. I will try and get the originals from my archives and if successful, will send you a copy.

Subsequent Edit: I have found a printed copy of Data dictionary approach to the maintenance of expert systems: The Knowledge Dictionary. I will scan a copy and hopefully this will suffice for your needs. Not the same as you were reading but all I could find.

bobj

#20

I found “Cornerstone Cases in A Dictionary Approach to Rule Maintenance” on page 4 from 1991 is a fig.1 that describes a knowledge dictionary. Is this related / a later version?