Jump to content
Kurt Hansen

Planning a relational database for (classical) music

Recommended Posts

I have some 36,000 CDs in a flat database (FM Pro 11).

I am considering building a new one, where the different data elements have their own table and bring it all together with relationships.

It would be a great help to analyze a sample database, but Google has not given a useful result. The example database need not necessarily be tailored to classical music, but the principle should resemble my needs, so I can learn about the structure. Where can I find such a download?

I realize that the following is rather loose and that it will show retail issues, but here are my initial thoughts:

Tables:

COMPOSER(s)
- First name
- Surname
- The year of birth
- The year of death
- Country

About birth / death: Accurate data are not always available, especially for early composers. There must be given approximate, but still so that composers can be listed in chronological order.

ARTIST(s)
- First name
- Surname
- Category example. soprano, trumpet player, pianist

ITEM
- Product Name / Title
- Catalog Number
- Barcode (with validation)
- Purchase price
- Wholesale price
- Retail price
- Release date
- Genre (classical, jazz, traditional etc.)
- Category (orchestral music, chamber music, opera etc.)
- Media (1 CD, 2 SACD, 1 CD + 1 DVD etc.)

RECORD COMPANIES
- Name
- Account number
- Supplier

Share this post


Link to post
Share on other sites

Based on your description, I would expect to see something like:

MusicLibrary.png.f152395fabe6b198dc64f62d7a1d26f8.png

The Composers and Performers tables could be possibly united into single Artists table, albeit retaining its dual role.

If you have duplicate performances on different media, then you may need another join table of Tracks in-between Performances and Media.

Share this post


Link to post
Share on other sites

I'm sorry, Comment. I have once again managed to express myself unclear, although I myself think that I make a great effort not to do so.
I see your sketch as a structure to a record collection. I ask, however, as representant of company selling music on disc media, and it is in this light my structure scetch must be seen.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • By Sergej Ivanov
      Hi, I got stuck with building a proper relationship scheme.
      I'm making an database with several tables. One table contains information on archaeological features (key field Feature ID), another contains data on documentation. One document may contain information on several features, for this reason in documentation table I've created several fields (Feature ID 1, Feature ID 2, etc). The two tables are related through Feature ID=Feature ID 1; in the second table I've made a self-relation between all Feature ID fields.
      In the Features layout I've made a portal displaying related records from Documentation table. Unfortunately it only displays related to the first field (Feature ID 1), while other fields (Feature ID 2, etc) seem remain unrelated. 
      What do I do wrong? Thank you!
    • By fmow
      I 'm setting up a database where one of the main tables (entities) is one called projects. This table involves a project number and related information including a client for the project and employees involved in each project. I 'd like to keep separate tables for our clientele and our employees. And of course use their data to populate the projects table. 
       
      And I 've really confused myself as to how to relate these tables with keys. Each project can have one client, and more than one employees. Could I just keep them unrelated and populate the projects table via lists?
       
       
      There's also another table for salaries where each employee will get a % involvement in each project and a salary for it. This is clearly a new entity, and I can relate it to the projects table via a foreign key of project number, and have fields for the name and surname of the employee, their % involvement and fee. Hmmm... ok I think I got this part, let me run it by you guys, I will relate the unique serial for each employee via a foreign key to the salaries table and keep the name and surname fields in the parent employees table and use these fields on the layout to populate... I think I lost it again...
       
      Any help much appreciated. 
       
      (as an aside I have to say I am really enjoying learning filemaker and using it, though the curve is steeper than I expected).
    • By NCB
      I have a table (table1) that is related to another table (table2) by its key. When a record is created in table1, a new record is created in table2. This is defined by their relationship.
       
      I want the new record in table2 to have some checkbox fields auto-populated.I have tried using the auto-fill option for the specified fields in the related table, but it is not working. The only thing that seems to work is a script with some "Set Field" calls to those fields, but I have only found an "onRecordCommit" script trigger to run this, and I need to to run as soon as the record is created. Is there a better way to do this?
       
      Thanks.
    • By imoree
      Hey Everyone;

      JUst wanted a chime in on this.

      I have just started to add a new way of creating records and wanted to know if you all thought / think this in necessary.

      eg.

      tbl_PRODUCTS LAYOUT { X } tbl_Products»attributes (Cartesian join)

      tbl_PRODUCTS LAYOUT { ID = ID & attributes_ID (ALLOW CREATE) } tbl_Products»attributes»create

      -i
    • By Kevin_J
      I thought I understood that different table views are simply that - VIEWS into the same table, like when you establish a self-join on a key and FM creates a new view for that table.

      I also thought that data in a field in a table is the same data, no matter which view I am using to display it.

      Extending that, if I enter or change data in a field using one view, that field will reflect the change to that field in any other view.

      Now, I understand the concepts of filtering and lookups that might change WHAT you're viewing or HOW you're viewing - but not the DATA itself.

      But I have a very strange situation that sure looks like my understanding has been wrong all along. Once I saw strange behavior, I set up a test situation where the same field, viewed twice in a single layout, with one instance of the field pointing to the initial table and the other instance of the same field pointing to a different view of the same table is displaying different data! How can that be?

      I know I've provided no specifics here and can certainly do so, but is my basic understanding wrong? What could account for what I'm seeing?

      Thanks for any thoughts.

      - KJ
      (now stumped and stuck)
×

Important Information

By using this site, you agree to our Terms of Use.