Music Database (Albums in MP3 format on DVD): Do I Need Junction Tables?

[tatihulot]

Hello everyone, I am using Access 2013. I want to create a simple Digital Music Album inventory database, but I'm not 100% sure if it is a one-to-many relationship or many-to-many.


I'm not sure if I need to create junction tables for this, so please let me explain what I want to do.

I have many albums in MP3 format, and want to back them up onto DVD. Here's an example of a DVD (I'm making up some album title names just for example's sake):

DVD Number: 1
Artist: Beethoven, Ludwig Van
Album Titles for Beethoven, Ludwig Van:
Beethoven's Greatest Hits
Best of Ludwig Van Beethoven
Golden Hits of Luddie V
Beethoven Comes Alive
Artist: Goodman, Benny
Album Titles for Goodman, Benny:
1938 Carnegie Hall Jazz Concert
Goodman Swings Again
Jazz Holiday

I'm assuming it's a one-to-many relationship because I have one artist with many album titles, so a junction table isn't necessary. Am I correct? There may be more than one artist's albums on the DVD, but the artists do not intermingle, so to speak.

My rough draft of the tables looks like this:

Table 1:
Artist
DVD Number ID

Table 2:
Album Title
DVD Number ID

The relationship is joined via DVD Number ID.
Am I doing this right? If not, what do I need to change?
Thank you for your assistance. Oh, and sorry about the fake album titles. I mean, come on…. Golden Hits of Luddie V?

T. Hulot

[tatihulot]

I should add more information to this thread. When I create the database, I will want at least one subform in it. The main form will contain the name of the artist. It will also contain the DVD Number.
The subform should contain the album titles on that DVD for that particular artist.

I'm still in the process of deciding if this is the most practical way of doing this inventory database. Right now, I am not sure. Thanks again. TH

[ssanfu]

If that is all you want to see, looks good. I would suggest do not use the Primary key field for DVD number.

Consider:

tblArtists
---------
ArtistID_PK Autonumber (Primary Key)
DVDNumber Long
ArtistName Text

tblAlbums
---------
AlbumID_PK Autonumber (Primary Key)
ArtistID_FK Long (foreign key to ArtistID_PK)
AlbumName Text


Suggestion for main form/subform:
A query based on the table tblArtists as the record source for the main form. The detail section should be in continuous forms view.
Place the Album subform (with query for recordsource) in the FOOTER of the main form.

[tatihulot]

Thank you for replying and for your assistance. Not 100% sure what a foreign key is. I'll work on it this weekend and will let you know how it turns out. Thanks again.

[ssanfu]

It is how the tables are related. Primary key -> Foreign key.

Here are a couple of tutorials http://www.rogersaccesslibrary.com/forum/forum46.html
Working through them should also help


Maybe these will help
Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers


Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp


Use Autonumbers properly
http://access.mvps.org/access/general/gen0025.htm

[tatihulot]

Quick follow-up question: In your example of tables above, you use the word LONG for DVD Number, and for ArtistID_FK Long (foreign key to ArtistID_PK).

Am I correct in assuming you mean the data type is Number/Long Integer, and not Long Text?

I'm still confused about foreign keys. My initial rough draft of the database isn't working. I didn't expect it to, though. I'm a novice database person, and the last relational database I created was a few years ago, with a LOT of trial and error.

[tatihulot]

I found this on a web site. I will try using the Lookup Wizard to create a foreign key, and see what happens.

Foreign keys

A foreign key, simply stated, is another table's primary key. The values in a foreign key field match values in the primary key, indicating that the two records are related — for example, a customer and an order that she has placed. Unlike primary keys:

• A table can have more than one foreign key.
  
• A foreign key does not necessarily have unique values.
  
• A foreign key cannot reliably identify a particular record. For example, you cannot always tell which record you are viewing from the Orders table by looking at the Customer ID.

You create a foreign key when you use the Lookup Wizard to create a field.

[tatihulot]

