CD database design headaches

K

Kastor

Hello

I'm trying to put together a database for my CD collection, but some
basic design questions are giving me headaches. I mainly collect
classical music, so I have created the following tables:

tblAlbums
tblWorks
tblComposers (Bach, Beethoven, etc.)
tblLabels (Decca, EMI, Philips, etc.)
tblCategories (piano, opera, chamber music etc.)

tblAlbums has the following fields: album title, artists, label (lookup
from tblLabels) etc.
tblWorks has the fields: composer (lookup from tblComposers) work
title, category (lookup from tblCategories) and album.

There is a one-to-many relationship between tblAlbums.ID (=primary key)
and tblWorks.Album because every CD album usually contains several
works. Now here's my first headache: while virtually all CDs contain
several works, the same work may also be present on several CDs. If
this is the case, I currently have to make double entries in tblWorks.
How can I avoid this? And how would I best design my forms?

Second headache: only a minority of my CD's have only one or two
artists; most have several. For instance, an opera album may have up to
fifteen singers, plus orchestra, chorus and conductor. Because it is
impossible to make a field for every artist, I have only one (memo)
field to store all artists. However, this means I have to type some
names over and over again, because I can't use a lookup field.

Any help will be greatly appreciated.
 
K

Kastor

Thank you, but this simple template doesn't address any of the design
issues I mentioned.
 
G

Garret

Kastor said:
Hello

I'm trying to put together a database for my CD collection, but some
basic design questions are giving me headaches. I mainly collect
classical music, so I have created the following tables:

tblAlbums
tblWorks
tblComposers (Bach, Beethoven, etc.)
tblLabels (Decca, EMI, Philips, etc.)
tblCategories (piano, opera, chamber music etc.)

tblAlbums has the following fields: album title, artists, label (lookup
from tblLabels) etc.
tblWorks has the fields: composer (lookup from tblComposers) work
title, category (lookup from tblCategories) and album.

There is a one-to-many relationship between tblAlbums.ID (=primary key)
and tblWorks.Album because every CD album usually contains several
works. Now here's my first headache: while virtually all CDs contain
several works, the same work may also be present on several CDs. If
this is the case, I currently have to make double entries in tblWorks.
How can I avoid this? And how would I best design my forms?
Click to expand...

What you want is to have tblAlbums have the field Works, and have that
field have a relationship with tblWorks through the field, Works, in
that table. I recommend NOT to use lookup tables. Instead, have a
combobox on a form with the values limited to the values in tblWorks
instead - so any value can go into the field, but it is limited through
the combobox instead of through the field.
Second headache: only a minority of my CD's have only one or two
artists; most have several. For instance, an opera album may have up to
fifteen singers, plus orchestra, chorus and conductor. Because it is
impossible to make a field for every artist, I have only one (memo)
field to store all artists. However, this means I have to type some
names over and over again, because I can't use a lookup field.
Click to expand...

You dont need a field for each artist. Just have a table called
"tblArtists" and there you can list all the Artists, and have that
table have a relationship with the field "Artists" in the tblAlbums.
 
D

Douglas J Steele

Kastor said:
There is a one-to-many relationship between tblAlbums.ID (=primary key)
and tblWorks.Album because every CD album usually contains several
works. Now here's my first headache: while virtually all CDs contain
several works, the same work may also be present on several CDs. If
this is the case, I currently have to make double entries in tblWorks.
How can I avoid this? And how would I best design my forms?
Click to expand...

It sounds more like you've got a many-to-many relationship then, not a
one-to-many. To resolve many-to-many relationships, you introduce a new
"resolution" table that holds the Album Id and the Works Id. Typically,
you'd use a form/subform arrangement to allow you to update this new table.
Second headache: only a minority of my CD's have only one or two
artists; most have several. For instance, an opera album may have up to
fifteen singers, plus orchestra, chorus and conductor. Because it is
impossible to make a field for every artist, I have only one (memo)
field to store all artists. However, this means I have to type some
names over and over again, because I can't use a lookup field.
Click to expand...

