Initial Check Box Setup

[Mike C.]

Hello

I am in the process of designing a database and am
wondering what the best setup would be. The database will
contain the usual user info (name, address, etc.), but it
will also contain a section that will have a number of
catagories that will be associated with that entry
(Catagory 1 - 10) and another section that will do pretty
much the same (subcatagory 1-5). I want to achieve this by
using check boxes on the form but am not really sure what
the most effieient way of doing this is.

I will have the "MainTable" that will contain all the user
information (FirstName, LastName, PhoneNumber, etc.) but
not sure how to set up the checkboxes. Would the below
example be sufficient and what type of problems can I expect.

Field Name DataType
----------------------
FirstName Text
LastName Text
Catagory1 Yes/No
Catagory2 Yes/No
SubCatagory1 Yes/No
SubCatagory2 Yes/No

If this is not the best way, any advice would be welcomed.

Also, evenutally, this will generate a report and I will
want just the catagory(s)/subcatagory(s) that are checked
to show up. Will this be feasible?

Any assistance would be greatly appreciated.

Thanks in advance,

m.

 

[Douglas J. Steele]

It's not the best approach.

You should have 3 tables: one for the user information, one for the details
of what categories are associated with each user, and one for the details of
what subcategories are associated with each user and category.

In other words, categories and subcategories should be rows in tables, not
fields.

Let's assume 2 users:

ID,FirstName,LastName
1,Mike,C
2,Doug,Steele

Let's assume you're associated with categories 2 and 4, and I'm associated
with categories 1 and 2.

ID,Category
1,2
1,4
2,1
2,2

Let's assume you're associated with subcategory 3 for category 2 and
subcategory 1 for category 4, while I'm associated with subcategory 1 for
category 1 and both subcategories 2 and 3 for category 2:

ID,Category,Subcategory
1,2,3
1,4,1
2,1,1
2,2,2
2,2,3