Sql Data Modelling

Question

Im attempting to build a database (pictured)

One of the criteria is that any instrument that is used can only be used by 1 musician at a time.

I was wondering how I would implement that it such a schema?

Another criteria is that 1 of the musicians on each song is the producer. How would I implement that criteria so that the producer has to come from the list of the musicians on that song?

Maybe this is 2 unrelated questions, I'm not sure?

Answer 1

First question:

If you merge the songinstruments and songmusicians table like this: songinstrumentsmusicians:

SongId
InstrumentId
MusicianId

and add a unique constraing for (SongId,IntrumentId)

Second question:

Add a producer flag to songmusicians (or songinstrumentsmusicians).

Answer 2

1st Question: along the lines that @lajos-veres was heading down:

Create a table: MuscicianInstrument

PK|ID

FK|MusicianID | int

FK|InstrumentID | inn

Endate | datetime

Following this: (link) (This is assuming SQL Server)

Create a custom check function and check constraint:

CREATE FUNCTION CheckActiveCount(
 @MusicianId INT, @InstrumentId INT
) RETURNS INT AS BEGIN

  DECLARE @ret INT;
  SELECT @ret = COUNT(*) FROM MusicianInstrumnet 
     WHERE MusicianId = @MusicianId AND InstrumentId = @InstrumentId and (Enddate IS NULL  OR Endate > CURDATE());
  RETURN @ret;

END;
GO

ALTER TABLE MusicianInstrumnet
  ADD CONSTRAINT CheckActiveMusicianInstrument CHECK (NOT (dbo.CheckActiveCount(MusicianId, InstrumentId) > 1));
GO

Have to run, will try to tackle #2 after an exam.