Hello folks,

Rank newbie here. FM Pro 6, Mac OS X, G-4 Dual, 1.5 GB RAM.

I'm creating a database of hundreds of bands. Beside the text field
listing each member's name, I want a drop-down (look-up?) list of which
instrument is played: Guitar, bass, drums, vocals, keys, saxophone,
etc. So, I have two questions:

1. What if one musician sings, plays guitar AND plays keyboards? Do I
put three (or more) lookup fields beside the name?

2. After I build this, how do I search for, say, JUST all the bass
players?

Sorry if this is entirely too elementary for this group. I'll buy a
book next week, I promise.

Thanks!

- SDOX

In article <030520051308196597%killme@killme.com>, killme@killme.com 
says...
> Hello folks,
> 
> Rank newbie here. FM Pro 6, Mac OS X, G-4 Dual, 1.5 GB RAM.
> 
> I'm creating a database of hundreds of bands. Beside the text field
> listing each member's name, I want a drop-down (look-up?) list of which
> instrument is played: Guitar, bass, drums, vocals, keys, saxophone,
> etc. So, I have two questions:
> 
> 1. What if one musician sings, plays guitar AND plays keyboards? Do I
> put three (or more) lookup fields beside the name?

Displaying it is the least of your worries. You need to build a data 
model first.

> 
> 2. After I build this, how do I search for, say, JUST all the bass
> players?

That would depend on your data model :)
 
> Sorry if this is entirely too elementary for this group. I'll buy a
> book next week, I promise.

You'll need it. This may prove to be quite complicated to do properly.

Why? Because band composition is a morphic collection of members and 
roles over time.

At the very least you'll want a table for bands, for musicians, and 
instruments, as well as tables to capture the many-many relationships of 
bands to musicians, and finally a table for musicians to instruments.

e.g.

bandtable [bandname, bandid]
musiciantable [personname, personid]
instruments [intrumentname, instrumentid]

bandmembers [personid, bandid]
musicianinstruments [personid, instrumentid]

The first 3 tables are simple:
Bands could be:
Alice Cooper, 1
Alice in Chains, 2
Alice Coltrane, 3
The Nazz, 4
....

Band Members:
Alice Cooper, 1
Dennis Dunaway, 2
Alice Coltrane, 3
Ravi Coltrane, 4
Layne Staley, 5
Jerry Cantrell, 6
Sean Kinney, 7

Instruments:
Vocals, 1
Lead Guitar, 2
Bass Guitar, 3
Drums, 4
Saxophone, 5
Harmonica, 6

Now... putting them together:

BandMembers: is just id pairs showing the relationship.

1,1 (Alice Cooper is in Alice Cooper)
2,1 (Dennis Dunaway is in Alice Cooper)
3,3 (Alice Coltrane is in Alice Coltrane)
4,3 (Ravi Coltrane is in Alice Coltrane)
5,2 (Layne is in AiC)
6,2 (Jerry is in AiC)
7,2 (Sean is in AiC)
1,4 (Alice Cooper was also in the Nazz)
2,4 (as was Dennis)

MuscianInstruments:

More id pairings, this time, muscian and instrument

1,1 (Alice Cooper does vocals)
1,6 (Alice Cooper plays harmonica)
2,4 (Dennis is on drums)
3,1 (Alice Coltrane does vocals)
4,5 (Ravi is on Sax)
5,1 (Layne is vocals)
6,2 (Jerry is on Guitar)
7,4 (Sean is on drums)

The reason its done this way is so you can pull up vocals in intruments, 
and quickly find related musicians... Alice, Alice, and Layne.

Similarly you can find Alice Cooper in Muscians, and quickly find 
related bands, and find that he is in "Alice Cooper", and "The Nazz"... 
or quickly find related instruments and find that he did the vocals as 
well as the harmonica.

This is just the -start- of a good database to track this. Clearly you 
might want to know -when- various band members were in which bands, or 
which instruments they played while in which bands. (Perhaps one of them 
did a solo project where they did vocals, but they never sang while they 
were part of their more permanent band...)

Do you need to just track who is in the band right now? Do you need to 
know who was in the band in 1980? Who played drums in 1985? Who played 
drums on the tour in '89 vs in the studio. Who played lead guitar on 
track 2 of their 4th CD? What if someone from one band was featured on a 
track by another band? Do you want to capture that too?

