Recap on ERD - foreign keys/relationships? (1 Viewer)

[benji_styler06]

Hi,

I did some access work back in college and am now trying to construct a database for which i will use the xml output. Im trying to set up my ERD.

These are the tables i have:

ARTIST, ALBUM, TRACK, RECORD LABEL, GENRE.

The attached file shows my tables set up in access. Im pretty sure it makes sense but need it checking before i start constructing the database and queries etc.

I beleive Album should be many to one artist

Track should be many to one album

Record label should be one to many albums

Many albums to one genre

I know how to add pimary keys but not sure how to add foreign keys and complete the inital structure, showing the above relationships (one-to-many etc).

Any help/advice would be much appriciated,

Cheers

Ben.

 

[Pat Hartman]

The tables are correct as they are. You already have the foreign keys in each table. All you need to do is to go back to the relationships window, double click on each relationship line and check the enforce Referential Integrity box. Access will then show you what it determines the relationships to be. You don't specify 1-m or 1-1; Jet infers the relationship based on the joined fields.

 

[benji_styler06]

Thaks for your help,

think iv got it now - however my relationship isnt how i intended, please look at the attaced screen shot. Does access determine the right relationships?

This is what i want, the parts in red are the wrong type of relationships and im unsure how to change them?

Album should be many to one artist - correct

Track should be many to one album - correct

Record label should be one to many albums - the diagram shows many record labels to one ablum?

Many albums to one genre - the diagram shows many genres to one album?

Many thanks for helping me with my learning

cheers,

Ben.

 

[Pat Hartman]

1. do not use embedded spaces or special characters in names. They cause problems with VBA.
2. Do not use the names of functions or properties as column names. Examples - Name, Date, Year - they cause confusion in VBA since it is not always clear whether you are refering to YOUR field or the VBA property or function.
3. NumberOfTracts should not be stored. I know it doesn't change but storing calculated values should be avoided.
4. The relationships between Album and Genre and Album and RecordLabel are backwards. Move Genre_ID and Label_ID to the Album table and remove Album_ID from those tables.

 

[benji_styler06]

thanks for your help, i think iv got it now - see attached.

Iv just set up a query for a form to add a new album.

its all working apart from the "album title". Its not letting me input data is this due to the ERD? The query works for adding artist name, genre and label name. Not sure why ist not working for adding the album title???

also attached is my query.

Many thanks for your help, wouldnt be able to work it out without helping hands

cheers,

Ben

 

[Pat Hartman]

You are getting further from the proper structure. Take a look at this:

tblAlbum:
AlbumID (autonumber primary key)
GenreID (foreign key to tblGenre)
RecordLabelID (foreign key to tblRecordLabel)
AlbumTitle

tblTrack:
TrackID (autonumber primary key)
AlbumID (foreign key to tblAlbum)
TrackTitle
TrackLength

tblArtist:
ArtistID (autonumber primary key)
FirstName
LastName

tblGenre:
GenreID (autonumber primary key)
Genre

tblRecordLabel:
RecordLabelID (autonumber primary key)
LabelName

tblAlbumArtist:
AlbumID (primary key fld1, foreign key to tblAlbum)
ArtistID (primary key fld2, foreign key to tblArtist)

Notice the last table - it is a junction table that relates artists to albums and allows for an album to relate to many artists and allows for artists to relate to many albums. This is how a many-to-many relationship is implemented. Right now you have AlbumID in the Artist table and ArtistID in the Album table. That is a pathological connection. You can't create a record in either table without having a record in the related table. So remove these foreign keys and replace them with the junction table.

If you want an album to relate to more than one Genre, then you need the same kind of table to implement that relationship.

 

[Pat Hartman]

Please don't start new threads on the same topic.