newbie help with normalization

  • Thread starter Thread starter d4
  • Start date Start date
D

d4

I have a spreadsheet like table that I want to break down. I have the
concept but need help/recommendations with Keys and relationships.
The machine_id is unique. The server name is linked to the machine_id,
but the name can change (ex. server1a when being built, then server1
when in production). There can be multiple QC's done on a server,
usually only one request per QC 1 or 2 - but can have another request
months later.
This is making my head hurt, so if anyone can shed some light it would
be much appreciated.

Original:

ID [PK]
Server
Build Type
Received Request
QC 1 Started
QC 1 Major Findings
QC 1 Minor Findings
QC 1 Comments
QC 1 Completed
QC 1 Performed By
Sent to Programmers
Returned
QC 2 Started
QC 2 Major Findings
QC 2 Minor Findings
QC 2 Reason in wait status
QC 2 Comments
QC 2 Completed
QC 2 Performed By

Broken Down:

Table 1: machine_id (unique) {Long Integer}

Table 2: Server (can be different name (rename, rebuilds, etc) but
still linked to machine_id) {Text}
Build Type {Text}

Table 3: Received Request {Short date}
Sent to Programmers {Short date}
Returned {Short date}

Table 4: QC Type (1 or 2) {Long Integer}
Started {Short Date}
Major Findings {Text}
Minor Findings {Text}
Comments {Text}
Completed {Short Date}
Performed By {Text}

Table 5: QC 2 Reason in wait status {Text}
Follow up {Short date}
Contact {Text}
 
What is the connection between Machine and Server? You say they are linked,
but it is unclear how.
In general it would go something like this:
Let's say that each server can have any number of requests assoicated with
it. To handle this you would have a Server table (tblServer) and a Requests
table (tblRequest). Each Server would be identified in tblServer with a
unique number or text string. I will call the field ServerID. Autonumber
is fine as this unique identifier (or primary key) if nothing else presents
itself. tblRequest would contain its own ID (such as RequestID), a ServerID
field, and whatever else you need such as date. ServerID in tblRequest
needs to be the same data type (as defined in table design view) as ServerID
in tblServer, except if ServerID in tblServer is an Autonumber, ServerID in
tblRequest would be Number (Long Integer is a good choice).
Click Tools > Relationships. Add both tables, then drag ServerID from one
table and drop it on ServerID in the other table. Click Enforce Referential
Integrity. You have established a one-to-many relationship.
Build a form (frmServer) based on tblServer, and another (fsubRequest) based
on tblRequest. With frmServer open in design view, drag the icon for
fsubRequest onto a blank space on frmServer. Now you can add server
information via the main form, and Request information (as many requests as
you need) via the subform.
I see that your QC table contains a place for major and minor findings.
Using the pattern described above, you would do well do have a Findings
table, related to the QC table. Each record in the Findings table could be
identified as Major or Minor. Or you could have a separate table for major
and minor. In any case, you can record any number of findings rather than
trying to cram everything into a limited number of fields. Having said
that, the relationship of the QC table to anything else is a matter of
guesswork to those of us who can't see your database. The purpose of the
second QC table is not clear, either.
To receive a more directed response, it would help if you describe in more
detail the real-world situation you are trying to organize. Remember, here
in the newsgroup we know nothing about it. All we have is your description.

d4 said:
I have a spreadsheet like table that I want to break down. I have the
concept but need help/recommendations with Keys and relationships.
The machine_id is unique. The server name is linked to the machine_id,
but the name can change (ex. server1a when being built, then server1
when in production). There can be multiple QC's done on a server,
usually only one request per QC 1 or 2 - but can have another request
months later.
This is making my head hurt, so if anyone can shed some light it would
be much appreciated.

Original:

ID [PK]
Server
Build Type
Received Request
QC 1 Started
QC 1 Major Findings
QC 1 Minor Findings
QC 1 Comments
QC 1 Completed
QC 1 Performed By
Sent to Programmers
Returned
QC 2 Started
QC 2 Major Findings
QC 2 Minor Findings
QC 2 Reason in wait status
QC 2 Comments
QC 2 Completed
QC 2 Performed By

