Music database

[David French]

I have been toying with a database to keep track of the music library of my
church.
My biggest hurdle is with the table design/relationship design of
categorizing the music.

Here is the scenario.

I have sheet music and also books that contain several individual pieces.
Each of these pieces has a title, author and lyricist (person that writes
the words). The books as well have a title and "author" or the person that
compiled the book. As well each piece of sheet music has a title, composer
and lyricist.
How do I create the tables and relationships to properly store this
information?

That is one issue.

The other is with categorizing the pieces.
One song may fit in one or many categories.
Again, how do I create the tables and relationships to properly categorize
the pieces to one OR many categories?

Please help!!
Dave French

 

[Guest]

I would make 3 tables, see below, field names below table:
talbe=song
song title
author
lyricist
book name
table=book
book name
author
table=category
song title
category
Each song would be in the song table, each book would be in the book table,
you could have multiple records per song in the category table

 

[Douglas J Steele]

You'd probably also want a 4th table that contains 1 row for each valid
category.

I'd rename your category table to songcategory, and call the 4th table
category.

 

[Lynn Trapp]

Here is the structure that I use:

tblSongs
Song_ID (PK)
Song_Title
Publication_Date
---other fields related to songs

tblComposersAndArrangers
ComposerAndArranger_ID (PK)
FName
LName
---other fields related to composers and arrangers

tblSongsBycomposersAndArrangers (This table joins the previous 2)
Song_ID
ComposerAndArranger_ID
Type ("Composer", "Lyricist","Arranger", "Both", Etc.)

tblCollections
Collection_ID (PK)
Collection_Title
Collection_Type (Book, Medley, etc.)
--other fields related to a collection of songs

tblSongCollections (This table joins the Songs table and the Collections
table)
Song_ID
Collection_ID



--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Guest]

David,

I suggest that you start with an existing database that does most of what
you need and modify it.

Northwind .mdb from MS is a good start. Modifiy the fields so that they
make sense for your application. Pay special attention to subforms in
Northwind.

David