Making a 1-1 database usable for future projects

[Confused Esther]

I have created a database to manage the process of recruitment for a project.

Currently all the details of the applications are recorded in one very large
table and I'm using autonumbers for the primary key.

The personal and contact details of the applicant are displayed on one form.
The details relating specifically to the application for this project are
displayed on an additional form.

I want to use this database for future projects (keep the pool of applicants
already there and be able to add some additional applicants).

Ideally, I would like the main form to be the applicants personal details
with separate forms for each project specific data.

To do this, is it best to keep all the data in the one table or separate the
data into tables such as 'applicant's personal details', 'project 1
applications', 'project 2 applications?

If it's best to separate the data into multiple tables, I worry about how to
make primary keys as the details are all relating back to only 1 entity. Any
suggestions?

If it's best to keep all details in the one (increasingly longer) table and
just display it in different forms, that's fine, that's what I've been doing
anyways.

The only problem that I'm having with the way it is now, is there is one
field in the table for the applicant's name, but the field appears on both
forms ('personal details form' and 'project 1 applications' form). If I have
opened both forms and I try to make changes to the applicant's name in the
original form, a message appears saying 'the data has been changed by someone
else'. If you drop changes, and close the other form, you can then go back
and make the changes. However, if you ignore it, eventually the database
crashes.

I realise the problem is having the name displayed on both forms, but is
there any way to avoid this problem?

Suggestions and advice much appreciated!!!!

 

[John W. Vinson]

On Wed, 4 Jun 2008 20:53:01 -0700, Confused Esther <Confused

I have created a database to manage the process of recruitment for a project.

Currently all the details of the applications are recorded in one very large
table and I'm using autonumbers for the primary key.

That's a flawed design from the get-go, I fear. Thirty columns is a VERY wide
table.

The personal and contact details of the applicant are displayed on one form.
The details relating specifically to the application for this project are
displayed on an additional form.

Fields pertaining to an applicant should be stored in a table of Applicants -
and nowhere else.

Fields pertaining to a project should be stored in a *separate* table of
Projects - and noplace else.

Fields pertaining to the relationship of an applicant to a project should be
stored in yet a third table, with ONLY the ApplicantID and ProjectID fields
from the first two tables.

I want to use this database for future projects (keep the pool of applicants
already there and be able to add some additional applicants).

The above will do so.

Ideally, I would like the main form to be the applicants personal details
with separate forms for each project specific data.

Base different forms on different tables.

To do this, is it best to keep all the data in the one table or separate the
data into tables such as 'applicant's personal details', 'project 1
applications', 'project 2 applications?

Neither. See above.

If it's best to separate the data into multiple tables, I worry about how to
make primary keys as the details are all relating back to only 1 entity. Any
suggestions?

You needn't and shouldn't relate all information to "1 entity". An Applicant
is one type of entity; the Applicant table has its own primary key. A Project
is *A DIFFERENT KIND* of entity, and has *its own* primary key, distinct from
and unconnected with the Applicant table's primary key.

If it's best to keep all details in the one (increasingly longer) table and
just display it in different forms, that's fine, that's what I've been doing
anyways.

The only problem that I'm having with the way it is now, is there is one
field in the table for the applicant's name, but the field appears on both
forms ('personal details form' and 'project 1 applications' form). If I have
opened both forms and I try to make changes to the applicant's name in the
original form, a message appears saying 'the data has been changed by someone
else'. If you drop changes, and close the other form, you can then go back
and make the changes. However, if you ignore it, eventually the database
crashes.

I realise the problem is having the name displayed on both forms, but is
there any way to avoid this problem?

Yes. Normalize your database as described above. For some tutorials in how
normalization works see

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[Confused Esther]

Thanks so much for your response John. It's helped, but I think I need to
clarify further.

Currently, all fields in my database relate to the applicant. However, there
are 2 types of information stored:

1. Applicants' Personal Info (Applicant name, DOB, contact details,
qualifications, etc)

2. Applicant's Application details for this specific project (were they
invited to apply for this project, what date did they apply for this project,
were they offered a job for this project, did they accept, what position, pay
rate were they offered, which work group will they be part of during this
project, did they withdraw from this project etc etc.

In order for me to, in future, re-use this database to hold specific
application details for further projects (whilst maintaining the previous
data) I'm more than happy to make separate tables (my one large table is
slightly scary).

What I'm assuming from your earlier advice is I should set up multiple tables:

*1 table holding the details relating to the applicant (using an unique
identifier (autonumber) as the PK?).
*1 table holding the details relating to their application for this current
project, and more tables for each further project (in this case would I have
to create a new unique identifier as an 'application ID' as the PK?). How
would I link the above 2 tables?
*1 table holding the details of the projects (using a project number as a
PK?). How would I link this table to the other ones?

Sorry if what I'm asking is exactly what you said before, but I'm not a
techie and just need to confirm!

I'll check out the links you provided too..

Cheers

 

[John W. Vinson]

Thanks so much for your response John. It's helped, but I think I need to
clarify further.

Currently, all fields in my database relate to the applicant. However, there
are 2 types of information stored:

1. Applicants' Personal Info (Applicant name, DOB, contact details,
qualifications, etc)

That's fine.

2. Applicant's Application details for this specific project (were they
invited to apply for this project, what date did they apply for this project,
were they offered a job for this project, did they accept, what position, pay
rate were they offered, which work group will they be part of during this
project, did they withdraw from this project etc etc.

These fields are NOT attributes *of an applicant*. They are attributes *of an
application by one applicant to one project*.

They should be in the third table I mentioned.

In order for me to, in future, re-use this database to hold specific
application details for further projects (whilst maintaining the previous
data) I'm more than happy to make separate tables (my one large table is
slightly scary).

What I'm assuming from your earlier advice is I should set up multiple tables:

*1 table holding the details relating to the applicant (using an unique
identifier (autonumber) as the PK?).

Personal details ONLY - nothing project related, nothing application related.
You do need a PK; autonumber is one way, if you have some external ID that is
guaranteed to be unique, stable, and short, by all means use it. (Hint:
people's names fail on all three counts).

*1 table holding the details relating to their application for this current
project, and more tables for each further project (in this case would I have
to create a new unique identifier as an 'application ID' as the PK?). How
would I link the above 2 tables?

The table should contain fields for the PersonID (Long Integer if that's an
autonumber) and Project Number (same datatype as the PK of Projects). The
people table would be linked one to many to this table by PersonID; the
Projects table would also be linked to it one to many by ProjectNumber. If
this table isn't related to *further* tables then you could ctrl-click these
two fields and then the Key icon, to make them a joint, two-field primary key;
this will prevent you from having the same person apply twice to the same
project.

This table would also have fields for the offer, acceptance, pay rate, etc.
etc.

*1 table holding the details of the projects (using a project number as a
PK?). How would I link this table to the other ones?

See above.

Sorry if what I'm asking is exactly what you said before, but I'm not a
techie and just need to confirm!

"Figure three times, measure twice, cut once"... <g>