Linking tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Im fairly new to access and have been set the task of creating a database
using access. The chosen topic is a DVD collection and there must be a
minimum of 6 tables. This is what i have come up with so far:
Tables-
DVD-------------Name of Film, Length, DVD Code, Price, Release date
Disc Info--------No of Discs, Extra features, Format,
Actors-----------Name, Age, Sex, Nationality,
Director---------Name, Age, Sex, Nationality,
Film Company--Name, Location,
Content --------Theme, Language, sex/nudity, Rating, Violence

The problem comes when i have to think about how the tables relate to one
another, to me it looks like all the tables will have a relationship with the
dvd table(im not to sure about the one-to-many etc relationships) am i on the
right track? i would appreciate any help.
 
The way you have it, it looks like the tables do not really relate. However,
if you add a few new fields to the DVD table, you should be able to link them
all. First off, you can add "Director" to the DVD table. This will help you
link it to the Director table by relating "Director" to "Name." If you add
"Film Company" as a field to DVD, you can also relate that to the "Film
Company" table.

If you add "Movie title" to the "Disc info" table, you could link that to
the DVD table. Where you run into trouble is with Actors. One actor may be in
several of the movies in your table, but one movie obviously has several
actors. I am sure there is a way to do this, I personally don't yet know how.
I hope somebody else can help you with that better than I can.

Also, content will be difficult unless each entry is referring to a specific
movie. If that is the case, you can add "Movie title" to the content section
too.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy
 
I spoke too soon. I suddenly remember, I think you can do a many-to-many
relationship by creating another table containing the primary keys from the
two tables that otherwise wouldn't relate. Can be a lot of work, though.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy
 
Thanks a lot for your help i can see what you are saying, would it make sence
to add an actor id and director id as primary keys in their tables so that i
can link them in to the dvd table?
 
Sure, that would work. You could give the actors and directors ID numbers
which you could then use to link to the DVD table. If you wanted to put
several actors for each movie, you'd have to have several fields. Actor 1,
Actor 2, Actor 3, something like that. Similar for the actor themselves. If
you were going to list movies in there table, you would have to list Movie 1,
Movie 2, Movie 3, etc.

Actually, though, it may benefit you more to choose one or the other. In
other words, only have the movie list several actors, or only have the actors
list several movies. Otherwise, it might be a bit redundant. If you only do
one, you can use reports or queries to show it either way. For example, if
you choose to list several actors for each movie, but then not bother to list
any movies in the actor table, you could still do a querie to list each
actor's name, then all the movies to which they are linked. This saves you
from typing in a lot of redundant information.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy
 
ok thank you that has been very helpful.

Paul (ESI) said:
Sure, that would work. You could give the actors and directors ID numbers
which you could then use to link to the DVD table. If you wanted to put
several actors for each movie, you'd have to have several fields. Actor 1,
Actor 2, Actor 3, something like that. Similar for the actor themselves. If
you were going to list movies in there table, you would have to list Movie 1,
Movie 2, Movie 3, etc.

Actually, though, it may benefit you more to choose one or the other. In
other words, only have the movie list several actors, or only have the actors
list several movies. Otherwise, it might be a bit redundant. If you only do
one, you can use reports or queries to show it either way. For example, if
you choose to list several actors for each movie, but then not bother to list
any movies in the actor table, you could still do a querie to list each
actor's name, then all the movies to which they are linked. This saves you
from typing in a lot of redundant information.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy
Click to expand...
 
Glad I could help. Hope that works for you! Good luck, and never hesitate to
come back to these boards if you need help. I do all the time. I don't know
where I would be without these kind, intelligent ladies and gentlemen. I'd
never have learned all the things I have about Access.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy
 
Back
Top