You'll really need to pin down exactly what questions you want your 
database to be able to answer; that will guide your design of the 
datamodel, and once you have that you can begin worrying about how to 
implement the UI.

All the tables i provided were just 2 fields, obviously this can be 
augmented... for example, you could have the bandmember table track the 
date they joined and left the associated band, etc, etc, etc.

Good luck to you. This sounds like a fun project. Best advice I can give 
you, as a "rank amateur" don't start out too ambitious :) this is the 
sort of project that can get out of hand fast :)

In article <MPG.1ce17214d9efe871989aff@shawnews>, 42
<nospam@nospam.com> wrote:

> Displaying it is the least of your worries. You need to build a data 
> model first.

 (much great info snipped for brevity)

Firstly, THANKS!

Perhaps I didn't state my objective clearly enough. I should have said
hundreds of CDs rather than hundreds of bands. I want to build a
database of all my CDs, containing all the information that I
previously mentioned, plus label name, cross-references to
side-projects, etc. But, looking over your answer, I suspect it'll
amount to practically the same thing.

The reason I want to catalog my CDs is because the company I work for
has instructed me to learn Filemaker in order to build a quite
mind-blowingly complicated database requiring many relational databases
and constantly changing data. Essentially, my boss wants to know things
such as:

1. Who bought item #2739 in September, 2003, and lives in Missouri?

2. How many appointments with customers do I have next month and what
time are they?

3. What's the URL for Vendor 123's website?

4. How many grams of solder did we use manufacturing items 1, 543, and
4235 from June 23 through August 4?

I thought I would do the CD database as a way to learn Filemaker before
I start importing huge Excel Spreadsheets and organizing all that data.

Oh, and I have no time limit. This is to be done in addition to all my
OTHER "jobs." But I DO want to learn Filemaker Pro. I've been making my
living with Mac computers since the mid-80s and I've never met a prog I
didn't eventually get pretty good at.

THANKS AGAIN!

-SDOX

On Tue, 03 May 2005 13:08:19 -0500, Sternodox wrote:
>  I'm creating a database of hundreds of bands. Beside the text field
>  listing each member's name, I want a drop-down (look-up?) list of which
>  instrument is played: Guitar, bass, drums, vocals, keys, saxophone,
>  etc. So, I have two questions:
> 
>  1. What if one musician sings, plays guitar AND plays keyboards? Do I
>  put three (or more) lookup fields beside the name?

I'd recommend checkboxes instead

>  2. After I build this, how do I search for, say, JUST all the bass
>  players?

Just do it the 'natural' way: Enter find mode, select to find 'bass'.

If you want to find bass only, you may need some extra work.

You forgot about question 3: how about members which are in several
bands?

I'd recommend two databases:

1) Band
2) Musician

A Band may have several musicians, and a Musician may be part of several
bands. Just build a relationship from a band id to the musician.

But maybe you want to refine your database even further and write down
when someone was a member of which band? 

.... just as an example that you may refine this more and more. Tell us
as soon as you look for this kind of info. I'd recommend a database 
3) History, combining Band, Musician and Dates

Have fun,
Martin

In article <slrnd7gvv7.3fq.t-use@ID-685.user.individual.de>, Martin
Trautmann <t-use@gmx.net> wrote:

> 3) History, combining Band, Musician and Dates
> 
> Have fun,
> Martin

Thanks Martin.

I'm beginning to put the entire thing down on paper, with a multitude
of lines and boxes hopefully indicating sensible interrelationships. I
guess that's the grafix artist in me. If building the database(s) is as
hard as keeping all these boxes and lines straight, I'm in for a hell
of a ride here.

- SDOX

In article <slrnd7gvv7.3fq.t-use@ID-685.user.individual.de>, t-
use@gmx.net says...

> >  1. What if one musician sings, plays guitar AND plays keyboards? Do I
> >  put three (or more) lookup fields beside the name?
> 
> I'd recommend checkboxes instead

I wouldn't. Checkboxes are appropriate when the option set is known in 
advance. Instruments is likely too broad, between Jazz, Rock, and 
Classical you are looking at 30 or so.

