locked
What's unique for a song Recording - Band? Artist? Song? Date? RRS feed

  • Question

  •  

    I'm struggling to design a music database to track artists/bands, albums, songs, recordings etc.  I want to identify the fields (including either solo-artist, band or perhaps duet) that make a Recording entity unique.  I've considered that Band, Song and Date Recorded would make a unique key for a Recording  (though that won't account for multiple recordings on the same day).  I believe a Recording should be unique to a Band or a Solo-Artist (but not both, so which?). 

     

    I've considered making a Recordings table, a Bands table and a Band_Recordings table (the Beatles may have multiple versions of Yesterday).  That would mean Ringo Starr becomes a band!  Not quite right.  I could call the Band table Acts or Performed_By to include both solo artists and bands but that seems wrong.  What would go in the fields First_Name & Last_Name for the Beatles? 

     

    I would like to be able to have an Artists table (Ringo, John, etc), a Bands table (Beatles) and a Band_Artists table (Beatles/Ringo, Beatles/John, etc).  With this scenario what foreign key lands in Recordings?  Artist_ID?  Band_ID? Both?  And again, what fields combine to uniquely identify a Recording?

     

    Another part of my problem is that for one Recording (Beatles/Yesterday), I'd like to also identify it as a Paul McCartney Recording (or John, George or Ringo) in addition to being a Beatles Recording. 

     

    I'm stuck!  Certainly this type of database is often created, but I can't see how.  Any answers or guidance is much appreciated. 

     

    Thanks in advance.

     

    Monday, January 7, 2008 5:10 PM

Answers

  • I enhanced the proposed design with actual tables:

    CREATE TABLE ARTIST(
    	ARTISTID INT IDENTITY(1,1) PRIMARY KEY,
    	ARTIST_FNAME VARCHAR(50),
    	ARTIST_LNAME VARCHAR(50),
    	DOB	DATETIME);
    
    CREATE TABLE SONG(
    	SONGID INT IDENTITY(1,1) PRIMARY KEY,
    	SONGNAME NVARCHAR(100), 
    	SONGDESCRIPTION NVARCHAR(100),
    	[LANGUAGE] VARCHAR(20), 
    	SONGTYPE VARCHAR(20));
    
    CREATE TABLE SOUNDTEAM(
    	SOUNDTEAMID INT IDENTITY(1,1) PRIMARY KEY,
    	SOUNDGROUPCODE INT, 
    	SOUNDDESCRIPTION VARCHAR(100), 
    	SOUNDTYPE VARCHAR(10), --(BAND,SOLO,DUET) 
    	ARTISTID INT REFERENCES ARTIST);
        
    CREATE TABLE RECORDING(
    	RECORDINGID INT IDENTITY(1,1) PRIMARY KEY,
    	SONGID INT REFERENCES SONG,      --FK
    	SOUNDTEAMID INT REFERENCES SOUNDTEAM,     --FK
    	RECORDINGDATE DATETIME, 
    	RECORDEDBY NVARCHAR(100));
    


    Kalman Toth Database & OLAP Architect SELECT Query Video Tutorial 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Saturday, January 25, 2014 1:01 AM
  • The basic design structure will look like as,

    ARTIST ARTISTID INT, -- PK ARTIST_FNAME VARCHAR(50), ARTIST_LNAME VARCHAR(50), DOB DATETIME SONG SONGID INT, --PK SONGNAME INT, SONGDESCRIPTION NVARCHAR(100), [LANGUAGE] VARCHAR(20), SONGTYPE VARCHAR(20) SOUNDTEAM SOUNDTEAMID INT, --PK SOUNDGROUPCODE INT, SOUNDDESCRIPTION VARCHAR(100), SOUNDTYPE VARCHAR(10) --(BAND,SOLO,DUET) ARTISTID INT --FK RECORDING RECORDINGID, --PK SONGID, --FK SOUNDGROUPCODE, --FK RECORDINGDATE, RECORDEDBY Refer ARTIST and SOUNDTEAM, In a "SOUNDGROUPCODE" there can be many "ARTISTID" and "SOUNDTYPE" can be either a Band/Solo/Duet. The relation between ARTIST and SOUNDTEAM is N:M.

    Each recording has a unique identifier i.e "RECORDINGID", "RECORDINGDATE" and "RECORDEDBY" along with a soundgroup.



    Regards, RSingh


    Friday, January 24, 2014 4:19 AM

All replies

  • Dan,

    Is this still an issue?

    Thanks!


    Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Thursday, January 23, 2014 6:13 AM
  • The basic design structure will look like as,

    ARTIST ARTISTID INT, -- PK ARTIST_FNAME VARCHAR(50), ARTIST_LNAME VARCHAR(50), DOB DATETIME SONG SONGID INT, --PK SONGNAME INT, SONGDESCRIPTION NVARCHAR(100), [LANGUAGE] VARCHAR(20), SONGTYPE VARCHAR(20) SOUNDTEAM SOUNDTEAMID INT, --PK SOUNDGROUPCODE INT, SOUNDDESCRIPTION VARCHAR(100), SOUNDTYPE VARCHAR(10) --(BAND,SOLO,DUET) ARTISTID INT --FK RECORDING RECORDINGID, --PK SONGID, --FK SOUNDGROUPCODE, --FK RECORDINGDATE, RECORDEDBY Refer ARTIST and SOUNDTEAM, In a "SOUNDGROUPCODE" there can be many "ARTISTID" and "SOUNDTYPE" can be either a Band/Solo/Duet. The relation between ARTIST and SOUNDTEAM is N:M.

    Each recording has a unique identifier i.e "RECORDINGID", "RECORDINGDATE" and "RECORDEDBY" along with a soundgroup.



    Regards, RSingh


    Friday, January 24, 2014 4:19 AM
  • I enhanced the proposed design with actual tables:

    CREATE TABLE ARTIST(
    	ARTISTID INT IDENTITY(1,1) PRIMARY KEY,
    	ARTIST_FNAME VARCHAR(50),
    	ARTIST_LNAME VARCHAR(50),
    	DOB	DATETIME);
    
    CREATE TABLE SONG(
    	SONGID INT IDENTITY(1,1) PRIMARY KEY,
    	SONGNAME NVARCHAR(100), 
    	SONGDESCRIPTION NVARCHAR(100),
    	[LANGUAGE] VARCHAR(20), 
    	SONGTYPE VARCHAR(20));
    
    CREATE TABLE SOUNDTEAM(
    	SOUNDTEAMID INT IDENTITY(1,1) PRIMARY KEY,
    	SOUNDGROUPCODE INT, 
    	SOUNDDESCRIPTION VARCHAR(100), 
    	SOUNDTYPE VARCHAR(10), --(BAND,SOLO,DUET) 
    	ARTISTID INT REFERENCES ARTIST);
        
    CREATE TABLE RECORDING(
    	RECORDINGID INT IDENTITY(1,1) PRIMARY KEY,
    	SONGID INT REFERENCES SONG,      --FK
    	SOUNDTEAMID INT REFERENCES SOUNDTEAM,     --FK
    	RECORDINGDATE DATETIME, 
    	RECORDEDBY NVARCHAR(100));
    


    Kalman Toth Database & OLAP Architect SELECT Query Video Tutorial 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Saturday, January 25, 2014 1:01 AM