database scheme general principles

[jjatcal]

Okay, I have gone through quite a few posts in reading up on design and now I am thoroughly questioning my database design. I am very new to this, so please bear with me....I see some folks quite helpful so I want to try this out.

It has been mentioned many places that 1-1 relationship is rarely used. This suggests that I may not be doing my database structure right, because I have those all over the place.

Here is what I have. Most of these are one to one. I have chosen to cascade update and cascade delete because if a record is updated/deleted in one table, it should be updated/deleted in all.

I have read the normalization paper on Jeremy's website but need some specific help to apply what it says....thanks...

With the exception of the ApplicantTracking (first listed) table, all are fields on the application form students would complete for the internship.

What is the normalized and suggested way to structure based on capturing this specific data?

Is it generally suggested to create a query on ALL these tables and then a form on the query, and use the form to update the tables? I have seen that in some posts too.

I guess in a nutshell I am asking for best practices or guiding principles etc...any docs on those you could forward in addition would be useful.

ApplicantTracking table

ApplicantID Text 50
CompleteApplPackage Yes/No 1
ApplSubmitted Yes/No 1
DateApplSubmitted Date/Time 8
ApplComplete Yes/No 1
ApplIncompNotice Yes/No 1
DateApplNotice Date/Time 8
ApplResubmit Yes/No 1
DateApplResubmit Date/Time 8
ResumeSubmitted Yes/No 1
DateResumeSubmitted Text 50
ResumeComplete Yes/No 1
ResumeIncompNotice Yes/No 1
DateResumeNotice Date/Time 8
ResumeResubmit Yes/No 1
DateResumeResubmit Date/Time 8
SOPSubmitted Yes/No 1
DateSOPSubmitted Date/Time 8
SOPComplete Yes/No 1
SOPIncompNotice Yes/No 1
DateSOPNotice Date/Time 8
SOPResubmit Yes/No 1
DateSOPResubmit Date/Time 8
SourceMonsterTrak Yes/No 1
SourceInternshipProgram Yes/No 1
DateCreated Text 50

Education table

ApplicantID Text 50
UnivName1 Text 50
UnivLocation1 Text 50
UnivFromDate1 Date/Time 8
UnivToDate1 Date/Time 8
Major1 Text 50
Degree1 Text 10
DegreeDate1 Date/Time 8
UnivName2 Text 50
UnivLocation2 Text 50
UnivFromDate2 Date/Time 8
UnivToDate2 Date/Time 8
Major2 Text 50
Degree2 Text 50
DegreeDate2 Date/Time 8
AllGPA Text 4
MajorGPA Text 4
GradGPA Text 4
DateCreated Text 50

EthnicityGender table

ApplicantID Text 50
Black Yes/No 1
EastIndian Yes/No 1
Chicano Yes/No 1
AmericanIndian Yes/No 1
White Yes/No 1
SpanishAmerican Yes/No 1
Chinese Yes/No 1
Japanese Yes/No 1
Vietnamese Yes/No 1
Filipino Yes/No 1
Other Yes/No 1
OtherExplain Memo -
TwoPlus Yes/No 1
TwoPlusExplain Text 50
Male Yes/No 1
Female Yes/No 1
DateCreated Text 50

GeographicalPreferences table

ApplicantID Text 50
NorCalRank Long Integer 4
NorCalExplain Text 50
SoCalRank Long Integer 4
SoCalExplain Text 50
EastMARank Long Integer 4
EastMAExplain Text 50
NYRank Long Integer 4
NYExplain Text 50
DateCreated Text 50

HealthcareCourseWork table

ApplicantID Text 50
CourseTitle1 Text 50
Institution1 Text 50
Grade1 Text 2
CourseTitle2 Text 50
Institution2 Text 50
Grade2 Text 2
CourseTitle3 Text 50
Institution3 Text 50
Grade3 Text 2
CourseTitle4 Text 50
Institution4 Text 50
Grade4 Text 2
CourseTitle5 Text 50
Institution5 Text 50
Grade5 Text 2
CourseTitle6 Text 50
Institution6 Text 50
Grade6 Text 2
CourseTitle7 Text 50
Institution7 Text 50
Grade7 Text 2
CourseTitle8 Text 50
Institution8 Text 50
Grade8 Text 2
CourseTitle9 Text 50
Institution9 Text 50
Grade9 Text 2
CourseTitle10 Text 50
Institution10 Text 50
Grade10 Text 2
DateCreated Text 50

OrganizationType
ApplicantID Text 50
Hospitals Yes/No 1
CommClinics Yes/No 1
MedicalGroups Yes/No 1
Policy Yes/No 1
HMO Yes/No 1
HealthDept Yes/No 1
Vendor Yes/No 1
OtherOrg Yes/No 1
OtherOrgExplain Text 50
DateCreated Text 50

Personal table
ApplicantID Text 6
LastName Text 50
FirstName Text 50
MiddleInitial Text 2
CurrPhone Text 10
EmailAddress Text 50
CurrStreet Text 255
CurrCity Text 50
CurrState Text 2
CurrZip Text 9
PermPhone Text 10
PermStreet Text 50
PermCity Text 50
PermState Text 2
PermZip Text 9
DateCreated Text 50

PreferencesConstraints table

ApplicantID Text 50
AvailableFullTime Yes/No 1
FullTimeExplain Memo -
SummerSchool Yes/No 1
CarAvail Yes/No 1
WillVolunteer Yes/No 1
HealthPolicy Long Integer 4
HealthEducation Long Integer 4
OtherPublicHealth Long Integer 4
OtherPublicHealthExplain Text 50
CommHealth Long Integer 4
PreMedicine Long Integer 4
Nursing Long Integer 4
SpeechPathology Long Integer 4
PhysicalTherapy Long Integer 4
ClinicalInvest Long Integer 4
DateCreated Text 50