Good news. I think I got the database working. Let me enter some data and tweak the interface and I'll let you know how it turned out. The Lookup Wizard seemed to do the trick, as far as creating a foreign key was concerned. I really and sincerely appreciate your help.

[ssanfu]

You create a foreign key when you use the Lookup Wizard to create a field.

If you used the lookup wizard in a table, see

The Evils of Lookup Fields in Tables
http://access.mvps.org/access/lookupfields.htm

I never use a lookup field in a table. (lookup tables are a different animal).


Also see The Ten Commandments of Access
http://access.mvps.org/access/tencommandments.htm

Am I correct in assuming you mean the data type is Number/Long Integer, and not Long Text?

Yes a "Long" is a Long Integer.


ArtistID_PK Autonumber (Primary Key) (1)---------------(many) ArtistID_FK Long Integer (foreign key to ArtistID_PK)
If One artist can have many albums
and One album has one artist,
then this is a one to many relationship.

For example, if Elvis has a ArtistID_PK = 1 and he has 5 albums, then in the Album table there would be 5 records

tblAlbums
--------------

AlbumID_PK	ArtistID_FK	AlbumName
1	1	Album 1
2	1	Album 2
3	1	Album 3
4	1	Album 4
5	1	Album 5

Does this help?

[tatihulot]

Thanks for replying again. I understand the basic differences between one-to-many and many-to-many. The information about why I shouldn't use lookup fields in a table was over my head. What should I have done, instead?

[ssanfu]

Sorry, I should have worded my answer differently.

Lookup fields in tables are considered Evil (bad design). However, since the dB is for your personal use, you can design the table however you want.
Since you probably will not move this dB to SQL Server, MySQL, etc. , the lookup field won't matter.


I only use forms to enter/edit data, so I use combo boxes on a form. Forms give me more control on validation of the data. If you feel like you want to open a table, query or form in datasheet view to add data, the lookup field would work.

The information about why I shouldn't use lookup fields in a table was over my head. What should I have done, instead?

It is mostly that a lookup field hides the true value of the field. Lets say the field in the table is related to a name field in a different table. Using a lookup field, the value stored might be 10, but you would see "ABC Corp."
If you tried to search the table on "ABC Corp.", no records would be returned because the actual data is 10, not "ABC Corp.".


Good luck with your project... keep asking questions

[tatihulot]

Thank you again for replying. Yes, this database is for my personal use, and will not be on a server or anything like that. As of this writing, the essential database design appears to be working. I'm just adding some ancillary things, such as a time stamp, and some buttons, such as Add New Record, Go to Last Record, Total Count, and things like that. I know how to create those buttons.

I have encountered a minor formatting problem, and have posted a question about it in the general Access category on this forum. Perhaps it's a question you'll know the answer to. Thank you again for your assistance. TH

[tatihulot]

Just a follow-up note here...I have realized my database design for this project is wrong. I need to redo the whole thing, and I have a feeling you understand why. It took a week to realize it, but I am focused on the wrong data. I was making the DVD's the primary data, and it has to be the artist, instead.

It would be much more practical if all of an artist's albums are related to the artist. The original design didn't have it that way. I now realize that isn't a good idea.

What I am going to do now is this: The main form will have a drop-down menu with the artists...either a combo box or a list box. I'm not sure which I'll use yet.

The subform will have a list of all the albums by that artist, that I've entered into the database. The DVD number will be adjacent to the album title.

I am assuming I can create a report based on DVD Number query, should I want that.

Right now, the challenge I face is figuring out how to get a drop-down menu to interact with a continuous subform. I've never done that before. But at least I know how to establish the one-to-many relationship. At least, I hope the procedure works in this updated database, as well.

I'm sure I'm not the only novice who looked at his/her database, declared it "finished," only to decide to scrap it and start all over again. So, with that being said, I thank you for your help, and I hope what I've learned from you and everyone else on this board will help me make the new database with a minimum of fuss. I'm going to dabble with it for a while...trial and error. Thanks again.