Rational Tables?

[Scooper2241]

I am starting a data base from scrach for a library of video tapes. The way
my company wants them set up is in categories. I have created tables for each
of the differnt categories and assigned fields (which are all more less the
same for all the tables). What I want to acheve frome this is a way to
search for example a speekers name and get a list of all the videos that
person sopke in by combining all the differnt tables. What is the best way
to do this?

 

[Dirk Goldgar]

I am starting a data base from scrach for a library of video tapes.
The way my company wants them set up is in categories. I have created
tables for each of the differnt categories and assigned fields (which
are all more less the same for all the tables). What I want to
acheve frome this is a way to search for example a speekers name and
get a list of all the videos that person sopke in by combining all
the differnt tables. What is the best way to do this?

A different way from the one you've started on, I'm afraid. You should
*not* have a separate table for each category. Instead, you should have
one table that lists all the categories, one record for each category,
and assigns each category an identifying value to serve as its primary
key. That could be the category name itself, but that's a bit
cumbersome, or a shorter category code that you type in, or an
autonumber ID that will be generated by Access automatically for each
record you add to this table.

Then you would have a single table for all the videotapes. If each tape
can fall into only a single category, you'd have a Category field in
this table, and for each tape you enter, you'd choose the key field from
the list of categories stored in the Categories table.

If, on the other hand, a videotape could belong to more than one
category, you wouldn't put a Category field in this table. Instead,
you'd have a third table to represent this many-to-many relationship.
This third table would store both the key field from the VideoTapes
table and the key field from the Categories table, so that the presence
of a record in this table would mean that this videotape falls into this
category.

Since you'll only have one table for all the videotapes, running a query
to find all the tapes with a particular speaker is now simple; that is,
unless there could be multiple speakers per videotape. If that could be
the case, then you'd use a simlar three-table structure for that
many-to-many relationship, too, and the query would have to involve a
join of those tables. Still, there'd be no need to search multiple
table, which is what you really want to avoid.

 

[Scooper2241]

Thank you Dirk for the help but i still have some questions.
You wrote:
A different way from the one you've started on, I'm afraid. You should
*not* have a separate table for each category. Instead, you should have
one table that lists all the categories, one RECORD for each category,
and assigns each category an identifying value to serve as its primary
key. That could be the category name itself, but that's a bit
cumbersome, or a shorter category code that you type in, or an
autonumber ID that will be generated by Access automatically for each
record you add to this table.

Then you would have a single table for all the videotapes. If each tape
can fall into only a single category, you'd have a Category field in
this table, and for each tape you enter, you'd choose the key field from
the list of categories stored in the Categories table.

If, on the other hand, a videotape could belong to more than one
category, you wouldn't put a Category field in this table. Instead,
you'd have a third table to represent this many-to-many relationship.
This third table would store both the key field from the VideoTapes
table and the key field from the Categories table, so that the presence
of a record in this table would mean that this videotape falls into this
category.

Since you'll only have one table for all the videotapes, running a query
to find all the tapes with a particular speaker is now simple; that is,
unless there could be multiple speakers per videotape. If that could be
the case, then you'd use a simlar three-table structure for that
many-to-many relationship, too, and the query would have to involve a
join of those tables. Still, there'd be no need to search multiple
table, which is what you really want to avoid.

1.When you used the work record at the top in caps did you mean feild?
2. I have 20 or so categories now all using the same fields inside them for
example one table is called CORPORATE and feilds inside it are: Date ,
Program name, Content Type, Footage Type, Run Time, and ten seperate fields
for speekers to be listed in the order of their apperence on the tape. Which
field would make the best primary key and do you think their is a need to
have three tables?

 

[Dirk Goldgar]

Thank you Dirk for the help but i still have some questions.
You wrote:
A different way from the one you've started on, I'm afraid. You should
*not* have a separate table for each category. Instead, you should
have
one table that lists all the categories, one RECORD for each category,
and assigns each category an identifying value to serve as its primary
key. That could be the category name itself, but that's a bit
cumbersome, or a shorter category code that you type in, or an
autonumber ID that will be generated by Access automatically for each
record you add to this table.

Then you would have a single table for all the videotapes. If each
tape
can fall into only a single category, you'd have a Category field in
this table, and for each tape you enter, you'd choose the key field
from
the list of categories stored in the Categories table.

If, on the other hand, a videotape could belong to more than one
category, you wouldn't put a Category field in this table. Instead,
you'd have a third table to represent this many-to-many relationship.
This third table would store both the key field from the VideoTapes
table and the key field from the Categories table, so that the
presence
of a record in this table would mean that this videotape falls into
this category.

Since you'll only have one table for all the videotapes, running a
query
to find all the tapes with a particular speaker is now simple; that
is, unless there could be multiple speakers per videotape. If that
could be
the case, then you'd use a simlar three-table structure for that
many-to-many relationship, too, and the query would have to involve a
join of those tables. Still, there'd be no need to search multiple
table, which is what you really want to avoid.

1.When you used the work record at the top in caps did you mean feild?

No. You don't want to have repeating groups of fields. Any time you
have tables set up with lists of repeating fields -- like "Category1",
"Category2", "Category3", ... -- that's a powerful indication that your
table should really be broken up into two.

2. I have 20 or so categories now all using the same fields inside
them for example one table is called CORPORATE and feilds inside it
are: Date , Program name, Content Type, Footage Type, Run Time, and
ten seperate fields for speekers to be listed in the order of their
apperence on the tape.

There's another example: the speakers on the tape shouldn't be stored
in separate fields in each tape record, but rather in separate records
in another table.

Which field would make the best primary key
and do you think their is a need to have three tables?

You should have one table, maybe named Videotapes, with fields like
these:

Videotapes
--------------
TapeID (primary key)
ProgramName
TapeDate ("Date" is not a good name for a field)
CategoryID
ContentType (if this is different from Category)
FootageType
RunTime

You should also have these tables with fields as indicated:

Categories
--------------
CategoryID (primary key: autonumber or user-assigned code)
CategoryDescription

Speakers
------------
SpeakerID (primary key)
SpeakerName

VideotapesSpeakers
--------------------------
TapeID
SpeakerID
SpeakerSequence (order of appearance on tape)


I don't know whether you may actually need a separate
VideotapesCategories table, because you haven't said whether a tape may
belong to more than one category. The above structure assumes each tape
can be in only one category, but may have multiple speakers.