Verifying Table structures and relationships

[GaP42]

@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.

 

[troutusa]

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:

1. 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.
2. 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.
3. 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.
4. 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,
5. 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.

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.

 

[troutusa]

@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.

1. Table Removed
2. Table Structure corrected
3. Instead of having subforms 3 levels deep I was planning on a separate subform for effects. (See frmSong layout. I would sync the subforms manually using the current track selected ID.)
4. Yes, these combo boxes are just filters.
5. Correct. They were mislabeled.

Regarding my naming convention, I started doing this many years ago. It seem to make queries much easier to find mistakes. I've stuck with it ever since.

I plan on loading all of the Brands and VST's using a dedicated form. I will preload some presets using a Preset form, but eventually I want to incorporate a "Not in List" event that will either load the new data using an SQL statement, or open the Presets Form. I have code for this and have used it successfully before.

Please see the new relationship diagram and frmSong design view attachments.

 

[GaP42]

Table relationships looks good to me... although I did spot one small adjustment to make:
In tblSong you have I think a remnant TrackID field. This is not and should not be used as a FK to the Track table as the relationship is to hold the FK to Song in the Track table, not the other way around. Remove it.

Edit: Just as a note:
iIn tblPreset the field TypeID is not a FK. It will hold a value that says this is a Preset of Type "Instrument" or "Effect".
In tblEffectUsed, field effectused_ number...? is the order in which effects are applied for a track. The name might be made more explicit. The "E1", "E2", "I" - are split as there were 2 pieces of data - E, I in the aforementioned TypeID, and the order "1" for "I"s, and then 2,3,4,... for the "E"s.

Form design and its support of workflow is generally governed by the user - and you are the user. We (I) would only really help solve particular problems with the user interface, not so much its appearance. I would note that in your form design I think you are perhaps heading to a problem with the Effect subform in the footer of the Song form. However, just being wary of the potential mistaken understanding that a SONG is a track, rather than their being a MASTER Track for a song (as Track Number 0).

The existing Effects form could be a continuous record display of the effects of the current track, and the details of a selected effect on the form be displayed in the Header or footer (like a split form), removing the need for the SongEffecs subform in the footer.

Good progress.

 

[troutusa]

Table relationships looks good to me... although I did spot one small adjustment to make:
In tblSong you have I think a remnant TrackID field. This is not and should not be used as a FK to the Track table as the relationship is to hold the FK to Song in the Track table, not the other way around. Remove it.

Edit: Just as a note:
iIn tblPreset the field TypeID is not a FK. It will hold a value that says this is a Preset of Type "Instrument" or "Effect".
In tblEffectUsed, field effectused_ number...? is the order in which effects are applied for a track. The name might be made more explicit. The "E1", "E2", "I" - are split as there were 2 pieces of data - E, I in the aforementioned TypeID, and the order "1" for "I"s, and then 2,3,4,... for the "E"s.

Form design and its support of workflow is generally governed by the user - and you are the user. We (I) would only really help solve particular problems with the user interface, not so much its appearance. I would note that in your form design I think you are perhaps heading to a problem with the Effect subform in the footer of the Song form. However, just being wary of the potential mistaken understanding that a SONG is a track, rather than their being a MASTER Track for a song (as Track Number 0).

The existing Effects form could be a continuous record display of the effects of the current track, and the details of a selected effect on the form be displayed in the Header or footer (like a split form), removing the need for the SongEffecs subform in the footer.

Good progress.

I see the mistake in tblSong.

