Extending a flat database to a related ditto

[Kurt Hansen]

I have a flat database of classical CDs, which are a part of our inventory. It includes a field "Composer(s)", where the content are written in free form.

I would like to make an extension with a composer database and create a relation between the two, so I can look up composers and see what CDs we have with them, and vice versa: look up a CD and see what composers are acting on it. Our composer field is not complete. If more than 3-4 composers, the text are written just "a.o." or "and others".

I have no experience with relationships, so it must be simple and explained step by step.

I have found this thread http://fmforums.com/forums/topic/50942-portal-grouping-problem/#comment-239210 and downloaded "Consultants" JoinDemo.fp7. It is just so simple so I should be able to figure out how to customize it, but how do I (as a user) create a new contact and a new organization and create a relationship (affiliation)?

I'm still using FM 11 Pro Advanced ;-)

[comment]

I am not sure what exactly your question is. If you're asking how to migrate your data to a relational setup, I don't think we have enough information to answer that. A lot depends on what "free form"" means. If it's something that can be manipulated into a return-separated list of names, then you might have a starting point. Another issue is how different the names of the same composer are between the various compositions. And of course, a computer cannot replace "and others" with the actual names.

With regard to the demo: you create a new contact by creating a new record in the Contacts table. Likewise for a new organization. To create an affiliation, there are three possible ways:

1. Select an OrganizationID using the dropdown menu in the first empty portal row on the Contacts layout;
2. Select a ContactID using the dropdown menu in the first empty portal row on the Organizations layout;
3. Create a new record in the Affiliations table and populate the ContactID and the OrganizationID fields with the ids of the two records you wish to relate.

Note that there is no way to create a new contact while creating a new affiliation for an organization, or vice versa (although one could be devised using a script).

 

[Kurt Hansen]

Thanks for replying, Consultant. Our composer field is a complete anarchy entered by different people over a period of several years.

Examples:

Arnold / Price
Williams John
Scarlatti, Alessandro
Blarr / Dillen / Denhoff a.o.
Scarlatti A / Albinoni / Vivaldi / Torel

I am working with it right now. The goal is to separate each composer for all CDs and create a new database with "Composers". I believe that I will also need a third database to enable relations between composers and CDs.

[comment]

How many records do you have?

[Kurt Hansen]

How many records do you have?

28,372 ;-)

[Kurt Hansen]

With regard to the demo: you create a new contact by creating a new record in the Contacts table. Likewise for a new organization. To create an affiliation, there are three possible ways:

1. Select an OrganizationID using the dropdown menu in the first empty portal row on the Contacts layout;
2. Select a ContactID using the dropdown menu in the first empty portal row on the Organizations layout;
3. Create a new record in the Affiliations table and populate the ContactID and the OrganizationID fields with the ids of the two records you wish to relate.

Hmmm, when I create a new contact and a new organisation two records are created automaticly in "Affiliations":

ID 9 ::Name = Kurt and ID 10 ::Organization = Danacord

I can now choose to connect Kurt with Danacord and delete the othrer record. Is that the way to do it? I guess not

[comment]

28,372 ;-)

Ouch. Isn't there some online database you could use to lookup the contents of a CD, based on the title or some other ID? Hopefully, they would have a uniform list of composers.

Otherwise you are looking at going over 28k records and manually entering the composers for each. If I had to this, I would start by importing a list of all composers from a reputable source, define a value list and use a checkbox to select the composers on each CD. In fact, I might stop after this point. A two-table many-to-many arrangement could well be sufficient in your case.

 

 

Hmmm, when I create a new contact and a new organisation two records are created automaticly in "Affiliations":

I don't think so.

Edited July 27, 2015 by comment

[Kurt Hansen]

Ouch. Isn't there some online database you could use to lookup the contents of a CD, based on the title or some other ID? Hopefully, they would have a uniform list of composers.

Otherwise you are looking at going over 28k records and manually entering the composers for each. If I had to this, I would start by importing a list of all composers from a reputable source, define a value list and use a checkbox to select the composers on each CD. In fact, I might stop after this point. A two-table many-to-many arrangement could well be sufficient in your case.

Yes, Consultant, there's no easy way to do this :-(

  On 27/7/2015, Kurt Hansen said:

Hmmm, when I create a new contact and a new organisation two records are created automaticly in "Affiliations":

I don't think so.

Hmmm, what do I do so?

[MikeKD]

Mmm....

A few thoughts.

1. Although you have 28,000 CDs, there won't be 28,000 composers.
2. Some CDs will have pieces by more than one composer.
3. Composers will have written more than one piece.
4. So you'll need a third table to join multiple composers with multiple pieces / CDs.
5. Pieces and CDs aren't the same thing, so may be that third table is actually pieces?
6. Though very occasionally, pieces are by more than one composer too (e.g. Posthumous completions)
7. There's got to be a fairly complete table, cvs, xl file of composers somewhere!!
8. 28,000?!!!!! OMG.

Cheers,

Mike

Here's a CSV from Yale:

http://ycac.yale.edu/downloads