Normalizing Access Database

Report · Mar 31, 2008

Hello~

I have a fairly solid background in CFML, but I am having trouble with the database end of things. Right now, I am using Microsoft Access, which I know is not ideal. I have a fairly complicated database for a project right now, and I was wondering if I could get some tips on how to re-organize it for more efficiency, because I feel like there are a lot of extra steps that I may not need. Any tips would be helpful. Thank you!

TABLES:

- MENTORS
id (autoNumber)
prefix
firstName
middleName
lastName
suffix
statusID (number)
email
license
licenseState
organization
department
address
city
state
zip
country
countryOther (memo)
orgOverview (memo)
webSite
phone
fax
agreement
practiceYears
attorneys
practiceTypeID (number)
practiceArea1 (number)
practiceArea2 (number)
practiceArea3 (number)
practiceArea4 (number)
practiceAreaOther (memo)
supervisionPlan (memo)
otherSupervisorsMore (memo)
areaResources (memo)
substantive (memo)
skills (memo)
skillsOther (memo)
statute
certified
certifiedMore (memo)
disabilities
agreement2
signature
formDate (date/time)
datestamp

- INTERNOPS
id (autoNumber)
mentorID (number)
mrlsSuitable
internDuties (memo)
semester
semesterOther (memo)
criteria (memo)
applicationMaterials (memo)
applicationMethod
submissionInfo (memo)
appDeadline (memo)

- PRACTICEAREA
id (autoNumber)
areaName

- PRACTICETYPE
id (autoNumber)
typeName

- STATUS
id (autonumber)
status

Report · Mar 31, 2008

The first thing that comes to my mind is to take these fields out of the mentors table:
practiceArea1 (number)
practiceArea2 (number)
practiceArea3 (number)
practiceArea4 (number)
practiceAreaOther (memo)

and set up a many to many relationship.

Report · Apr 04, 2008

Here are some great examples that may help you. It's like a DB class in a single HTML page.

Rules of Data Normalization