Same deal here: because there's a many-to-many relationship between artists
and CDs, you need a resolution table that holds the CD Id and the Artist Id.
 
D

Douglas J Steele

Garret said:
What you want is to have tblAlbums have the field Works, and have that
field have a relationship with tblWorks through the field, Works, in
that table. I recommend NOT to use lookup tables. Instead, have a
combobox on a form with the values limited to the values in tblWorks
instead - so any value can go into the field, but it is limited through
the combobox instead of through the field.
Click to expand...

Uh uh. See what I replied elsewhere in this thread.

Putting a field for the Works Id in tblAlbums would mean that you could have
most one Work per Album
 
G

Garret

Douglas said:
Uh uh. See what I replied elsewhere in this thread.

Putting a field for the Works Id in tblAlbums would mean that you could have
most one Work per Album
Click to expand...

I was wrong, but if you had tblAlbums as the parent table, and Works as
the child table, then you could have multiple Works per Album. You
link the tables by "AlbumName" field. But then if you wanted a Work to
belong to multiple Albums, I think you would have to have a WorkID as
the primary key instead of "WorkName" which could be messy. Doug's way
is probably better.
 
K

Kastor

OK, thanks. I'm going to try using a joining table. Should I enable
"cascade update" and "cascade delete"?
 
D

Douglas J Steele

I've yet to see a real need for Cascade Update: I make it a rule to avoid
changing primary keys as much as possible, and if it's unavoidable (which
should be never...), I prefer deleting or expiring the existing row and
inserting a new row with the correct values.

Cascade Delete, on the other hand, probably makes sense. If you lose a CD
(or have to throw one out due to damage, say), deleting the entry from the
Albums table would automatically delete it from the new table.
 
K

Kastor

OK, here's what I've done. I've created a separate tblArtists, with the
fields ID (primary key) and Name. I haven't made separate first name
and last name fields, because I may have several artists with the same
surname, but at the same time I don't want duplicates. It's the
combination of first and last name that should be unique, not the first
and last names separately. So I made one name field set to "indexed, no
duplicates".

Then I've created the junction table tblAlbumArtists, which contains
AlbumID and ArtistID, and I have established one-to-many relationships
(with referential integrity) between these fields and the corresponding
ID fields in tblArtists and tblAlbums.

After that, I've made a simple query, qryAlbumArtists, with
tblAlbum.Title, tblAlbumArtist.ArtistID and tblArtist.Name. On the
basis of this query, I've then generated a form using the form wizard.
This form consists of a parent form (columnar) with the album title and
a child form (datasheet) with the ArtistID and the artist name. This
allowed me to add as many artists to an album as I want, but
unfortunately there was no way to see if an artist already existed. I
have tried to solve this by changing the AlbumID texbox to a combobox
and changing the RowSource to include the artist name, so I can select
existing artists from the list. Because tblArtist.Name has "indexed, no
duplicates", there is no danger that I'll accidentally make a new
record for an artist that already exists.

This works, but it's not very user-friendly. I don't like the way the
subform works. Is there a better way?
 
D

Douglas J Steele

What's not user friendly about it? What would you rather do instead?

BTW, there was no reason not to separate first and last name. You can create
an "indexed, no duplicates" index that includes up to 10 fields, so that the
combination of fields must be unique, but the values of individual fields
need not.
 
K

Kastor

Well, it's fine as long as I'm the only one using this database, but
I'd prefer not to work with ID numbers on a form. Ideally, you'd just
select the names from a list.

I was hoping there was a way to put an "index, no duplicates" on
multiple fields, that's why I mentioned that. I'll check the Access
help file.
 
D

Douglas J. Steele

I don't understand.

The point of a combobox is that you can display one thing, but have another
thing used as the value. For example, base your combobox on a query that
returns ArtistId and ArtistName, in that order, sorted by ArtistName. Set
the column widths to 0";2" (for instance), and the Bound Column to 1. You'll
only see the ArtistName when you look in the combobox, but once you select a
particular Artist, the ArtistId associated with the artist is what will be
stored in the table to which the box is bound.
 
Top