Broken Down:

Table 1: machine_id (unique) {Long Integer}

Table 2: Server (can be different name (rename, rebuilds, etc) but
still linked to machine_id) {Text}
Build Type {Text}

Table 3: Received Request {Short date}
Sent to Programmers {Short date}
Returned {Short date}

Table 4: QC Type (1 or 2) {Long Integer}
Started {Short Date}
Major Findings {Text}
Minor Findings {Text}
Comments {Text}
Completed {Short Date}
Performed By {Text}

Table 5: QC 2 Reason in wait status {Text}
Follow up {Short date}
Contact {Text}
Click to expand...
 
The original list is what I have now. I am running Quality Checks (QC)
on Servers.
The machine_id is a unique id given to each server, which also has a
server name. I guess linked is the wrong word as machine_id = server
name, but the server name can be changed (it is called one thing while
being built, then renamed when operational).
There are 2 types of QC that are done. One type when the machine is
being built (QC1) and one type when operational (QC2). However, there
can be multiple runs of QC1 or 2 (until all Findings are fixed). Also,
if a server needs to be QC at a later time, (checkups, rebuilds, etc)
then another round of QC's will be run.
The QC 2 table is when a server is QC'd and needs to have a QC 2 ran.
It is in a wait status and I need to follow up with the builder every 2
weeks until they are done installing whatever additional stuff they
need to.

Sorry about the lack of description earlier. This is new to me. Thanks.
 
The original list is what I have now. I am running Quality Checks (QC)
on Servers.
The machine_id is a unique id given to each server, which also has a
server name. I guess linked is the wrong word as machine_id = server
name, but the server name can be changed (it is called one thing while
being built, then renamed when operational).
There are 2 types of QC that are done. One type when the machine is
being built (QC1) and one type when operational (QC2). However, there
can be multiple runs of QC1 or 2 (until all Findings are fixed). Also,
if a server needs to be QC at a later time, (checkups, rebuilds, etc)
then another round of QC's will be run.
The QC 2 table is when a server is QC'd and needs to have a QC 2 ran.
It is in a wait status and I need to follow up with the builder every 2
weeks until they are done installing whatever additional stuff they
need to.

Sorry about the lack of description earlier. This is new to me. Thanks.
 
Responses inline.
d4 said:
The original list is what I have now. I am running Quality Checks (QC)
on Servers.
The machine_id is a unique id given to each server, which also has a
server name.
Click to expand...

Each record in a database that may need to be related to other records is
assigned a unique identifier, or primary key (PK). If each server is given
a unique number, and if that number stays with the server, then MachineID
(or ServerID, or whatever you want to call it) is the PK. Whatever the name
given to the server, it can always be identified by its PK. This means you
need a Server table (tblServer).

tblServer
ServerID (PK)
Other information about the server

You also need a QC table (tblQC):
QC_ID (PK)
ServerID (Foreign Key, or FK)
Date
Other information specific to a particular QC operation (but not
specific findings)

And a Findings table (tblFinding)
FindingID (PK)
QC_ID (FK)
Major_Minor (define if a finding is major or minor)
Description
Technician
Other information specific to a single finding

Link the PKs to their namesake FKs as I described in the earlier post.
Build three forms: frmServer, fsubQC, and fsubFinding. Drag fsubFinding
onto fsubQC, then drag fsubQC onto frmServer. If you create a query based
on tblFinding, and sort it by Major_Minor, then by whatever else (if
anything) you choose, you can base fsubFinding on that query rather than on
the table, and you will see the Findings records sorted with all of the
major findings first, then the minor findings.

You may decide to have two QC tables: one for machines being built, and
another for operational machines. Give them different names, and structure
them similarly to tblQC as described above. You can link FKs from several
tables to the same PK.

Similarly, you may want two findings tables (major and minor), or maybe even
a major and minor finding table for each QC table.

My suggestion is that you experiment with a database built with just the
three tables. See how the pieces fit together, then decide if you want to,
for instance, use a separate table for major and minor findings (so that
major and minor can be displayed side by side rather than one above the
other).



I guess linked is the wrong word as machine_id = server
 
Back
Top