hello list,

I've got a list of resources (books, audio cass., videos, etc.) that I'm
putting into a database. The problem comes when I try to categorize these
resources. Some of them can fit into multiple categories. For example: the
video "Yoga Practice for Beginners" can fit into "Alernative Medicine",
"Fitness", & "Stress Management". I'd like to have a field that could
contain a list of numbers that are the primary keys of category names in a
"Categories" table.

Is this a good way to set this up?
Is there a better way?
Does anybody see any pitfalls to this approach?
Are there any built-in Access or SQL procedures (functions) to seperate out
the category numbers?

Thanks in advance for you thoughts,

Rich

I think this is a pretty reasonable strategy.  

Karri

-----Original Message-----
From: Azinger, Richard [mailto:richard.azinger@p...]
Sent: Monday, May 20, 2002 4:25 PM
To: Access ASP
Subject: [access_asp] Multiple categories



hello list,

I've got a list of resources (books, audio cass., videos, etc.) that I'm
putting into a database. The problem comes when I try to categorize these
resources. Some of them can fit into multiple categories. For example: the
video "Yoga Practice for Beginners" can fit into "Alernative Medicine",
"Fitness", & "Stress Management". I'd like to have a field that could
contain a list of numbers that are the primary keys of category names in a
"Categories" table.

Is this a good way to set this up?
Is there a better way?
Does anybody see any pitfalls to this approach?
Are there any built-in Access or SQL procedures (functions) to seperate out
the category numbers?

Thanks in advance for you thoughts,

Rich

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Azinger, Richard" <richard.azinger@p...>
Subject: [access_asp] Multiple categories


: I've got a list of resources (books, audio cass., videos, etc.) that I'm
: putting into a database. The problem comes when I try to categorize these
: resources. Some of them can fit into multiple categories. For example: the
: video "Yoga Practice for Beginners" can fit into "Alernative Medicine",
: "Fitness", & "Stress Management". I'd like to have a field that could
: contain a list of numbers that are the primary keys of category names in a
: "Categories" table.
:
: Is this a good way to set this up?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Not really

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: Is there a better way?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Yes - if you model this using an ER diagram, you will see that you have what
is called an M:N relationship (many-to-many).
To handle this in a database you would first create two tables that model
the real entities (ie products and categories).
Then you would create a *third* table, which is called a "join" table. Here
you stored the relationships between the two other tables.

The third table would contain just two fields (in your case), being
ProductID and CategoryID, and these two fields would be a composite primary
key.

If the vidoe "Yoga Practice for Beginners" had ProductID = 1, and the three
categories you mentioned had CategoryIDs 1,2,3, you would store this as:

ProductID    CategoryID
1                        1
1                        2
1                        3

If you have another product (say "My test video") and it belonged to
categories 1,2,6 then you'd add records like so:

ProductID    CategoryID
1                        1
1                        2
1                        3
2                        1
2                        2
2                        6

Cheers
Ken