ACCESS DVD Database

That makes sense. That way, my purchase info would be a second table,
and the actors a third, and the director table another as some films do
have more than one, and each director and actor are associated (usually)
with more than one film/title as it were.

Well, the purchase info should ONLY go in a 2nd table if there going to
be repeating data, or more then on "set" of purchase information. If each
dvd only have non repeating fields (purchase date, purchase amount etc),
then that belongs in the main table. There is ZERO reason to create
and maintain anther table for fields that simply attached to the DVD
and are not repeating values. So, as far as I can tell/guess based
on your information, those fields should be in the main table.

So I should be able to do this with only about three or four tables,
one of which will be huge, and the others relatively small.

Yes, that sounds good to me....

Just keep in mind the two cases we have:

tblDVD will have a field (long number) that lest you enter the directors id.
This is not a one to many relational, but a simply field that looks up the
director value.

The 2nd case is the "many" actors. This is repeating data that belongs to
one record (is a classic one to many relationship).

So far with your given design, we have 3 tables......


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

soory,...I mean 4 tables.....

On Mon, 28 May 2007 19:40:13 -0600, "Albert D. Kallal"
wrote:

That makes sense. That way, my purchase info would be a second table,
and the actors a third, and the director table another as some films do
have more than one, and each director and actor are associated (usually)
with more than one film/title as it were.

Well, the purchase info should ONLY go in a 2nd table if there going to
be repeating data, or more then on "set" of purchase information. If each
dvd only have non repeating fields (purchase date, purchase amount etc),
then that belongs in the main table. There is ZERO reason to create
and maintain anther table for fields that simply attached to the DVD
and are not repeating values. So, as far as I can tell/guess based
on your information, those fields should be in the main table.

OK. I used Excel to copy a sheet with only my purchase info. I made a
separate table with it in Access, but I suppose I will import it into
fields I generate in my main table, because, as you say, it won't be
repeating data on a per title/ID basis, and has no need to be separate.

So I should be able to do this with only about three or four tables,
one of which will be huge, and the others relatively small.

Yes, that sounds good to me....

My actors table is an index table that lists all actors' unique IDs and
DVD title IDs for any given title, and will have many repeated IDs

And a second table that has only the actors' IDs and their names.

Same for the director's table, as there are "some" directors, but many
movies. 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. I thought that was
one of the ideas behind tables and relationships was to keep the whole
database size down by not repeating data.

Just keep in mind the two cases we have:

tblDVD will have a field (long number) that lest you enter the directors id.
This is not a one to many relational, but a simply field that looks up the
director value.

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.

The 2nd case is the "many" actors. This is repeating data that belongs to
one record (is a classic one to many relationship).

Yes, classic is the right term. :-] I also did that one as an index
table with all the title ID's and all the actor ID's, then another table
with the actor names for each actor ID. The form for viewing it would do
the O-T-M query on a given title ID, and grab those names associated with
all the actor ID's in the index table. That's sounds like the right way
to approach it.


So far with your given design, we have 3 tables......

I have the main table, the two indexes of O-T-M components (directors
and actors), 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. It has already
jumped from 31MB to 78MB, but the access is surprisingly faster, as well
as the saves. 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. I was easily able
to build a form for the main table that lets me look at one record at a
time for it (split) with the raw table below. That one is pretty
vulnerable to data corruption (the odd keypress)though when being
"viewed".

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.

Thanks for your help and suggestions. Turns out, I likely know more
than I gave myself credit for, it was just a matter of getting used to
the features and menus in the access design realm.

Same for the director's table, as there are "some" directors, but many
movies.

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/AlbertKal...icles/Grid.htm

And, here is some additnal informaton on sub-forms:

http://www.members.shaw.ca/AlbertKal...000000005.html


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

On Mon, 28 May 2007 23:03:10 -0600, "Albert D. Kallal"
wrote:

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

I've been watching this thread with interest, Albert - let me ask you what you
think of an idea I've had.

Not only might some (small?) fraction of movies have multiple directors; but
perhaps you might want to keep track of other people, neither actors nor
directors. Producers, costumers, set designers, key grips and best boys...
well, this could get silly... but might another modification involve

tblPeople
PersonID
LastName
FirstName
other bio data

tblPeopleInvolved
DVD_ID
PersonID
RoleID

tblRoles
RoleID
Role e.g. Leading Actor, Director, Associate Producer, ...

Just throwing out the idea for consideration!

John W. Vinson [MVP]

"John W. Vinson" wrote in message

Not only might some (small?) fraction of movies have multiple directors;
but
perhaps you might want to keep track of other people, neither actors nor
directors. Producers, costumers, set designers, key grips and best boys...
well, this could get silly... but might another modification involve

tblPeople
PersonID
LastName
FirstName
other bio data

