Same for the director's table, as there are "some" directors, but many
Well, it better look at the above the other way around. You have a movie,
and
you need to set who the director is. However, you are correct, if you
reverse
the viewpoint, then it true, a director will have many movies. but, we don't
have to create a special table or anything because we can place that
director
name in the tblDVD. On the other hand, if you design is to allow MORE THEN
one director for a DVD move, then we would have to create a new table to
hold that 'many' values. (eg: tblDirectorsForAMove).
That is why I drew:
tblDVD ---> tableDirctors
<--- tblActorsInMovie--->tblActors
we have
one ----> to many
On the "one" side, you only setting a simple id field, and not repeating
records.
So, sure, while directors will have many movies, we can simple store the
director
id in each tblDVD.
However, since there is typically only one director per movie,
they could be in the main table. It just seems that the database would
be a lot larger though with all those repeated names.
Your not repeating the director names and information over and over. The
ONLY thing
you going to store in tblDVD is the director ID. For reports, or display the
name
and birthdates of the director in a report (or even on the main dvd form),
you will
use the relational abilities of ms-access to pull that information from the
other table.
Fact is, you only EVER have one copy
of a directors name in the database. If you modify or correct the spelling
of that
director's name, then all reports, forms and everywhere will reflect this
new updated
name because only ONE copy of the director name ever exists in the system
(remember, I assuming our design is for only ONE director for a movie).
I thought that was
one of the ideas behind tables and relationships was to keep the whole
database size down by not repeating data.
hum, you lost me here now. What data you are talking about that we need to
repeat?
The director is looked up by way of the DVD title ID, and the
director's index table, which is director ID's and DVD Title IDs only.
I don't see the need for the above. You just need a plain Jane table of
directors. You enter their names, birthday, whatever else you have. Each
director record would have a primay key of id. It is this "id" number
that you store in the main tblDVD record (in a field called director_id
for example)
that all you need to accomplish the above.
I have the main table, the two indexes of O-T-M components (directors
and actors)
As mentioned, you don't need two indexes since we store the director ID
in the main table. If you only allowed ONE actor per movie, then the same
would be done for the actor_id. However, since we need "many" actors for
a dvd, then we created that tblActorsForDvd. Since you older data is
perhaps not in the exact same format, then you might have to run some
quires or even some code to re-format the data to your needs.
and the tables that relate those director and actor IDs to
actual names. Then I have the separate Purchasing table, which it
appears I can and should merge with the main database.
As long as the purchasing information is not repeating data for a given
DVD, then yes. If there is more then one set of purchasing information
for a given DVD..then you need a separate table.
It has already
jumped from 31MB to 78MB, but the access is surprisingly faster, as well
as the saves.
You need to do a compact and repair after doing any major file operations.
And, if you been editing and testing forms for a hour or so..then again you
need to compact. (you will get the feel for file increase size and when to
compact -- but, when developing...you do a compact a lot).
I have made the relationships between the main table and
the actor and director tables. I need to create a form that lets me look
at one title, and see all the linked data for it. So I guess I need to
construct a couple of queries, and then build a form.
No, you don't need to build quires that joins the data. the form + sub-form
models the relations for you.
I was easily able
to build a form for the main table that lets me look at one record at a
time for it.
My main view form will be a read only form, unless I can assign edit
functions to specific fields on a per field basis, like the purchase
info.
I would place the purchase information on anther tab control on the form.
Tab
controls lets you group information on a form.
So, do NOT build query's with joined data for your forms.
You build a main form for the tblDVD.
To display he "many" records for actors, you will crate another form (a
continues form) that displays the actors. This continues form will then be
dropped into the above main form. This allows you to display and edit a
one-to many records. And if you don't want to crate the forms separate, then
use the forms wizard to insert a sub-form. it will do all the dirty work for
you.
Here is some screen shots of forms + sub-forms....
http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm
And, here is some additnal informaton on sub-forms:
http://www.members.shaw.ca/AlbertKallal/Articles/fog0000000005.html