database design

[DGregg]

I am trying to design a db (Access 2003 SP2) and do not know the best
approach to take. I have a paper form for which the Access db will be based.
I will need to create a form for data entry but am having difficulty
determining how to design the tables. In all, I will have 236 fields. Yes,
it's a lot and they are all unique (cannot be calculated using
query/reports/etc.). Once all of the records are entered I will eventually
need to export the data into other applications for additional GIS &
timeline/association analysis.

Originally I created one large table encompassing all 236 fields, but I must
have exceeded the 2000 character limit because I received errors and was
unable to save. I think the better approach might be to breakdown the db
into several tables as there are at least 4 main components for each record
(each component varies in the number of fields but can contain up to 25
fields for each component):

- person (unique; not a set list) (name, DOB, address, etc.)
- event (contains information such as date, time, ID#, location, etc.)
- supervisor review checklist
- employee (unique; not a set list) (name, DOB, address, etc. - will ALWAYS
be different from "person" information above)

The form is person-driven in that there could be multiple persons relating
to a single event (i.e. 3 persons for 1 ID#), therefore 1 record will be
created for each person. Each record will have information for one person
related to an event, event information itself, which in turn will contain the
supervisor review checklist. Also, each event will have information
pertaining to, at minimum, one employee, but could have up to 10 employees
involved with one person/event.

I was thinking of creating tables for Event, Person, Supervisor Review but I
do not know how to link them. I especially do not know how to handle the
employee component. Should there be one large table to hold information for
up to 10 employees (employee1_name, employee2_name, etc.) or should there be
a table for each employee (10 separate tables each containing the same
fields)? Also, normally the ID# would be unique, but since this db is
person-driven, the ID# can be duplicated, so what do I use as a primary key?

Again, I need to be able to arrange the tables so that I can easily have all
of the information entered via a form. The data will need to be analyzed and
exported (ideally in one large db) for further analysis. Please help; I'm at
a loss. Any help is greatly appreciated. Thank you in advance.

 

[tina]

you're describing a spreadsheet. if you're going to use Access to house and
work with this data, then store the data relationally - each entity is
entered one time only, in one place only, and then the entities are related
to each other. suggest something along the lines of

tblPeople
PersonID (Autonumber, primary key)
FirstName
LastName
DOB
Group (if an employee will NEVER be a "person" [participant?] associated
with an event, then you can designate each record as belonging to either the
"Person" group OR the "Employee" group.)
<other fields that describe a person only. each person, whether an employee
or not, is entered in this table one time.>

tblEvents
EventID (Autonumber, primary key)
(or you can use the event ID# as the primary key, as long as it is unique
and unchanging.)
Location
DateAndTime (Date/Time data type)
<other fields that describe an event only. each event is entered in this
table one time.>

tblEventPeople
EventPeopleID (Autonumber, primary key)
EventID (foreign key from tblEvents)
PersonID (foreign key from tblPeople)
<any other fields that describe an instance of a specific person related to
a specific event. this is the table that relates your entities - Events and
People - to each other.>

the supervisor checklist is a complete cipher; you haven't given enough
information for me to make any suggestions about its' structure, other than
to say that it should probably be related to tblEvents, using EventID as a
foreign key, as we did above in tblEventPeople.

Again, I need to be able to arrange the tables so that I can easily have
all of the information entered via a form.

don't base structural decisions on interface requirements. store the data in
correctly designed and related tables FIRST, and then build the user
interface to meets the data entry needs of the end user.

i strongly urge you to read up on relational design principles, BEFORE you
implement *any* data model in this database. properly used, Access is a very
powerful tool for data storage and manipulation - improperly used, it can be
a nightmare to work with. for more information, see
http://home.att.net/~california.db/tips.html#aTip1.

hth