ReferralSource table
ReferralID Long Integer 4
ApplicantID Text 50
ReferrerName Text 50
ReferrerEmail Text 50
ReferrerStreet Text 50
ReferrerCity Text 50
ReferrerState Text 50
ReferrerZip Text 9
CampusPresentation Yes/No 1
Professor Yes/No 1
ProfessorName Text 50
CampusCareerCenter Yes/No 1
ListServe Yes/No 1
HCCAlumni Yes/No 1
OtherWebsite Yes/No 1
OtherWebsiteExplain Memo -
HCCBoothFair Yes/No 1
OtherReferral Yes/No 1
OtherReferralExplain Memo -
DateCreated Text 50

Thanks.

 

[nberryman]

Firstly EthnicityGender table

You don't ned a field for every ethnic type so this would work:

Add a field to ApplicatntTracking for EthnicType.

EthnicityGender table

EthhnicID
EthnicDescription

Each type of ethnic type would be a record in the table and not a field.

Gender. There's only two choices so a combo box in the Applicant tracking table for Male or Female would be enough.

I would also reconsider the grades table and use the same technique for recording grades as entries in a table rather than fields

And finally why are you using Text to store dates? SHould be a date type.


Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[lespaul]

This is an example of a one to many relationship:

HealthcareCourseWork table

ApplicantID    Text    50
    CourseTitle1    Text    50
    Institution1    Text    50
    Grade1    Text    2
    CourseTitle2    Text    50
    Institution2    Text    50
    Grade2    Text    2
    CourseTitle3    Text    50
    Institution3    Text    50
    Grade3    Text    2
    CourseTitle4    Text    50
    Institution4    Text    50
    Grade4    Text    2
    CourseTitle5    Text    50
    Institution5    Text    50
    Grade5    Text    2
    CourseTitle6    Text    50
    Institution6    Text    50
    Grade6    Text    2
    CourseTitle7    Text    50
    Institution7    Text    50
    Grade7    Text    2
    CourseTitle8    Text    50
    Institution8    Text    50
    Grade8    Text    2
    CourseTitle9    Text    50
    Institution9    Text    50
    Grade9    Text    2
    CourseTitle10    Text    50
    Institution10    Text    50
    Grade10    Text    2
    DateCreated    Text    50

Any time you start naming fields #1, #2, etc., that's a sure sign of a one to many relationship. Each applicant has many courses!

What is this database going to be used for?


Leslie

 

[dhookom]

For starters, I would remove almost every date out of the ApplicantTracking table. The proper normalization would have:
[Blue]
tblApplicationSteps
===================
StepID (autonumber Primary Key)
StepTitle (values like App Submitted, Resume, ...)
StepActive (yes/no set to No when you no longer require)
StepSequence (number to use for standard order of completion)

tblApplicantTracking
==============
ApplicantID
StepID (link to tblApplicationSteps)
StepDate date of completion or submission
StepComments (optional text)
[/Blue]
Every individual date/step for each applicant should create a new record. This type of normalization allows you to add or remove steps without ever changing the table structure or form/report controls.
You have similar issues in PreferencesConstraints, Education, EthnicityGender, GeographicalPreferences, HealthcareCourseWork, OrganizationType
, and PreferencesConstraints tables.

You have created a structure that is a nightmare for maintenance. Proper normalization will provide much more flexibility.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[JeremyNYC]

Yeah, this will be crazy painful to work with. It's good that you read that paper by Paul, but I definitely think you should get back there and read it again, with some of the above comments in mind. It takes a while to wrap one's brain around some of these things, but the payoff is simply huge.

Your HealthcareCourseWork table is a good place to start, as LesPaul pointed out. You have a series of repeating fields. That's one of the things the paper advises against.

ApplicantID Text 50
CourseTitle1 Text 50
Institution1 Text 50
Grade1 Text 2
CourseTitle2 Text 50
Institution2 Text 50
etc, etc...

Instead it should look like this:
PersonID
CourseID
InstitutionID
Grade

(no etc. That's where it stops.)

You'll notice I've changed the field names. All of these should be numeric. The first three will be foreign keys, meaning that they'll be linked to a field with the same name that is an autonumber primary key in another table. In each case, that table should be named the same as the field, except you'll put tbl in front and chop off the ID from the back.

Each of those other tables will describe the thing named. tblPerson will describe a person. Whether that person is an applicant or a janitor, most of the fields you track for a person will end up being similar. tblCourse will describe a course. And it may only have one other field: "FieldName". Or it may end up having several others. But you don't want to classify a course as two different courses just because there's a typo in one of the records. So you make a list of the courses and each time someone takes a course, you put in the course ID. (You'll learn more about how to make that happen easily when you start building forms.)

This setup allows you to have a person take as many courses as they want. If you want to limit that, it's not a problem; use the validation rule to limit the grade they can have. Or use code in your form to make sure there's not more than a certain number of records for each person. But you'll tackle that later. What you want to do now is get comfortable with this new way of approaching data.

Any of this make any sense to you?

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[jjatcal]

Lespaul, this database is to take in applications for an internship program.

I am going to digest some of what all of you have written and go back to the drawing board. Give me a couple of days and I'll be back with some questions (I need to work on another key client project and need to put this on hold for a few days).

Jeremy, your info was good. I needed that level of specificity in what was being suggested. Like I said, I will digest it and redesign and get back.

Thansk again!!!