Thank you so much for your help. I need to get up to speed on self-referential relationships before I understand it fully.
Verifying Table structures and relationships (1 Viewer)
- Thread starter troutusa
- Start date
By the way, what modeling software are you suing to create the above diagram?
Here is my current form of documentation. It may shed some light on how I use the information and how it is related.
Each column is a track, except for column 1 which represents the combined output of all the other tracks. (FYI, there are approximately 15 tracks for this song. 4 more pages of what you see here.)
Each column is a track, except for column 1 which represents the combined output of all the other tracks. (FYI, there are approximately 15 tracks for this song. 4 more pages of what you see here.)
The answer to your first question is yes.
For the second question, the VST may be used multiple times on one track. For each instance, the settings would be saved as a preset with an appropriate PresetType assigned to each preset.
I am not sure how the self-referential method would work here. In what I've read, you have to create a second instance of the same table and link the FK in the primary table to the PK in the duplicate table. What would be the FK in the Primary Table?
Yes, to be clear: physically there is one instance of the table. It contains a foreign key reference to itself: ie to its PK. In the relationship window you add the same table twice defining the relationship between those two.
I understand that it is the same table added twice. I don't understand what the FK would be in the First instance of the table. Would it be Null for all other Track records?
The FK ("ParentTrackFK") for a track is optional - ie null is allowed. For each track the ParentTrackFK will point to the parent of that track. Your song tracks, Track 0, have no parent so for those records ParentTrackFK will be null. For other tracks, that comprise a song, the ParentTrackFK will be the song's PK and so to point to it. A query can then list all Songs (Track 0) and the tracks of which they are composed - by finding instances where the ParentTrackFK = song PK.
As track records may exist which have not yet been incorporated in a song, it would be best to have some marker that a track is a song (ie Track number = 0) rather than rely on the null in the ParentTrackFK to determine if the track is a song.
NOTE: the self-referential relationship can support multiple levels in a hierarchy if required - by pointing to parents that are not at the top of the tree (a song) - however I have not picked that up from this discussion. You appear to need only the song as level 1 and the tracks of which it is composed - level 2, the next level down and no further.
As track records may exist which have not yet been incorporated in a song, it would be best to have some marker that a track is a song (ie Track number = 0) rather than rely on the null in the ParentTrackFK to determine if the track is a song.
NOTE: the self-referential relationship can support multiple levels in a hierarchy if required - by pointing to parents that are not at the top of the tree (a song) - however I have not picked that up from this discussion. You appear to need only the song as level 1 and the tracks of which it is composed - level 2, the next level down and no further.
I'm still digesting this information, but at some point, the songs will have a parent Album. Will Self-referencing allow a song to appear on more than one album?
mike60smart
Registered User.
- Local time
- Today, 23:55
- Joined
- Aug 6, 2017
- Messages
- 2,249
Hi
Could you put this data into an Excel sheet and upload?
Albums are a new requirement. Albums and Songs have a M:M: relationship. That is not supported by the Self-referential join.
Introduce an ALBUM table, and a SONG-ALBUM junction table to resolve this between TRACK and ALBUM
See post 26 - i don't think it is in that form.
The forum will not allow me to post an excel file or a link to an outside file. I can send it to your email if you like. Mine is troutusa@duck.com.
See attached spreadsheet for manual mock-up interpretation of your doc in proposed db structure (not all columns, not all tables). Apologies for any mis-interpretation of labels used.
EDIT: Detected some mistakes/glitches - use the second one
Last edited:
I tried to make some changes. Too many fields that just relate to a song would be left blank in the tracks table where the song was just another track. There doesn't need to be a media table. The 4 files that get linked are always the same 4 files. They are the saved output of the song project in different forms. (pdf, mp3, wav, and MIDI.
I've prototyped the input form to help see what I mean. The idea with the form is that whatever track has the focus, the effects subform would display a continuous form with all the effects used on that track.
I really appreciate your effort and input.
Attachments
A rendition of a record representing a song and associated tracks is attached as provided by @troutusa and uploaded with permission on behalf of troutusa. It is important to note that this is a special rendition as the records are in paper form as shown in post #26.
Commenting upon post #39 Relationships:
Commenting upon post #39 Relationships:
- As you have now decided to keep Songs and Tracks in separate tables, there is no need for the self-referential relationship that was introduced earlier. Therefore remove the table tblTrack_1, and make the join directly between tblSong and tblTrack (1:M). This also means the Track table should no longer needs the tbltrack_Parent field.
- It also has the issue that Songs have Effects applied to them, not just the tracks of which they are made. This can be addressed through a construct in which TrackType includes a "Song" type, which allows this "SongTrack" to be assigned one or more effects. It is only used for that purpose. The Song is composed of the SongTrack and the other Tracks in the Track table that comprise it. The Track_name for the SongTrack may or may not be the same as the SongTitle. This then would also have an impact on your data entry form. Further commentary to come.
- Re naming of fields: the name of the table is being used as a prefix to every filed in each table. I would suggest this is unnecessary. Just ensure the names are meaningful within the context of each table, and ensure you avoid using reserved words. EDIT: Retain SongID, TrackID etc as the generic ID name for the PK can then lead to needing to track down which one is being referred to.
- The decision to limit to the four out types is yours to make, however should another format arise that you want to incorporate, then you will need to change the song table and re-build queries, forms, reports to accommodate the change, whereas having a media type table would allow you to add the new type and minimise any rebuild, if any,
- Composer, in your diagram, has a relationship to the table AlbumSong. I would have thought that the Composer is related to the Song directly and does not depend upon the Album as well as the Song. Further, as songs may arise through collaborations between two or more composers, that a SongComposer junction table similar to the SongPerformer table, would be used to resolve the M:M: relationship.
Last edited:
@troutusa: re the data entry mock up form:
There is a dependency upon adoption of the data structures/ relationships:
1. Assuming the removal of the tblTrack_1 table and the Parent attribute then obviously you will have no need for that on the form.
2. Song Details will need a mechanism to create the Performer and Composer association.
3. While Tracks is a subform of Songs, Effects should be a subform of Tracks, not of Songs. You may need to consider a separate form that can be initiated from this one, depending on screen real estate, that enables this information to be maintained.
4. Brand ID and VST ID in Tracks is not relevant - they apply to Presets. In Effects, the Brand depends upon the VST selected, so the Brand combo has no relevance - except to set it up as a filter to the values presented in the VST combo (cboBrand is unbound)
5. BTW the compo boxed will/should display the VST / Brand ... etc name, not the ID although they will be used to capture the ID. So perhaps the labels are not correct.
Also, just consider the way in which you expect to work with the application. Are you going to use it only after you have defined the song and all the associated Tracks and effects, and tweaking these records as needed or will you want to build a library of the VSTs, and the Presets, which you can link to as needed when building your song record. You indicated earlier that you have, potentially thousands of these? That implies a plain-vanilla combo box may not be entirely suitable. You may need to think about how best to find (asyoutype) the record you want. There are some solutions to that type of problem that can be found in this forum and elsewhere.
Let's start there...
Thanks for the thanks.
Good luck with the project.
There is a dependency upon adoption of the data structures/ relationships:
1. Assuming the removal of the tblTrack_1 table and the Parent attribute then obviously you will have no need for that on the form.
2. Song Details will need a mechanism to create the Performer and Composer association.
3. While Tracks is a subform of Songs, Effects should be a subform of Tracks, not of Songs. You may need to consider a separate form that can be initiated from this one, depending on screen real estate, that enables this information to be maintained.
4. Brand ID and VST ID in Tracks is not relevant - they apply to Presets. In Effects, the Brand depends upon the VST selected, so the Brand combo has no relevance - except to set it up as a filter to the values presented in the VST combo (cboBrand is unbound)
5. BTW the compo boxed will/should display the VST / Brand ... etc name, not the ID although they will be used to capture the ID. So perhaps the labels are not correct.
Also, just consider the way in which you expect to work with the application. Are you going to use it only after you have defined the song and all the associated Tracks and effects, and tweaking these records as needed or will you want to build a library of the VSTs, and the Presets, which you can link to as needed when building your song record. You indicated earlier that you have, potentially thousands of these? That implies a plain-vanilla combo box may not be entirely suitable. You may need to think about how best to find (asyoutype) the record you want. There are some solutions to that type of problem that can be found in this forum and elsewhere.
Let's start there...
Thanks for the thanks.
Good luck with the project.
Thank you for your input. I am very grateful. With confidence in my table structures, I can work on my forms and not be worried that the table structure is the problem when something doesn't work as expected.
Similar threads
