Media Relationships

[deniedaccess]

Hi, I hope this is in the right section.



I've just started using Access 2010 and I'd like to create a media database for all my movies, TV, and music. Now I just need to work out the best way to link relationships between 3 tables.

movieid (primary) Title Format Year Notes
tvid (primary) Title Format Year Season Episodes Notes
albumid (primary) Artist Album Tracks Format Year Notes

I've never done this before so can someone help out please?

[CJ_London]

have you worked out what the relationship is between say an album and a movie?

[deniedaccess]

I guess it would be something like the year or format?

What I'd like is if I use a query on say title, it would display both Movies and TV entries etc.

[CJ_London]

In that case, I would have one table for all three categories with an additional column for mediatype (movie, tv, album). I would also add a further column MediaPK which would be an autonumber to uniquely identify the record - this will normalise your data. OK, I agree a movie does not use season, episodes or tracks, but they can be left blank. The benefit is ease of use.

For example you might decide that you want to also store artiste details (e.g. consider your album table) - they may have been in a movie and tv and perhaps made an album.

To do this you would have two tables

tblArtistes
ArtistePK
ArtisteName
ArtisteDOB
...
...


tblBeenIn
BeenInPK
MediaFK
ArtisteFK

this latter table is a link table so you can see for any movie, tv or album, which artistes were involved. Conversely you can also see for any artiste which movie, tv, album they were involved with.

If you keep your three separate tables, you will have to do this query three times (once for each media type) and then union them together.

With your current setup and to find a name your union query would be something like

SELECT title, format, year
FROM tblMovies
WHERE title=[Enter Title]
UNION SELECT title, format, year
FROM tblTV
WHERE title=[Enter Title]
UNION SELECT Album, format, year
FROM tblAlbums
WHERE Album=[Enter Title]

Note that title, format and year are the only two fields you could bring though because they are the only ones that appear in each table

Note also that format and year are reserved words (they are the names of functions) so recommend you change them to something else like mediaformat, releasedate

[deniedaccess]

Okay thanks for your help Ajax.