Verifying Table structures and relationships

[Minty]

An anology that might help is an invoice with line items and a line discount.
On top of the line discount you might also have an overall Invoice discount.
They are both discounts but applied at different levels.

So your Song is the invoice. The line items are your Tracks, the discount is the Effect.

The additional layer of complexity is that you can have multiple discounts per line, and those discounts can be stored as defined groups. These are your Presets .

Does that explain it correctly?

 

[GaP42]

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

A PRESET of type Instrument is used (associated to) a TRACK through the EFFECTUSED table. (as all PRESETS do)
PRESETS have a Type Table - is this where you set them to be of type instrument or effect or sound clip?
As the same VST can be used (sometimes) for a PRESET of type Instrument or effect - this would imply to me that although the VST settings mat be the same you would have 2 separate PRESET records for these cases.

 

[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

Thank you so much for your help. I need to get up to speed on self-referential relationships before I understand it fully.

 

[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

By the way, what modeling software are you suing to create the above diagram?

 

[GaP42]

Visual Paradigm Community Ed - not for commercial use. Just a learner with the tool

 

[troutusa]

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

 

[troutusa]

A PRESET of type Instrument is used (associated to) a TRACK through the EFFECTUSED table. (as all PRESETS do)
PRESETS have a Type Table - is this where you set them to be of type instrument or effect or sound clip?
As the same VST can be used (sometimes) for a PRESET of type Instrument or effect - this would imply to me that although the VST settings mat be the same you would have 2 separate PRESET records for these cases.

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.

 

[troutusa]

Visual Paradigm Community Ed - not for commercial use. Just a learner with the tool

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?

 

[GaP42]

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.

 

[troutusa]

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?

 

[GaP42]

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.

 

[troutusa]

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.

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]

Here is my current form of documentation. It may shed some light on how I use the information and how it is related.
View attachment 115623
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.)

Hi
Could you put this data into an Excel sheet and upload?

 

[GaP42]

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?

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

 

[troutusa]

Hi
Could you put this data into an Excel sheet and upload?

 

[GaP42]

Hi
Could you put this data into an Excel sheet and upload?

See post 26 - i don't think it is in that form.

 

[troutusa]

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.

 

[GaP42]

Here is my current form of documentation. It may shed some light on how I use the information and how it is related.
View attachment 115623
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.)

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

 

[troutusa]

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

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.

 

[GaP42]

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.