Need some help in designing tables

[Amit]

Hi,

I'm designing a database and tables, and could do with
some feedback regarding the optimal way to go about it.

It's a database to store information about organizations
which will be used to print a resource directory. So, I'm
not anticipating much data manipulation or queries, and
this will result in a straight-forward report to be used
as a directory.

Data
====
The information consists of Organization name, address,
contact information etc. Each organization can have:
1. Materials
2. Activities
There are 13 different kinds of Materials (eg. Books,
Research articles, Video, Slide presentation, Catalog,
Cookbook etc.) that an organization can check. Each kind
of material applies to one or more of the following:
a. Preschool
b. Chlidren
c. Teens
d. Adults
e. Seniors
Additionally, each material can be available in languages
other than English. One or more of them can be selected.
So, I need to capture that information (Spanish/Cape
Verdean/Chinese/Portuguese/Vietnamese).

Similarly for Activities.
===
It seems to me that there is a one-to-one relationship
between an Organization, and the information for it. Also,
most of the fields will be of check-box kind (Yes/No).

What I'm not sure about is whether I should have all this
information in one table (even if there is one-to-one
relationship), or have different tables--one for
Organization, second for Materials and kind of materials,
and a third for Activities and kind of activities, and
then include the uniqueID of the Organization in the other
two tables as foreign key.

Will appreciate any feedback and/or pointers regarding
this.

Thanks!

-Amit

 

[Jeff Boyce]

Amit

From your description, I am envisioning a ... spreadsheet!

Any time you have a "one-to-many" relationship, you are better off in a
relational database to design a parent table and a child table.

If you have many "checkbox" type fields, I suspect you have a many-to-many
relationship. That is, an organization can have none, one, or many of the
"things" you are using checkboxes to indicate, and the "things" you are
using checkboxes for can belong to none, one, or many organizations.

If this reflects your situation, and you wish to use Access instead of
Excel, plan on using three tables to manage this: one table for
organizations, one table for "things", and one table to show the valid
pairings of organization and "thing".

There's probably more, but this would be a good starting point...

 

[Amit]

Hi Jeff,

Thanks for your response. Yes, it is more of a
spreadsheet, and we have considered the possibility of
using Excel instead of Access. But, it's much easier to
enter data using a form in Access than in a spreadsheet.

Cheers,

-Amit

 

[Jeff Boyce]

Amit

Depending on which version of Excel you have, you can create a "form" for
data entry in Excel, too!

 

[Bas Cost Budde]

Amit

Depending on which version of Excel you have, you can create a "form" for
data entry in Excel, too!

I did that in version 3.something so I don't think anyone can find a
version that doesn't allow this. Today, I mean.