join table question - multiple users?

[human]

I have a hypothetical (but might become practical) question: I have a database of music tracks. Users can drag on tracks and they get stored on a cloud server (AWS S3) and FM stores a reference to that. FM can play the tracks  (via javascript "wavesurfer") and see all kinds of data about the tracks. When users log into the solution they are recorded in a users table, which stores some of their preferences, as well as a list of tracks they've marked, so that can be restored when they next login. Now suppose I want to allow them to create multiple playlists. Normally I would create a playlist table and a join table with a list of playlist ID's and trackID's, and that would sit between the main tracks database and the playlists table. That's all pretty simple. However, suppose I want individual users to each have their own playlists? I simply cannot picture how that could work.

[comment]

36 minutes ago, human said:

suppose I want individual users to each have their own playlists? I simply cannot picture how that could work.

Not sure where you are having a problem with this - or perhaps I am missing something in your description. The "proper" structure would be:

If you wanted, you could use a checkbox field in the Playlists table to select the tracks, and eliminate the Entries join table.  But then you would not be able to add any properties to a join, or produce a report of multiple playlists. (If your playlists have an order of tracks, then this would not be a good option for you.)

 

 

Edited November 7, 2022 by comment

[human]

The relationship between tracks and playlists is many-to many, so requires a join table, correct? If I have multiple playlists belonging to different users, then that's another join table between users and playlists. So I can't see how those two join tables work together. In your diagram, what would the schema for "entries" look like? Maybe playlistID, userID, trackID?

[comment]

I am afraid I don't follow. Earlier you said you wanted users "to each have their own playlists". If each user has his/hers own playlists, then each playlist belongs to one user only - so that would be a one-to-many relationship, with no need for a join table.

If you wanted users to share playlists, then you would indeed need another join table between the two:

 

Edited November 9, 2022 by comment

[human]

I'm still having trouble designing it. Each user has his own playlists, but initially the list will be empty. The user can create a playlist by marking tracks with a checkbox and then giving that collection a playlist name, and can do that as many times as she wants. They are not shared. So I am thinking something like this?

[comment]

33 minutes ago, human said:

Each user has his own playlists

Then the structure would be as per my first post.

34 minutes ago, human said:

The user can create a playlist by marking tracks with a checkbox and then giving that collection a playlist name

This is a matter of building a user interface, not structure. Technically, it is not possible to populate a playlist with tracks before a playlist record is created. So you would need to provide the user with a "sandbox" where they can populate a draft playlist, then give them an option to "save this playlist as …". The draft playlist could be simply a global text field in the Users table (again, assuming the order of tracks does not matter). Then you would need a script to take the selected tracks, create a new playlist record related to the current user and add the selected tracks to it.

 

[human]

I got it working pretty much as in your first diagram. So I have a join table TrackID-PlayListID (what you are calling "Entries" I think), and a script to add marked tracks and the current PlaylistID to that join table. It finds all marked tracks, then loops through them and adds them. But how do I prevent duplicates from being created? I'm thinking that for each pair I have to do a find and see if it succeeds or fails?

I haven't tried allowing the records to be created via the relationship, but - if it works the way I have everything connected - would that prevent duplicate entries?

Thanks

[comment]

2 hours ago, human said:

how do I prevent duplicates from being created?

There are two aspects to this:

If you want to be absolutely sure that a duplicate entry cannot exist, under any circumstances, then add a text field to the join table, make it auto-enter a calculated value of:

PlaylistID & "|" & TrackID

and validate it as Unique,Validate always. That's at the data level.

However, Filemaker's validation error message is not exactly user-friendly - so you want to add measures at the UI level to prevent it from kicking in during normal operations. IOW, do not give the user an opportunity to create a duplicate in the first place. I am not sure I understand fully the workflow you have created (esp. why you need to find the marked tracks). If you're selecting the tracks using a checkbox field, then there can be no duplicates. Likewise if you're looping over a found set in the Tracks table (which, as I said, should not be required, but still). So you should be fine on that front.

 

2 hours ago, human said:

I haven't tried allowing the records to be created via the relationship, but - if it works the way I have everything connected - would that prevent duplicate entries?

If you construct a dedicated relationship for creating new records in the join table, using both PlaylistID and TrackID as the match fields, then it is not possible to create a duplicate entry through this relationship.  But IMHO, such construction would be an unnecessary addition to the file's schema.

 

Edited November 10, 2022 by comment

[human]

29 minutes ago, comment said:

Quote

I am not sure I understand fully the workflow you have created (esp. why you need to find the marked tracks). If you're selecting the tracks using a checkbox field, then there can be no duplicates. Likewise if you're looping over a found set in the Tracks table (which, as I said, should not be required, but still). So you should be fine on that front.