I suppose if you are only interested in pop/rock, and only want 
vocals/guitar/bass/keyboards/drums it would make sense. But I think 
you'd just be annoyed as the exceptions kept rolling in ... be it the 
violinist here, the saxist there all the way to Weird Al's accordian....

In article <MPG.1ce2b4d5d79b4ce0989b02@shawnews>, 42
<nospam@nospam.com> wrote:
> I wouldn't. Checkboxes are appropriate when the option set is known in 
> advance. Instruments is likely too broad, between Jazz, Rock, and 
> Classical you are looking at 30 or so.
> 
> I suppose if you are only interested in pop/rock, and only want 
> vocals/guitar/bass/keyboards/drums it would make sense. But I think 
> you'd just be annoyed as the exceptions kept rolling in ... be it the 
> violinist here, the saxist there all the way to Weird Al's accordian....

Actually, it's ALL Metal.

Black Metal, Death Metal, Doom Metal, Thrash Metal, Viking Metal, and
all the incestuous permutations and sub-genres that emerge from this
fascinating music.

And 42 is correct about the number of instruments. There's a Black
Metal band from Hungary called SEAR BLISS that doesn't know Black Metal
bands aren't supposed to play trombones. (Thank goodness! They're
great!) And several Metal bands from countries such as Finland and
Ukraine utilize folk instruments, some of which have names that contain
more letters than this entire post!

Ah well. When I get my paper graph done, I'm sure I'll be back with a
multitude of tedious questions.

Thanks very much for the responses.

By the way, I also listen to Classical (Stravinsky, Mahler and Bartok
being personal favorites) but that's an entirely separate collection
from my Metal CDs.

- SDOX

On Wed, 04 May 2005 14:39:52 -0500, Sternodox wrote:
>  In article <MPG.1ce2b4d5d79b4ce0989b02@shawnews>, 42
>  <nospam@nospam.com> wrote:
> > I wouldn't. Checkboxes are appropriate when the option set is known in 
> > advance. Instruments is likely too broad, between Jazz, Rock, and 
> > Classical you are looking at 30 or so.
> > 
> > I suppose if you are only interested in pop/rock, and only want 
> > vocals/guitar/bass/keyboards/drums it would make sense. But I think 
> > you'd just be annoyed as the exceptions kept rolling in ... be it the 
> > violinist here, the saxist there all the way to Weird Al's accordian....
> 
>  Actually, it's ALL Metal.
> 
>  Black Metal, Death Metal, Doom Metal, Thrash Metal, Viking Metal, and
>  all the incestuous permutations and sub-genres that emerge from this
>  fascinating music.
> 
>  And 42 is correct about the number of instruments. There's a Black
>  Metal band from Hungary called SEAR BLISS that doesn't know Black Metal
>  bands aren't supposed to play trombones. (Thank goodness! They're
>  great!) And several Metal bands from countries such as Finland and
>  Ukraine utilize folk instruments, some of which have names that contain
>  more letters than this entire post!

My recommendation: There's no need to have all instruments on one
layout. You could provide a default selection list for ONE instrument
from the basic instrumentation. With expert knowledge you could learn
how to select more than one instrument from the list (cmd-click?).
However, you might provide a button next to this field which offers a
dedicated instruments layout. You may offer all instruments in one long
alphabetical list, you may use yet another checklist to display typical
metal instruments, another one for classical or folk, one more for free
text input of an instrument. You might even provide dynamical lists for
e.g. any instrument that this musician ever played up to now. 

You might provide categories which are composed (semi-)automatically, search
e.g. for all kinds of trumpets, for all kinds of keyboard instruments
etc.

>  By the way, I also listen to Classical (Stravinsky, Mahler and Bartok
>  being personal favorites) but that's an entirely separate collection
>  from my Metal CDs.

I'd recommend a very different approach to classical musique: A typical
classical piece will span several tracks. On some rare occasions it may
even span several CDs (which I ignore, but duplicate CD data here).

Thus my data organization here is

- CD
- work
- tracks 
- composer
- artist

CD and work are the most important databases, while composer is more a
reference (e.g. including the Bachwerkeverzeichnis) and tracks is just
some subpart of works for reference of the sentences (? correct English
term?).

BTW: a CDDB interface is very helpful here in order to reduce the amount
of work.

- Martin