Okay, here's my goal. I have a client who wants a database to manage singing gigs, including his songbook, show set lists, venues & contacts, and key signatures of songs. I've outlined below the tables I I created and the end goal for layouts. Parts of this one have me stumped.
1. Songs-this includes song title, singer name, starting key, ending key, duration, genre and tempo
2. Venues-this includes the venue name, address, contact person, phone, etc.
3. KeySigs-this is a list of key signatures which also has a container field for a picture of the actual key signature
(i.e., flats and sharps on a staff).
4. Gigs-I think this is the crux of the database. There are fields for date, start/stop times. I have a VenueID field here and in the Venue table to pull the content from Venues into a Gigs layout. There are also fields for Set (usually 1-3) and Slot (where in a set a particular song will fall). The goal is to create a Gig, select the Venue and populate/pull fields from Venues, then enter a song ID and assign the Set/Slot for each song.
The Songs table should only have one instance of each song so that this serves as an overall library or songbook for the singer. A given song would generally only be performed once per gig, but not necessarily. The KeySig would provide the ability to "look at" the list for consistency and harmonics on a given set list.
Ultimately Gigs is the hub of activity. On this layout, the date, Start/End times, VenueID, SongID, Set and Slot are the only fields in the table. I've successfully pulled info from the Venues table based on the VenueID (contact, phone, address, etc.). Creating the setlists using SongID and making Set/Slot assignments is where I'm stuck... I created a portal for this, but can't get the fields to populate based on SongID.
My thought was that the Gigs table would contain SongID Set & Slot to create set lists, and all the other information would "display" from their respective tables.
Is my thinking amiss? I appreciate feedback of any kind (except beating me up for taking on something this convoluted). LOL