Initially when the user creates a playlist from checkbox marked track, there can be no duplicates. However she has the ability at any time to add additional marked tracks, or remove some or all checkmarks - the set of checkbox'd tracks is a list of trackID's in a user field, and is saved on exit and restored on entry. The checkbox system is essentially separate from the playlist system. I have a button "edit playlist" that will use a playlist selected from a pop-down menu, with the ability to also create a new one (and checking for duplicate playlist names as well). When that button is clicked it will try to add all the marked tracks to the currently selected playlist via the join table. So it could create duplicate entries, however now I'm trapping for that by doing "go to related record" and if it gives an error then there are no duplicate entries, otherwise there are, and I skip them. I haven't tackled removing entries yet, not sure how I'm going to do that.

29 minutes ago, comment said:

If you construct a dedicated relationship for creating new records in the join table, using both PlaylistID and TrackID as the match fields, then it is not possible to create a duplicate entry through this relationship.  But IMHO, such construction would be an unnecessary addition to the file's schema.

I couldn't figure out how to create a record in the join table via a relationship. Clearly I'm just doing it wrong. So I'm just creating a new record for each trackID/playlistID pair and that works fine.

Thanks for your help.

[comment]

1 hour ago, human said:

she has the ability at any time to add additional marked tracks, or remove some or all checkmarks

If I were a user used to creating a playlist via a checkbox field, I would expect to edit it via the same interface - and I would expect clicking the "edit playlist" button to load the checkbox field with the current selections, which I could then modify while seeing the entire picture. Then I would have no opportunity to create duplicates during editing either.

Added:
In case it wasn't clear: at the end of editing, all existing entries would be deleted and new records would be created for all selections in the edited field. This is not the only way to handle editing, but it is probably the simplest.

 

 

Edited November 10, 2022 by comment

[human]

Yes that would work but is kind of clunky. If they select an existing playlist to edit, I'd have to clear all their marks, and delete all the entries from the playlist, then generate it again from a new set of marks. In my scenario the marked items are a separate sort of playlist all on its own. New marks will get added to the playlist, marked items that are in the playlist already don't get added again. But how to delete items that are in the playlist, but not marked, is a challenge.

What would be better would be an interface where they could just drag tracks into and out of a playlist portal. I might be able to do that with a plugin, not sure.

Thanks again.

[comment]

8 hours ago, human said:

that would work but is kind of clunky.

My assessment of the two alternatives is exactly opposite of yours. Deleting all related records and creating a brand new set is a rather trivial task; creating records only for new values in the edited list is more difficult - and deleting existing records that are not in the edited list even more so. The only potential downside to my method I can see is losing the audit trail of when each record was created.

 

8 hours ago, human said:

an interface where they could just drag tracks into and out of a playlist portal.

I don't know about that - but you could show a portal to all tracks and let them switch individual tracks on/off. The difference between this and a checkbox field is that you could run a script that immediately creates or deletes the corresponding record in the join table.

I should also point out again that if your playlists do not have an order - or any other field describing an individual entry - then the join table is probably an overkill.

 

[human]

On 11/11/2022 at 1:28 AM, comment said:

I should also point out again that if your playlists do not have an order - or any other field describing an individual entry - then the join table is probably an overkill.

A playlist can have many songs, and a song can be assigned to many playlists, so that's a many-to-many, correct? So I would think that it requires a join table. Is there a simpler way?

On an unrelated question - as I said I'm playing the track with javascript in a webviewer. I got the idea, and how to do it, from Jeremy Brown's video linked below.  At first I had the webviewer on the main layout, but this was a problem, because every time the user makes a record change (eg clicking a checkbox, etc) it refreshes the webviewer. That slows it down quite a bit, because the javascript in the webviewer is reloading the base64 data again and again, even though its the same track. The only time it should really reload it is when the user clicks play on a different track's play button.

So then I changed it to have the web viewer in a separate floating document window. Now it does not refresh unless the user clicks play, and the web viewer refreshes itself - I don't even have to use "set webviewer reload". However, there is other data on the layout (not in the web viewer), and that does not refresh. I can't figure out how to make it refresh unless I close the floating window and reopen it. I can do that, but then I need to save its' position on screen, and its size, and restore that as well. I can do that, but I thought there must be a simpler way?

 

 

[comment]

34 minutes ago, human said:

A playlist can have many songs, and a song can be assigned to many playlists, so that's a many-to-many, correct? So I would think that it requires a join table.

Filemaker allows you to implement a many-to-many relationship without a join table, using a multikey field (such as a checkbox field). It's not a full-featured implementation and has the limitations I already mentioned above. But it seems like it could be a welcome simplification in this case.

I'm afraid I did not understand your "unrelated question" - and being unrelated, I would suggest you post it separately.