Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Rational Tables?

Discussion in 'Information Technology' started by Scooper2241, Nov 10, 2005.

  1. Scooper2241

    Scooper2241
    Expand Collapse
    Guest

    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?
     
  2. Loading...

    Similar Threads Forum Date
    Is Sikh Philosophy Empiricism or Rationalism? Sikh Sikhi Sikhism Oct 21, 2014
    Inspirational Pictures - Yes, Why? Inspirational Stories Oct 28, 2013
    USA Rationalist Society on COW URINE CRAZE (in Punjabi) Breaking News Jan 14, 2013
    Inspirational film fun for girls, boys, dads and Soccer Moms Sikh Youth May 22, 2010
    Atheism Rational Theists and Rational Atheists of the World, Unite! Interfaith Dialogues Mar 25, 2010

  3. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "Scooper2241" <Scooper2241@discussions.microsoft.com> 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
    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)
     
  4. Scooper2241

    Scooper2241
    Expand Collapse
    Guest

    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?


    "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?
     
  5. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "Scooper2241" <Scooper2241@discussions.microsoft.com> wrote in message
    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:

    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)
     

Share This Page