Newbie - Tables

[Crispy]

I am trying to teach myself a basic understanding of how to set up and use
Access (2002 version)
In doing so, I am wrestling with the best way of structuring tables for a
database of CD Albums.

The information I want to include is:

Artist Name
Album Title
Notes about the Artist
Web address of artist
Notes about the Album
Year of release
Track Names
Number of tracks
Total running time of CD
Price of CD
Condition of CD
Record label
Genre of music
A list of pointers to pictures of album covers (to avoid OLE bloat)
Other Notes (general notes on Album or artist)

My inclination is to make a separate table for each and to then work out the
relationships between the data.

Is this correct, or should it be something like

1. ALL artist related info in one table
2. ALL Album related info in another

I have read that it is preferable to split data into as many discrete tables
as possible.

Anyone have any views on what be the most efficient way to do this also with
a view to flexibility and expansion in the future?

Thanks in advance

 

[Kevin Sprinkel]

You're on the right track. The idea behind splitting the
application into discrete tables (part of
the "normalization" process) is to logically group
attributes with the thing they are describing, and avoid
redundant data, which can lead to errors.

The following should get you started.

Artists
- ArtistID (Autonumber)
- FirstName
- LastName
- Birthdate
- Website
- Notes

Labels
- LabelID (Autonumber)
- LabelName
- Website
- PhoneNumber
- Notes

Albums
- AlbumID (Autonumber)
- ArtistID (Foreign Key to Artists)
- LabelID (Foreign Key to Labels)
- Title
- Release Date
- Price
- Condition
- GenreID
- AlbumCoverLink (Hyperlink)
- Notes

Genres
- GenreID (Autonumber)
- Genre

Tracks
- TrackID (Autonumber)
- AlbumID (Foreign Key to Albums)
- TrackNumber
- TrackName
- RunningTime


Number of Tracks can easily be found from a Totals "Count
Of" query on the Tracks table. If you don't plan on adding
every track to your database, however, add a
NumberofTracks field to the Albums table.

Similarly, total CD running time can be calculated from
a "Sum Of" totals query on the RunningTime of each track,
grouped by AlbumID.

You may want to consider breaking genre down by track
rather than album, to accommodate versatile artists.

HTH
Kevin Sprinkel

 

[Evi]

There is a database in Acc97 which I presume should also be in 2002, called
Music Collection. If you don't have it, it should be free to download on the
Microsoft website.

But if you prefer your own (I do!) then I'd recommend
tblLike
LikeID
Opinion
which has records like Like, Hate, Indifferent to let you say how much you
like either an album or a recording.

TblArtist with
ArtistID FName, LName and anything you may ever want to know about an
artist.
LikeID from TblLike

TblLabel
LabelID
LabelName
to list the different record lables

TblCategory with CategoryID and Category (such as Rock, Classical, Retro)

TblRecording (which would contain RecordingID ArtistID, LabelID, CategoryID
and details about that recording like length of recording (in case you can't
be bothered to list all the individual track times and add them up), if
you'd like it, if you own it, how much it costs on Amazon. Stuff which only
applies to that recording..
It also has LikeID from TblLike


TblTracks has TrackID RecordingID TrackNum Track, TrackTime (I'd record this
in decimal format, 3.15 rather than 3:15 then use a custom function to turn
it into actual hours and minutes) Fave
(this last is a tick Yes No field to let you mark your favorite tracks on a
recording)

Each table has its own primary key field
BTW my ID names, which I have as autonumbers are only for clarification,
make yours much shorter, 5 letters at the most, or they will be really
annoying in tables and queries.

Evi

 

[Crispy]

That is VERY helpful indeed - Thanks

Does it not however, fly in the faxe of keeping all data separated in tables
of their own, and then making the relationships accordingly? Or perhaps I am
just being very thick?

Regards

 

[Crispy]

Sorry - Aother quickie - I hope -

Why would I split the ArtsitName into first and last names - Could that not
cause a problem with artsits who do not exists - eg: It would be possible to
have an artist called Norah Jones and then for someone to enter another name
that makes an artist of Harold Jones?

Probably being dim again...... )

 

[Evi]

I have a feeling that you may have some misunderstanding about keeping data
separated into table. It isn't the individual items but the groups of data
that should be separated. So, as Kevin rightly said, the Artist's name goes
in an Artist table, and so does the Artist's date of birth (if you want it).
Those things apply only to the artist, they don't apply to the recording he
made or to the types of music that are available.

Similarly, anything you want to say about the recording label goes in the
Label table.

The difficulty can come in deciding which fact goes with what.

It's not being thick but pretty normal to find this tricky. I've spent some
happy hours redesigning databases where I got that wrong.
Evi

 

[Evi]

I think I see another misconception here. You definitely WOULDN'T have one
table for first names and one table for last names. Kevin is entirely right,
split them up, not into separate tables but into separate fields.

Crispy, your name is so unusual, that I just wonder if we have 'met' before.
Did you ever work on a database about brochures for your mother?
Evi

 

[Kevin Sprinkel]

As Evi points out, we all struggled (and continue to!)
with Access starting out. It's complex, yet powerful and
versatile, and to most who hang out here, worth the effort.

The relationships are built in to the design I posted.
They are:

One Many
----------------------------- ------------------------
Artists.ArtistID Albums.ArtistID
Labels.LabelID Albums.LabelID
Genres.GenreID Albums.GenreID
Albums.AlbumID Tracks.AlbumID

In each case, the key field on the One side is a unique
identifier. There will be only one artist with a given
ArtistID, for example. But there may be many albums of a
given artistm label, or genre, and many tracks per album.
The field on the Many side is called a foreign key,
because it contains the key field from another table.

HTH
Kevin Sprinkel

Hey, when you're done, come on over and catalog my
collection! :^)

 

[Roxie Aho]

One nit with the Artist's table, First Name and Last Name
fields. How do you enter The Beatles?

I shouldn't be confusing the issue by going down this
track but:
Paul McCartney -- Beatles, Wings, solo
George Harrison -- Beatles, Travelling Wilburys (sp?), solo

Perhaps a Group table with its primary key a foreign key
in Artist?

Roxie Aho
(e-mail address removed)

 

[Kevin Sprinkel]

Not to mention one name artists, e.g., Jewel. :^)

But I think it depends on how you want to search, whether
you want a search on "Paul McCartney" to turn up Wings,
Beatles, and his solo work, or just the latter.

 

[Crispy]

Hmm!

I think I will just keep the artist name as one field! - In that way, I can
include whatever I want! - Assuming of course that in my Artsistable, I have
all artist names and then put the field as no duplicates, I can presumably
have Paul McCartney, Wings, and so on....

 

[Crispy]

When I have defined all the tables, how best to be able to put the data in a
form? - Via a query, or just by selecting from each table in the form design
wizzard?

Thanks again

 

[Kevin Sprinkel]

Developers base most of their forms on queries, but forms
for simple "lookup" tables (such as Genres) can be based
on the table itself. For such tables, I normally don't
even bother with a form; I enter the data directly in
table view.

In any case, I suggest you not worry about optimizing your
design ahead of time. You'll learn more and faster
by "just doing it", and making and learning from your
mistakes. Unlike back in the day, Access is quite
flexible for modifications later on.

So, Point, Shoot, THEN Aim!

Good luck.