Relationship Diagram

[chobo321321]

Hi, I'm working on a small personal database to try and get some of concepts down. I'm having some difficulty understanding data entry for the M to M relationship between the tblAlbums and tblTracks (data entry without using forms).

I can fill in the tblAlbum, and tblTrack tables separately, but I find it awkward to go back and look up the ID's from these two tables and enter them into tblAlbumTracks. Is this normal for that kind of relationship? And would a form help in elminiating that double-checking? Here's a pic of my relationship diagram.

I'm also using a "number" datatype for all my ID fields. Should I be using autonumber, or is number a better choice? Any help is appreciated, thanks.

 

[aleb]

I realy do not see a need in a tblalbumtrack unless you are trying to mix the same tracks in the different albums and tracks are independent of the album.
I am pretty much sure that this is not the case.
Make the Albumid go directly to the tbltracks as a FK.

 

[Pat Hartman]

I would use autonumbers for AlbumID and TrackID. Track# should be renamed TrackNum to get rid of the special character and it should be moved to the relation table since TrackNum could be different on each album so it is related to the union of album and track rather than to trackID as you currently have it.

Download my Many-to-Many sample to see how forms work and how the relation record is created.

 

[chobo321321]

If I use autonumber for the datatype, wouldn't that leave gaps in the autnumber sequence.

Ex:

I have records from 1 to 100, and I delete record 59, and I add a new record, I would have 1 to 101 (but I would be missing 59). Would the proper thing to do be modify the record as oppose to deleting it, or is there something else that could be done.

I also downloaded your example database, and was wondering why your tblbokings uses a "number" datatype over an autonumber in that instance? Sorry for all the questions, setting up databases is a real challenge for me, no matter how small and simple they are Thanks for the help.

 

[Pat Hartman]

I have records from 1 to 100, and I delete record 59, and I add a new record, I would have 1 to 101 (but I would be missing 59). Would the proper thing to do be modify the record as oppose to deleting it, or is there something else that could be done.

Don't get hung up on the "key" needing to contain consecutive values. The primary key only needs to be unique. Don't worry about gaps. They don't affect anything. If you want a field you can use to assign a physical location for example, add another column to the table. Increment it by 100 so as your collection grows, you can add new albums in between.

was wondering why your tblbokings uses a "number" datatype over an autonumber in that instance?

The Customer and Venue keys in the Customer and Venue tables are autonumbers. The foreign keys to Customer and Venue, which are the primary keys in the Bookings are number. It doesn't make any sense to have the Customer and Venue fields be autonumbers in the Bookings table. Even though they are part of the primary key of the Bookings table, Customer and Venue are not "defined" in the Bookings table. They are only "related" here. Remember as foreign keys they are just references to an existing primary key in another table. You can NEVER have a foreign key value that doesn't already exist in its parent table if you have properly implemented Referential Integrity.

I would change the relationship between Genre and Album to be many-to-many because you might want some albums to come up in both the rock and country lists for example.

 

[chobo321321]

Thank you so much for your help! It all makes sense to me now especially with the gaps in the autonumber. This is by far the most helpful forum I have ever been to to, thanks

**Update
I changed the tables around, and the relationship looks like this:

For both the many-to-many junction tables, I use a number datatype just like in your example. For the many-to-many genre relation if I wanted to have an album associated with more then 1 genre I just make a new record and use the same album ID, but with a different genreID? I'm also using a combo box lookup for the genreID field in tblAlbumGenre, so I know which id is what genre. If you notice anything missing please let me know, thanks.

 

[aleb]

Sorry for buttin' in .... Still don't see the need in tblabumtrack ... as far as I remember even the same song if that is a remix or remake vary on the length and the whole bunch of other details. In your case the tbltrack allows to exist some track which is not a part of any album

 

[chobo321321]

I made the relationship like that just in case I ever came across a track name that is in multiple albums.

Ex:

Album A - track name (silent night)
Album B - track name (silent night)

Since their are so many versions of the song, but most go by the same name.

 

[Pat Hartman]

I agree with aleb's point that a particular song may be different in some details on different albums. In that case, move those columns as well as the TrackNum which you moved earlier. That would leave only the title in the Track table and all the other attributes would exist in the relation table since they depend on the album as well as the song.

 

[chobo321321]

Thanks guys, I'm gonna fix that and start populating the tables, and hopefully all goes well