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.

 

[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.

 

[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.

 

[chobo321321]

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