Verifying Table structures and relationships

[troutusa]

I am on iteration 5 of trying to create a database where I can store details for music I write using Cubase Software and other instrument/effect programs. I beginning to believe that it is my table structure and relationships that are causing my forms to not work as expected. Below is a brief description of what I need and a relationships report showing my tables and relationships. I would appreciate any feedback on where I might be getting off on the wrong foot with my table designs.

1 Song has many Tracks(Instruments) that make up the song.
1 Song may have many Effects.
1 Track(Instrument) may have many Effects.
Instruments and Effects are "VST" programs that either produce music (instrument) or alter the instrument (effect). Some VST's can do both, most are dedicated to either making sounds or altering sounds.
An Instrument or an Effect is assigned a Preset name after the VST's settings are set. (There are thousands of presets. So, I want to be able to filter the presets by the Software Company (Brand), the Software Name (VST), and the Software Type [Effect, Instrument, Audio Clip, etc.])

There are one-many and many-many relationships here.

I don't want to get into the forms before I am confident my table structure and relationships are accurate.

I would appreciate feedback on what I have so far.

 

[GaP42]

Pre-warning - I am not across the ins and outs of music composition / tracks so ...
I note from your relationship diagram that you have 2 very similar Effect Tables - which could be one table with a type attribute to distinguish the Song and Instrument Effects - however you also mention some VST can do both.....(so maybe a third option for both)
You also have a relationship from Track to Preset - Is this valid? - or is the only real relationship from Track to Preset through Track Effect?
Your VST attributes appear to need expansion to accommodate Software name, and the Effect Category? Instrument? Will Audio Clip be a link to the sample - in VST?

 

[troutusa]

Regarding the similar effects tables. An effect can be applied to the entire song (All Tracks) or individual tracks. It occurs to me though, that the Song-Effect and Track-Effect relationships should be one-many not many-many.

