AnsweredAssumed Answered

music-database: correct entity relations diagram?

Question asked by filemakerbro on Jun 29, 2018
Latest reply on Jun 29, 2018 by Lemmtech

Dear all,

 

I am designing a database for the musiclibrary of a radiostation.

In this data modeling process I have some questions/iddues I want to share with you, ask for your advice and help.

Some background infomation first:

  • The music comes from a. CD's and b. from  several harddiscs. I named the CD's and HD's: "sources".
  • A lot of tracks exist double of threedouble (they appear and on different CD's and/or on different HD's). There are also several versions of titles: either covers or remixes etc..
  • 80% of all the music is "catalogued" in excel spreadsheets. FMPro has to import those spreadsheets.

First question I have is concerning the data modeling-proces: I'm defined  tables, fields and the relationships between the tables.

 

TABLES:
1.         artist

2.         title of track (fields: version, time: intro, time track, ending (fade or cold), year, BPM,

            Album, Sound, Mood, Intensity, Familiar, Gender, Country, Artist-Group, Chart Position)

3.         source (fields: CD, HD)

 

RELATIONSHIP BETWEEN TABLES
artist <-> title  = many to many (1 artist can have many titles and 1 title can have many artists (!covers)). I need a joint table.

 

JOINT TABLE A

track (relation between table 1 (artist) & table 2 (title)

RELATIONSHIP BETWEEN TABLES

track <-> source = many to many (1 source can have many tracks and1 track can be on many sources). Again, I need a joint table.

 

JOINT TABLE B

track-source (relation between joint table A (track) and table 3 (source)

This is the entity relationship diagram (so far):

 

DIAGRAM.jpeg

 

 

First QUESTION: Is this the correct ERD or am I missing something and/or did I make a mistake?

 

Second QUESTION concerns importing data from the spreadsheets.

Every artist needs his own unique primary key field: I don't want 120 different PKF for - let's say- The Beatles after importing the excel-sheets.

What I need is a script that only imports those Artists (and gives them a PKF) who are not already in the database. 

 

Is this a good method,  a script, or is there another way to accomplish this? And how would that script look like? I am not an experienced script-writer.

Thanks in advance for you assistance.

 

Regards,

Jan

 

 

 

 

 

 

Outcomes