tblPeopleInvolved
DVD_ID
PersonID
RoleID

tblRoles
RoleID
Role e.g. Leading Actor, Director, Associate Producer, ...

Just throwing out the idea for consideration!

That is very *slick* approach. I like it because it is SIMPLE and nice.

We can add writers, distributors....what ever you want, and we really kept
the number of tables down to a min.

About the only "minor" downfall is that our sub-form will have different
things mixed in it, and it bit easier for user to enter data of ONE type
into a sub-form (eg: actors). However, that quite minor, and having the
"role" combo box default to actors would solve that anyway! So, try as I may
to find a real downside..the upsides to this approach are really nice...

For the report, you can still could have a "separate" list of actors, and
another to list everything else (two sub-reports based on the same people
table..but one would be filtered to actors).

Simple and flexible at the same time wins everyday!

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

On Mon, 28 May 2007 23:29:55 -0600, John W. Vinson
wrote:

On Mon, 28 May 2007 23:03:10 -0600, "Albert D. Kallal"
wrote:

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

I've been watching this thread with interest, Albert - let me ask you what you
think of an idea I've had.

Not only might some (small?) fraction of movies have multiple directors; but
perhaps you might want to keep track of other people, neither actors nor
directors. Producers, costumers, set designers, key grips and best boys...
well, this could get silly... but might another modification involve

tblPeople
PersonID
LastName
FirstName
other bio data

tblPeopleInvolved
DVD_ID
PersonID
RoleID

tblRoles
RoleID
Role e.g. Leading Actor, Director, Associate Producer, ...

Just throwing out the idea for consideration!

John W. Vinson [MVP]


To quote the German guy from Laugh In...

Vedy Interesting... :-]

On Mon, 28 May 2007 23:03:10 -0600, "Albert D. Kallal"
wrote:

You need to do a compact and repair after doing any major file operations.

No. It is still 78 MB after compacting.

The main data was 31, the actors were 10MB more, and the directors were
over 1 MB.

That still doesn't add up though.

Oh well. Databases and their hidden indexes, etc. must take up more
space than raw data in a spreadsheet.

It still opens and saves a lot faster than a spreadsheet though.

On Mon, 28 May 2007 23:29:55 -0600, John W. Vinson
wrote:

Not only might some (small?) fraction of movies have multiple directors; but
perhaps you might want to keep track of other people, neither actors nor
directors. Producers, costumers, set designers, key grips and best boys...
well, this could get silly... but might another modification involve


Yes, and writers, directors of photography, foley editors, musical
score writers, and awards table.

I am starting to get you guys' lingo down a bit too.

So,

tblDVD_Main
DVD_ID
DVD_Title
UPC_Code
Studio
Release_Date
Status
Sound
Released_Versions
MSRP_Price
MPAA_Rating
Theatrical_Release_Date/Year
Genre
Disc_Aspect
Timestamp
Where_Purchased
Purchase_Price
Purchase_Date
Cover_Image
Disc_Count

tblPeople
Party_ID
Name

tblRoles
Role_ID
Role

tblDVD_Roles
DVD_ID
Role_ID
Party_ID

This would condense all other parties involved into one table. Their
role could differ from film to film as well

Then an awards table for the various factions that give such things out
as some films garner several awards as well as there being several
different types of award given.

tblAwards
Award_ID
Award_Name

tblDVD_Awards
DVD_ID
Award_ID

Maybe even a table for the extras included (which means we need a field
for number of discs in the main table, like Disc_Count), as in deleted
scenes, director or actor commentaries, storyboards, etc.

tblExtras
Extra_ID
Extra_Description

tblDVD_Extras
DVD_ID
Extra_ID

This could end up a fairly deep database. I LIKE IT! :-]

"Steve" wrote in message
hlink.net...

BTW, relook at Mr. Marshall's two responses and try and find any bit of
help regarding you creating a database that duplicates your Excel
workbook. There is none. He is only good for spewing venom. He's not an
Access MVP take note; he's a disgrace to the standards of MVP.

Well Steve, now you've done it. John Marshall is a Visio MVP who's skills in
Access are every bit as sharp as many an Access MVP. He has been an MVP
quite a long time, and is very active within the private Access MVP groups
at our meetings. You do know, I hope, that Visio is a tool which can be used
for ERDs and database flow charts. It is programmable with VBA and can be
used to build databases.

Please do not defame someone who's Access skills quite probably exceed your
own, and who's standing as an MVP is impeccable. It does you no favor. You
have been chastised innumerable times in both UseNet and Microsoft forums
for trying to profit from a free resource. The only permitted advertising in
all newsgroups is up to 5 lines in a sig. You are welcome to offer free help
at any time. Anything else is not permissible.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com