Rational Tables?

I am starting a data base from scrach for a library of video tapes. The way
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/7036-rational-tables.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7036
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?

*

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

"Scooper2241" wrote in message
news:116F0A39-E78D-4171-B1DB-C5E5B7047E19@microsoft.com
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7036
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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7036

Thank you Dirk for the help but i still have some questions.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7036
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7036
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?


"Scooper2241" wrote:

> 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?

"Scooper2241" wrote in message
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7036
news:C764CC78-46A5-438F-BB3C-7E20B1D5B98D@microsoft.com
> 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:

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7036
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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)