Triangular Relationship?

[Bazza]

Bit of a newbie at all this. I understand simple relationships, but some confuse me. I've looked at other posts in forums, downloaded sample databases, but not been able to find anything quite like this? I've come across this problem before, and am not sure how to resolve it..

A simple music database, tables as below (there'll be a bit more to it than this, but not much - my requirements are quite simple):
tblArtist
ArtistID
Artist

tblRecording (CD/Album)
RecordingID
Recording
ArtistID (recording artist)

tblTracks
TrackID
Track
RecordingID
ArtistID (track artist)

If the Recording is a various artists, I plan to have an Artist called Various Artists for the Recording, and then the tracks will have the individual Artists against them.

When I draw this out, 1 artist to many recordings, 1 recording to many tracks, but then I've also got a 1 artist to many tracks - hence my "Triangle".

Can tables be related like this in a triangle, or will this cause problems when I try to retrieve data through queries later on?

Perhaps I haven't planned this well - for example I've seen other posts mention a linking table between the artist and the other tables - but surely this will simply move the triangle to be between the link table, recording table and track table?

Any help would be appreciated.

 

[georgedwilkinson]

Your scenario seems much simpler than real-life. But sticking to your simple requirements, you would need:
Performers
PerformerID
Name
etc.

Performances
PerformanceID
Name
etc.

Compilations
CompilationID
AlbumName
etc.

PerformerRoleTypes
PerformerRoleTypeID
Type
etc.

PerformerPerformances
PerformerID
PerformanceID
PerformerRoleTypeID
etc.

Tracks
TrackID
CompilationID
PerformanceID
Maybe a reference to the performer who get's top billing
etc.

This should allow you to gather information about all of the artists and other performers/composers/etc. (to make it more generic, you could drop the "Performer" nomenclature in favor of something that more accurately describes the types of parties involved in the music or performance making process).

Of course, this may be a little too much for your actual needs, I'm just going by what I think you're saying is your need.

 

[Bazza]

Thanks very much for your help.

My simple requirements stem from a spreadsheet that is currently doing the job with just 7 pieces of data (TrackNo, TrackTitle, ArtistName, Genre, RecordingTitle, RecordingMedia, Date). The reason for switching to a database is because of course many pieces of data are repeated, inviting errors.

I've tried to put together a diagram of how I understand that I should connect the tables that you kindly suggested, which is attached (I tried to do pdf but was too big - hope excel is ok?).

Do I have this right?

Would I be able to connect the tblCompilations to the tblPerformerPerformance, so that a compilation can link through to a performer called 'various artists', or is the idea of having a seperate compilation artist to avoid this?

Apologies for asking what is probably pretty basic questions, but I have to learn somehow

 

[georgedwilkinson]

It looks generally right (I just woke up from a long nap though). Except:
1. PerformerPerformances should have a FK for PerformanceID and PerformerID. It is where you track the resources that "performed" a certain performance. You don't need the PerformanceName there since that data is already captured in the Performances table.
2. If you need the performer (or performers) on a compilation, you'll need to either put a column in the Compilations table to list the "single" or "main" performer or duplicate the M:M PerformerPerformance with a junction table like:
PerformerCompilations
CompilationID
PerformerID
PerformerRoleTypeID
etc.

BTW, this is not written in stone. I simply took your description of the problem and diagrammed it.

Questions are a good thing. You are doing right to ask them.