Is this a normalization problem?

[Gina K]

I’m trying to develop a database to track Engineering Change Requests (ECR)
and prevent drawings that are under ECR from being printed. Once an ECR is
initiated, the drawing's status will read "Under ECR Control" and there are 8
Yes/No questions to be answered (such as is there a job in process, is a
routing change required, are there parts in inventory). Based on the answers
to these questions there are up to 14 signoffs required to verify all changes
have been made. Once all applicable signoffs are completed the drawing's
status will read "Clear" and then it can be printed.

I started out having all 22 (8 Yes/No and 14 signoffs) in the same table
with field names like:
DrawingNumber (text)
ECRInitiatedBy (text)
DescOfChange (memo)
DeliverySchedChangeRequired (Y/N)
DeliverySchedAdjustedBy (text)
DateDeliverySchedAdj (date)
NewDetailRequired (Y/N)
NewDetailCompletedBy (text)
DateNewDetailCompleted (date)
FixtureChangeRequired (Y/N)
FixtureChangedBy (text)
DateFixtureChanged (date)
and so on.

I set up code in my data entry form to fill “N/A†and the current date in
the fields that were not required (based upon the answers given to the Yes/No
questions). But when I tried to come up with a way to verify that all the
fields were filled in so that I could set the drawing's status to "Clear", it
got ugly. I’m thinking that maybe my database isn’t properly normalized, and
that this table should be split? If not, can you help me come up with a
graceful way of checking these fields? (The way I had come up with was very
convoluted.)

Any suggestions would be greatly appreciated.
Thanks.

 

[mscertified]

Your design is definitely not normalized and could lead to maintenance
problems. For instance, what happens if additional signatures or yes/no
answers become necessary?
You should have the yes/no questions and verifications each in their own
separate tables, each as a separate row. Do all the yes/no questions have to
be answered before any signatures are possible or is there a dependency
between each yes/no and each signature. If the latter, the design gets a bit
more complex.
Each time a signature is granted you will have to run a query to determine
if now all the signatures have been granted and if so, mark the ECR as
'clear'.

-Dorian

 

[Roger Carlson]

Hi Gina,

I agree that you data model is not normalized. On my website
(www.rogersaccesslibrary.com), is a small Access database sample called
"ReallyBadDatabaseDesign.mdb" which illustrates this design problem and how
to solve it. It is essentially what mscertified recommended, but I thought
an example might be useful.

BTW, I don't mean to imply that your design is really bad, just that the
sample on my site is an extreme example of the problem.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Gina K]

Do all the yes/no questions have to be answered before any signatures are

possible or is there a dependency between each yes/no and each signature.

All the yes/no questions will be answered before the signoffs begin.

Each time a signature is granted you will have to run a query to determine
if now all the signatures have been granted and if so, mark the ECR as
'clear'.

Sorry that I'm so dense, but what exactly would this query do - check for
null values in each of the signature fields or something like that?

Thanks again.