The only real connection between a track and the instrument is the Preset. (The Brand, and VST, are only needed to filter down the thousands of presets when you combine all the Brands' VSTs presets together.) So the tblTrack only needs the Preset ID. I need to see the Brand and VST when I try to find a preset. It is also possible to have an Effect with the same Preset name as an Instrument. (That may sound confusing, but in the digital realm, the preset name means little. It could be "Snowball".)

The table tblType lets me know if the preset is an Instrument, and Effect or a Sound Clip.

The Preset has a FK from VST, which has FK from Brand. It is like City --> County --> State -->Country.

 

[The_Doc_Man]

Looking at the diagram, I see a major "gotcha" waiting to pounce. Imagine a query that somehow has to work between tblSong and tblTrackEffects - two tables as far apart (relationship-wise) as possible in your diagram. I'm not saying that you would ever have to do this, but if you did, you would find a difficulty caused by having two possible pathways between the two. This also goes between tblSong and tblPreset.

In both cases, you have one/many (or many/one) relationships involved. When this happens, Access - which is a combinatorial engine - will iterate on any unconstrained relationship that has multiple values in that field, leading to queries returning more records than exists in either table.

The suggestion to collapse two tables into one and add a field to discriminate between their origin would eliminate this problem.

 

[GaP42]

Regarding the similar effects tables. An effect can be applied to the entire song (All Tracks) or individual tracks. It occurs to me though, that the Song-Effect and Track-Effect relationships should be one-many not many-many.

Your relationship diagram shows them as one to many -- are you saying that despite the diagram, that you think they are many to many?
ie: For a SONG, there may be many SONGEFFECTS, and a SONGEFFECT applies to many SONGS? (similarly for Tracks)

Is there a hierarchy?
A SONG is composed of 1 or More TRACKS. A TRACK utilises 1 or More TRACKEFFECTS.
A TRACKEFFECT only applies to one TRACK
or can the same TRACKEFFECT apply to more than one TRACK? - if this latter case then a table to resolve this many to many would be introduced to resolve the M:M relationship: - possibly TRACKEFFECTEMPLOYED between TRACK and TRACKEFFECT.

Are all Effects actually applied to tracks and these tracks are accumulated/aggregated/comprise to SONGS? So while SONGS may have effects applied to them, the effects are actually only applied through the tracks of which the songs are composed?

 

[troutusa]

Your relationship diagram shows them as one to many -- are you saying that despite the diagram, that you think they are many to many?
ie: For a SONG, there may be many SONGEFFECTS, and a SONGEFFECT applies to many SONGS? (similarly for Tracks)

Is there a hierarchy?
A SONG is composed of 1 or More TRACKS. A TRACK utilises 1 or More TRACKEFFECTS.
A TRACKEFFECT only applies to one TRACK
or can the same TRACKEFFECT apply to more than one TRACK? - if this latter case then a table to resolve this many to many would be introduced to resolve the M:M relationship: - possibly TRACKEFFECTEMPLOYED between TRACK and TRACKEFFECT.

Are all Effects actually applied to tracks and these tracks are accumulated/aggregated/comprise to SONGS? So while SONGS may have effects applied to them, the effects are actually only applied through the tracks of which the songs are composed?

A song can have several Effects, but it is a 1-many relationship. Songs may share the same effect but that is only a coincidence. The effect may change for one song, but that change will not be reflected in other songs.

A song has several tracks (like guitar, drums, synth, flute, etc.). Each track may have multiple effects (like chorus, and delay, or distortion.) Originally I was thinking this was M:M but I think I was wrong. I think it is 1:M. I don't need to know all the tracks a preset is in. I only need to know which presets are in a given track.

To answer your last question, the SongEffect is any effect that is applied to all tracks in a song. It, or they, is applied to the stereo output of the entire song which is the collection of tracks after being mixed together.

 

[plog]

Here's the issues I see:

A song can have several Effects, but it is a 1-many relationship. Songs may share the same effect but that is only a coincidence.

1. tblSongs-tblSongEffect is a many-many relationship. Read the above 2 sentences, the first makes an assertion, then the second immediately contradicts it. Coincidence or not, one song can have many effects and one effect can belong to many songs. You need a junction table between tblSong and tblSongEffect to sort that out.

2. tblTrack-tblTrackEffect is a many-many relationship as well. See #1 for reasoning. You need a junction table between them as well.

3. Relationship Loops. There should only be 1 way to trace a path between tables using relationships. Between tblTrack an d tblSongEffect I can trace 3 paths. That's not right. Don't know what is, but 2 need to go.

Then I have thoughts that need more information to flesh out:

4. tblSongEffect and tblTrackEffect might be merged. I'd make changes 1 and 2 above and then work on this. Those 2 tables have a lot of same fields that lend them to merging.

5. tblSong--are _MidiFile, _MP3 and _Wav mutually exclusive? Are they Yes/No fields? If a new format is added how do you envision accomodating it? My guess is that instead of those 3 fields, you just need one _MediaType

 

[Pat Hartman]

Also, there is no Effects table. Is there a list of Effects and can an Effect apply to either a song or a tract? If you add an Effects table, you then have two junction tables. One to connect Effects to Tracks and the other to connect Effects to Tracks.

Are Presets related to Effects or to Songs and Tracts? The diagram seems to conflict and have this relationship at different level.

 

[troutusa]

Looking at the diagram, I see a major "gotcha" waiting to pounce. Imagine a query that somehow has to work between tblSong and tblTrackEffects - two tables as far apart (relationship-wise) as possible in your diagram. I'm not saying that you would every have to do this, but if you did, you would find a difficulty cause by having two possible pathways between the two. This also goes between tblSong and tblPreset.

In both cases, you have one/many (or many/one) relationships involved. When this happens, Access - which is a combinatorial engine - will iterate on any unconstrained relationship that has multiple values in that field, leading to queries returning more records than exists in either table.

The suggestion to collapse two tables into one and add a field to discriminate between their origin would eliminate this problem.

tblSong would not be related to tblTrackEffects. tblTrackEffects was a joining table I was using because I was thinking the relationship between tblTracks and tblEffects was M:M. I'm pretty sure I am wrong and the relationship is just 1:M. The same for tblSongs and tblEffects. I had created a join table tblSongEffects, but this isn't necessary. I do not need to know the songs or tracks each Effect is in, I only need to know which effects are in a given song or track.

Here's the issues I see:



1. tblSongs-tblSongEffect is a many-many relationship. Read the above 2 sentences, the first makes an assertion, then the second immediately contradicts it. Coincidence or not, one song can have many effects and one effect can belong to many songs. You need a junction table between tblSong and tblSongEffect to sort that out.

2. tblTrack-tblTrackEffect is a many-many relationship as well. See #1 for reasoning. You need a junction table between them as well.

3. Relationship Loops. There should only be 1 way to trace a path between tables using relationships. Between tblTrack an d tblSongEffect I can trace 3 paths. That's not right. Don't know what is, but 2 need to go.

Then I have thoughts that need more information to flesh out:

4. tblSongEffect and tblTrackEffect might be merged. I'd make changes 1 and 2 above and then work on this. Those 2 tables have a lot of same fields that lend them to merging.

5. tblSong--are _MidiFile, _MP3 and _Wav mutually exclusive? Are they Yes/No fields? If a new format is added how do you envision accomodating it? My guess is that instead of those 3 fields, you just need one _MediaType

1. I have been thinking the same thing. But I think I am wrong. If you consider the Student to Classes example that is always used for a M:M relationship, I do not need to know both sides. I only need to know the Track or Song side. I only need to know what students are in each class. I never need to know a list of classes a student is taking.
2. Same as 1.
3. I need to study this one.
4. Actually, the Song could be thought of as a Track and one table could handle the information. The tracks need to be numbered, so I could always make the Song Track "Track 0". I separated them because in the form I have to display the song effects separately from Track Effects.
5. The 3 media type fields are actually paths to different files for each song. Each song has a score (.pdf file) a Midi note backup file (.mid) and an audio file (.mp3).

 

[troutusa]

Also, there is no Effects table. Is there a list of Effects and can an Effect apply to either a song or a tract? If you add an Effects table, you then have two junction tables. One to connect Effects to Tracks and the other to connect Effects to Tracks.

Are Presets related to Effects or to Songs and Tracts? The diagram seems to conflict and have this relationship at different level.

Effects are actually Presets. A Preset can be an Effect, an Instrument, or an Audio Clip. You set a bunch of settings on a VST and then save it as preset. The VST can be a sound producer (synthesizer), or a sound modifier (echo box). Each VST has many settings to make before you get what you want for a track. So the settings are saved as Presets.

 

[Pat Hartman]

You have Preset related to both track and track effects. That doesn't make sense. I think you need to remove the PresetID from the track table.

 

[GaP42]

Based on the above discussions / clarifications .. I think the structure is like this:

The dark line indicates a mutually exclusive relationship of EffectUsed to Track and Song.
A key question that needs explicit consideration is whether a Preset applies to the Effect or to the use of that effect. As you said, a VST allows a range of Presets that can be retained and used in application to tracks or songs. However, you have also said "Effects are actually Presets" - which, unless there are specific attributes belonging to an effect that do not belong to a Preset, would mean these two could collapse to one table.
BTW Are songs always characterized (by you) as one genre or multiple? Similarly, for MediaType - can a song and have multiple media types. If multiple, then introduce junction tables for these.

 

[gemma-the-husky]

What are you actually doing?

Writing songs with samples, and effects at various points?

Can you describe the structure of a song, and list the data elements you want to store in the database?

Are effects applied to the song as a whole, or to sections of a song. Or both?

 

[troutusa]

What are you actually doing?

Writing songs with samples, and effects at various points?

Can you describe the structure of a song, and list the data elements you want to store in the database?

Are effects applied to the song as a whole, or to sections of a song. Or both?

I compose songs. Think of it like a band. There is a guitar, drums, bass, violin that VST instruments each with their own track. The guitar has a distortion pedal. The entire band has a reverb pedal. The pedals are VST effects. Since the instruments and effects are all created synthetically each is comprised of many settings. The VST allows you to save a group of settings and call it a preset.

Early on I had some a hard drive corruption and lost a great deal of work. So I note the most important information for each song. I also export the midi information. (Midi is the digital record of all note events.) If a project becomes corrupted or a VST comes out with an update that messes up the settings I can look at my notes and get back to the original fairly easily.

It is a bit of overkill, but I also like to work with access. As a hobby, I've built a few databases that I use regularly.

1 song has several tracks, and at least one effect.
Each track may have multiple effects.
(Song effects and track effects come from the same VST's.)
I could consider the Song as a Master track, and do away with the segregating Song effects from Track Effects.

I hope this helps.

 

[troutusa]

Based on the above discussions / clarifications .. I think the structure is like this:
View attachment 115611The dark line indicates a mutually exclusive relationship of EffectUsed to Track and Song.
A key question that needs explicit consideration is whether a Preset applies to the Effect or to the use of that effect. As you said, a VST allows a range of Presets that can be retained and used in application to tracks or songs. However, you have also said "Effects are actually Presets" - which, unless there are specific attributes belonging to an effect that do not belong to a Preset, would mean these two could collapse to one table.
BTW Are songs always characterized (by you) as one genre or multiple? Similarly, for MediaType - can a song and have multiple media types. If multiple, then introduce junction tables for these.

Effects and Instruments are always Presets.
I could consider the song effect relationship a Master track relationship, then I would only need Tracks related to Effects. (Tracks are numbered, so I could make the song track number Zero for all songs. That way I can pull out song effects in any report or form view easily enough.
The genre is a 1:M. It's rather an afterthought and not worth giving much attention to. It's the Tracks, Effects, and Presets that are important to storing the data I really need.

 

[troutusa]

Based on the above discussions / clarifications .. I think the structure is like this:
View attachment 115611The dark line indicates a mutually exclusive relationship of EffectUsed to Track and Song.
A key question that needs explicit consideration is whether a Preset applies to the Effect or to the use of that effect. As you said, a VST allows a range of Presets that can be retained and used in application to tracks or songs. However, you have also said "Effects are actually Presets" - which, unless there are specific attributes belonging to an effect that do not belong to a Preset, would mean these two could collapse to one table.
BTW Are songs always characterized (by you) as one genre or multiple? Similarly, for MediaType - can a song and have multiple media types. If multiple, then introduce junction tables for these.

Presets and Effects are the same thing. Effects are a subset of Preset Types. The problem is a Track has one Instrument Preset, but 0 to many Effect Presets. This is where I am running into trouble and really need help. There are 2 separate relationships with a track record and the Preset table. The 1:1 Track:Instrument and 1:M Track:Effect where Instruments and Effects are both records from the Presets table..

 

[gemma-the-husky]

I was thinking you meant tracks as in album tracks. You mean tracks as in multi track recordings. Is this dealing with mixing settings as well then, where you can increase the volume of individual elements, and other stuff that's so far beyond me I have no idea.

When you say track zero is the song, do you mean human voices, lyrics. Would there be only one such track then?

Let's say you have a bass guitar track. Do the effects on that track vary during the finished song, and you have to incorporate volume changes and effects within the information you are storing? Or is this more conceptual to remind you of how you designed the performance.

It's even hard not being sure we aren't at crossed purposes with your terminology. Eg, song doesn't sound like it's the term for the complete performance.

Maybe others know precisely what you mean.

 

[GaP42]

1. With Songs considered as a "special" track the merge of Songs and Tracks will require you to introduce a self- referential relationship for Tracks - which will enable you to see which tracks comprise a song (NO specific need for track no 0 to see that it is a sing, however you need an isSong T/F attribute: this can also then be used to prevent song being built of another song (I presume)
- re the self-referential relationship - It expresses that a track MAY be composed of many other tracks, and a track MUST be a member of a Parent Track (song) - however that should be MAY not MUST (optional - the tool would not allow me to represent that with 'o'. It will mean Songs are not required to be composed of one or more tracks and a track may exist that does not belong to a song.

2. Regarding Presets: If the preset has a type of "Instrument" or "Effect", then use that to control use of Instrument Presets to a track. Thus only one relationship is needed from track through to Preset via EffectUsed.

3. See the below E-R model : Effects/ Presets are now combined, Tracks and Songs are also merged.. Presets can be reused through the EffectUsed table.

Edited - made the change to the self-referential relationship

 

[troutusa]

I was thinking you meant tracks as in album tracks. You mean tracks as in multi track recordings. Is this dealing with mixing settings as well then, where you can increase the volume of individual elements, and other stuff that's so far beyond me I have no idea.

When you say track zero is the song, do you mean human voices, lyrics. Would there be only one such track then?

Let's say you have a bass guitar track. Do the effects on that track vary during the finished song, and you have to incorporate volume changes and effects within the information you are storing? Or is this more conceptual to remind you of how you designed the performance.

It's even hard not being sure we aren't at crossed purposes with your terminology. Eg, song doesn't sound like it's the term for the complete performance.

Maybe others know precisely what you mean.

A song is comprised of tracks, like guitar, vocals, drums, etc. Mix all the tracks together and you get the song. Effects can be applied to individual tracks, or the entire song. The entire song is like a Master Track that is a mixdown of all the instrument tracks.

It is a bit confusing if you have never worked with a Digital Audio Workstation (DAW) like Cubase. A song is a collection of tracks that may have effects (like reverb or delay) applied to them. The song itself may have effects applied to it (like EQ, or compressor). The effects are the same software (VST's), but applied at different levels in the song structure.

 

[troutusa]

1. With Songs considered as a "special" track the merge of Songs and Tracks will require you to introduce a self- referential relationship for Tracks - which will enable you to see which tracks comprise a song (NO specific need for track no 0 to see that it is a sing, however you need an isSong T/F attribute: this can also then be used to prevent song being built of another song (I presume)
- re the self-referential relationship - It expresses that a track MAY be composed of many other tracks, and a track MUST be a member of a Parent Track (song) - however that should be MAY not MUST (optional - the tool would not allow me to represent that with 'o'. It will mean Songs are not required to be composed of one or more tracks and a track may exist that does not belong to a song.

2. Regarding Presets: If the preset has a type of "Instrument" or "Effect", then use that to control use of Instrument Presets to a track. Thus only one relationship is needed from track through to Preset via EffectUsed.

3. See the below E-R model : Effects/ Presets are now combined, Tracks and Songs are also merged.. Presets can be reused through the EffectUsed table.

View attachment 115618
Edited - made the change to the self-referential relationship

View attachment 115619

That looks right, but it doesn't show that there is also an Instrument Preset from tblPreset in the tblTrack. (Presets can be Instruments, effects, or Sound clips. An effect Preset is never an instrument Preset, but the same VST can sometimes be either an instrument or an effect.