Good Evening All -
Because I am STILL undecided on a particular data model, I have been reading posts and re-researching normalization. I have examined other similiar data models, tried various scenarios, and combined those with the actual business process, and I keep coming back to a One-to-One relationship. Further, I must say, there are many different points of view on 1/1 relationships. So, just for discussion sake and because I am currently leaning that way, what are the pros and cons of the following structure:
tblEntity
EntityID (PK)
txtentityName
etc.
tblLocation
LocationID (PK)
txtLocationName
etc.
Entities can have many certifications
and
Locations can have many certifications
therefore, M/M
tblJTEntityLocations
EntityLocationID (PK)
lngLocationID (FK)
lngEntityID (FK)
EntityLocations can have many certifications 1/M
tblCertifications
CertificationID (PK)
lngEntityLocationID (FK)
dteCompletionDate
The certification process is comprised of three separate completion milestones (transactions) and each one has very distinct and sizeable data requirements.
So, the detail tables that follow are the ones I think have a 1/1 relationship with the tblCertifications. I'm fairly certain, there would never be more than one record in the detail table for each CertificationID.
tblCertificationDetailsSection1
CertificationDetailsSection1ID (PK)
lngCertificationID (FK)
tblCertificationDetailsSection2
CertificationDetailsSection2ID (PK)
lngCertificationID (FK)
tblCertificationDetailsSection3
CertificationDetailsSection3ID (PK)
lngCertificationID (FK)
I could combine the three detail tables with the Certification table and have one very large table but, I keep thinking that doesn't really follow the business process and I'm concerned about the simplicity of design as well as ease in which data is extracted for reporting purposes. Oh, I forgot to mention, the government is involved in the certification process and they have strict form completion and reporting requirements. Hopefully all this make sense. Please share any thoughts and suggestions...
Thanks, in advance, for your help!