I do not understand the TypeID FK issue in the tblPReset. The value for this field comes from the tblType list of values. Please explain further why my assumption is incorrect. (I'm not suggesting I am correct.)

Regarding Effect number, the order in which the effects are applied is important. The track numbers are really only a convenience. The order could be stored in any fashion. It is convenient to keep certain tracks together, so there is some significance to the track number. The effect number is crucial. Adding distortion to a delayed signal can create quite different results than delaying a distorted signal.

The Song Effects subform in the footer was not thought through. I wanted to prototype the form to help describe my goals.

I am having trouble with the track subform. I am considering adopting your design of a song being a track 0.

 

[GaP42]

I do not understand the TypeID FK issue in the tblPReset. The value for this field comes from the tblType list of values. Please explain further why my assumption is incorrect. (I'm not suggesting I am correct.)

The TypeID field appears in both tblPreset and tblTrack. Your relationships show a join only from tblTrack to tblType via the TypeID field. That is correct - it allows a track to be designated as a "Master Track" (song) or a "Component Track". However the TypeId in tblPreset is not the same. It is used to indicate that the Preset is of type "Instrument", "Effect" or "SoundClip". Thus TypeID in tblPreset cannot be a foreign key to the table tblType (unless you add a distinguishing attribute for the type of type - Track or Preset). You may want to set up a tblPresetType for these values (to be used as the record source for a combo to select the type of Preset or use a fixed list of values as the combo record source.

Regarding Effect number, the order in which the effects are applied is important. The track numbers are really only a convenience. The order could be stored in any fashion. It is convenient to keep certain tracks together, so there is some significance to the track number. The effect number is crucial. Adding distortion to a delayed signal can create quite different results than delaying a distorted signal.

Agree - the order for effects is important - so really the comment was only that the field effect number might be better named effect order.. it was a very minor observation, in that respect. What was important was that on your paper form you had values like I (Instrument), E1, E2 . E3 (Effects) for each track. As described above, I and E describe the type of effect, whereas the number was about order (for type I there is an implied order of "0" - meaning the first). Applying the rules for normalisation in design of databases, this composite item was split (atomic values). Further it was noted that E and I applied to (depended upon) the Preset, whereas the Order depends upon the EffectUsed, so they were allocated to those specific tables (another step in the normalisation process).

Re Track 0: whether it is designated as Track 0 or as the "Master Track" , a tblSong record will be composed of Tracks of which one must be designated as such. As you have an order to the tracks on your paper form a number was adopted. [one after thought: if it that field is numeric integer, rather than text, then the "0" becomes the value when not specified (default): that may cause issues (accidentally setting two tracks for the one song as the MasterTrack). You might want to set the rule to be the Master Track is always Order = 1, and any sub-tracks have a unique Order > 1.

BTW: re this suggestion, if adopted:

The existing Effects form could be a continuous record display of the effects of the current track, and the details of a selected effect on the form be displayed in the Header or footer (like a split form), removing the need for the SongEffects subform in the footer.

I generally make the continuous display read-only (each item is Enabled and Locked = yes) so edits are only carried out where the details are shown.

I also heard that Cubase has a plugin that allows export of content to xml. That may be of use for population of much of the data through import processes.

Hope that helps clarify.

 

[troutusa]

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:

1. 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.
2. 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.
3. 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.
4. 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,
5. 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.

I have started from scratch again and taken your earlier advice. I have pared the tables down to the essential ones and left off issues like composer and performer. (You were right about the composer. I had the relationship to the wrong table.)
Attached is an Excel File mockup.

 

[GaP42]

have started from scratch again

Surprised you are going that far.. really at post #43 it was settled - with the minor tweaks - as advised. Anyway that is for you to decide. Noted that in your latest PDF/Excel mock up you no longer worry about the Song-specific data (no tblSong) and have now re-instated the self-referential FK to link them together.
Re TrackType - your original examples had tracks such as Master Track and Other Tracks which were just columns following the master track column. Any of these could then have effects applied to them. In the PDF you have TrackTypes that allow: Song, Effect, Instrument, Soundclip, Controller
I respectfully suggest that this does not appear correct. Apply the keep it simple principle - Track Types are either Mastertrack(Song track) or OtherTrack. Effects (Presets) are applied to tracks and are not the type of track. You appear to have that available in the Preset (type)

 

[troutusa]

Surprised you are going that far.. really at post #43 it was settled - with the minor tweaks - as advised. Anyway that is for you to decide. Noted that in your latest PDF/Excel mock up you no longer worry about the Song-specific data (no tblSong) and have now re-instated the self-referential FK to link them together.
Re TrackType - your original examples had tracks such as Master Track and Other Tracks which were just columns following the master track column. Any of these could then have effects applied to them. In the PDF you have TrackTypes that allow: Song, Effect, Instrument, Soundclip, Controller
I respectfully suggest that this does not appear correct. Apply the keep it simple principle - Track Types are either Mastertrack(Song track) or OtherTrack. Effects (Presets) are applied to tracks and are not the type of track. You appear to have that available in the Preset (type)

I was unable to get the forms to work properly. The circular join between track type and VST type I suspected were the problem. So I tried to adopt your recommendations. (I left some details off because I need to get the core functioning first. I've created 2 table "Preset Type" and "Track Type". This eliminates the circular reference. I believe it is also true that the types have 2 different meanings and should be separate.
I see what you are saying. Track types are either master or song tracks. Only the VST differentiates what the track is doing. Good catch. I will fix this.
I have been working on this for a long time. I really want to get to the next stage. Your help is greatly appreciated.
Thank you.
p.s. In the join tables, I have opted to use a composite key instead of an autonumber. Is there a practical reason you add an autonumber to join tables. (Everything I read uses composite keys.) I would like to know your opinion.

 

[troutusa]

I've constructed the database table and am beginning work on the main form "frmSong". I had to make changes in the relationships to get the form to work. I had to remove the referential integrity between some tables to be able to add records. I do not understand why this is the case.

At this point I am only able to add one track to a song. Access is telling me any additional record would create a duplicate in the table. I can find no reason for this to be the case.

I've been building the form one step at a time and testing along the way. It is not finished, but I cannot get past this error.

 

[mike60smart]

I've constructed the database table and am beginning work on the main form "frmSong". I had to make changes in the relationships to get the form to work. I had to remove the referential integrity between some tables to be able to add records. I do not understand why this is the case.

At this point I am only able to add one track to a song. Access is telling me any additional record would create a duplicate in the table. I can find no reason for this to be the case.

I've been building the form one step at a time and testing along the way. It is not finished, but I cannot get past this error.

Hi
Find attached your database with a few revisions

I added Unique PK's to all of your Junction tables

The Form that opens allows you to select an Album / Add a New Albums and Add Tracks for each Album.

Is this nearer to what you need?

 

[troutusa]

Hi
Find attached your database with a few revisions

I added Unique PK's to all of your Junction tables

The Form that opens allows you to select an Album / Add a New Albums and Add Tracks for each Album.

Is this nearer to what you need?

Do you add unique keys to junction tables for a reason? (I know I do things just because of my experience, and I'm wondering what experience may have impacted this habit.)

Did you try to add any tracks? I do not understand what Access is calling a duplicate anywhere.

I'm also stumped by the fact I had to remove Referential Integrity from some of the relationships just to be able to add songs. The first song can't possibly have a related record in any other table, nor the first track.

I will look at what you've done. I really appreciate your input. I built a few applications 20 years ago and have forgotten most of what I learned. Use it or lose it.

 

[troutusa]

Hi
Find attached your database with a few revisions

I added Unique PK's to all of your Junction tables

The Form that opens allows you to select an Album / Add a New Albums and Add Tracks for each Album.

Is this nearer to what you need?

Thank you for doing that. I had not yet started on the album part. I'm stuck in the tracks part. I was able to build a sfrmTracks and load the tracks ok but when I went to add the Preset information, I have not found a way to do it.
The attached uses a method I copied from a sample database. It has to do with combo boxes in continuous forms. It uses text boxes to cover the combobox display giving the appearance that the combo box holds the value you want to display. I have not completely implemented the method because I can't add/edit track details properly yet.
Attached is what I have so far. It does not work, but you can see what I am aiming for.
I just want the preset_ID stored with the track, but I need to get to the Brand and VST tables to help filter the presets. Linking this all together creates problem/s that I don't know how to solve yet.

 

[mike60smart]

Thank you for doing that. I had not yet started on the album part. I'm stuck in the tracks part. I was able to build a sfrmTracks and load the tracks ok but when I went to add the Preset information, I have not found a way to do it.
The attached uses a method I copied from a sample database. It has to do with combo boxes in continuous forms. It uses text boxes to cover the combobox display giving the appearance that the combo box holds the value you want to display. I have not completely implemented the method because I can't add/edit track details properly yet.
Attached is what I have so far. It does not work, but you can see what I am aiming for.
I just want the preset_ID stored with the track, but I need to get to the Brand and VST tables to help filter the presets. Linking this all together creates problem/s that I don't know how to solve yet.

Hi
I am not following your requirement.

I always thought that an Album has many Tracks.

Your frmSong is based on the Tracks table with a Subform based on the same Tracks Table???

 

[troutusa]

Hi
I am not following your requirement.

I always thought that an Album has many Tracks.

Your frmSong is based on the Tracks table with a Subform based on the same Tracks Table???

The confusion is because in the outside world, "tracks" means songs on an album. But in the recording studio "tracks" are individual instrument recordings that get merged together to make a song. There is a guitar track, a drum track, a vocals track, etc.
However, a song can be considered a Master Track, which is a collection of all individual tracks.

 

[troutusa]

This is to give you an idea of what a song project looks like. This is only the one screen. On my second screen are all of the VST's with presets. Screen one shows the VST being used but it does not tell you what preset is has been selected. The rack on the right are the VST's being used, but again they do not tell you the preset.

The top half of the screen is the instruments and their midi events. The bottom half is like a mixer board, where you can control volume and select effect sends. The effects are not numbered but their position in the stack is the order the effects are applied and must be kept in that order.

 

[mike60smart]

Hi
Can you put together the data for 1 Track.

1. Track Name with Track Number and Active Track details
2. List all associated Effects
3. List ass associated VST's
4. Any other related data

 

[troutusa]

Hi
Can you put together the data for 1 Track.

1. Track Name with Track Number and Active Track details
2. List all associated Effects
3. List ass associated VST's
4. Any other related data

I have not been able to link the Presets with the Tracks. I have a number of presets, VST's and Brands loaded into my DB project. I also have songs and tracks. I just can't link the tracks and presets together. I have posted a sample of the DB so far. See SongDB v82.zip attachment in previous comment.

 

[mike60smart]

I have not been able to link the Presets with the Tracks. I have a number of presets, VST's and Brands loaded into my DB project. I also have songs and tracks. I just can't link the tracks and presets together. I have posted a sample of the DB so far. See SongDB v82.zip attachment in previous comment.

The only way we are going to be able to understand your requirement is if you can give a full detailed example of a Track.

Not a picture but an actual Example of 1 Track and all associated details.

Track name xxx has the following:-
1. List the Effects for the Track
2. List the VST's for the Track
3. List anything else associated with the Track.

 

[GaP42]

I have attached a basic mock up of your database application - based on the db design at post #43
No frills, but the form frmSong allows, tracks to be created for a song (as a subform), and then the tracks to be assigned various effects. 'use the butotn on the subfom for a track or double-click on the name of the track, to open up the EffectUsed form - showing effects assigned to the track, and allowing new associations for Tracks to Presets (selected from the combo box in the footer). You can polish this up as needed.

I have used the filter when opening the EffectUsed form to show only effects assigned to the Track. I also sent the value of the Track Record ID to the form using open args so that when a new record is created in EffectUsed it provides the Track ID. This together with the value from the list of Presets (combobox) and a Sequence no (in the form footer) is sufficient to create the EffectUsed record.

Creating records for these junction tables can follow this pattern. Open a form filtered to show/supplly one side of the junction. Get the value for the other side of the junction from the combo based on its source table. Populate any other fields as needed in the junction. Think of it as making the bridge between the two source tables.

Apply this process to Songs and Composers and Songs and Performers. You will add these tables and assocuited junction tales as per post #43.

There is still work to be done in ensuring no duplicates, and other validations (tables/index deign and in the form before update event.

Apologies I am away at the moment and won't be back for a week. Just a rush mock up given the discussion issues above.

Re use of primary kays as composite keys cf using a dedicate unique primary kay:
To me there is not really any difference, given the composite is properly set up. Perhaps one difference in very large databases might be some saving in space. With the use of the composite key perhaps there is the need to be mindful of the need to supply at least 2 values when needing to specify the record - in a query, as opposed to supplying the single value to retrieve/ select a record. I do not think it is significant. Both the composite key components and the single value primary key must inherently carry no implied meaning other than as a record identifier.

Perhaps others here have different views?