Attempting to design database around Excel datasheet design

[CBartman]

Posting question as last resort - been using this forum for long time with
wonderful results - great job people.

Bleery eyed search - finding no viable solution.

Labor Time Guide -
Problem: (I guess I would call this a ) 3 dimensional array in an Excel
sheet, with the cell formula being "=if(A1 = "A", "number",if(A1 = "B",
"number",if(A1 = "C", number, "number not found")

"A1" being equal to a user selected list box result (A-Z), which results in
a cell value equal to a labor time ("number").

"Machine" (ModelIndex) <has> many "Systems" / "Systems" <can belongs to> man
different (ModelIndex) "Machines"

"System" <has> many "Groups / "Groups <has only> one "System"

"Groups <has> many "Components" / "Components" <has only> one "Groups

(What you can DO to this component) "JobCode" is a predefined code list
(A-Z) applicable to ALL machines (ModelIndex).

Not every component uses ALL "JobCodes" (Hence the formula to calculate only
the "A1" "JobCode" that pertains to the component, else "Not apllicable"
message)

Excel Array: Left column lists ALL possible components on ALL (ModelIndex),
which is a combination of selected "System|Group|Component" (no "nulls").

"Header column" lists ALL possible Machines (MachineIndex). Not all Machine
use ALL components (many "nulls").

What I visualize here is a table for every ""JobCode" (A-Z), with the fields
being:
Table "JobCodeA" - ComponentCode | MachineCode | LaborTime
Table "JobCodeB" - ComponentCode | MachineCode | LaborTime
etc, etc...

Massive redundancy, massive "nulls", every table would inculde ALL
components and All Machine Codes... Hmmm. (not very efficient)

The only difference in the tables would be the time entered, based on the
table name. Must to be a better way.

The tables for selecting the model of machine, the system, the group, and
the component went well, but populating records with LaborTime is based on
JobCode, which is where I'm stuck.

Excel allowed me to put a formula (above) in "LaborTime" cell, where I could
display "LaborTime" based on "JobCode", using only the JobCode that pertained
to THAT component on THAT machine. (No redundancy, some (acceptable) "nulls").

Labor Times may change in the future (Table Update).
Requested expansion to many other machines. (Migrating to DBMS from Excel).

LaborTime table design? Duh...dazed and confused.

(Sorry this ended up being so long)
"Subject should be a brief, meaningful summary of your question or comment"

Thoughts?

 

[tina]

different (ModelIndex) "Machines"

solution: three tables, as

tblMachines
MachineID (primary key)
MachineName
<other fields that describe a specific machine only>

tblSystems
SystemID (pk)
SystemName
<other fields that describe a specific system only>

tblMachineSystems
MachSysID (pk)
MachineID (foreign key from tblMachines)
SystemID (fk from tblSystems)
<other fields that describe a specific machine and specific system
combination>

relationships are
tblMachines.MachineID 1:n tblMachineSystems.MachineID
tblSystems.SystemID 1:n tblMachineSystems.SystemID

"System" <has> many "Groups / "Groups <has only> one "System"

solution: add another table, as

tblSystemGroups
GroupID (pk)
SystemID (fk from tblSystems)
<other fields that describe the group for a specific system>

relationship is
tblSystems.SystemID 1:n tblSystemGroups.SystemID

"Groups <has> many "Components" / "Components" <has only> one "Groups

solution: add another table, as

tblComponents
ComponentID (pk)
GroupID (fk from tblGroups)

relationship is
tblGroups.GroupID 1:n tblComponents.GroupID

(What you can DO to this component) "JobCode" is a predefined code list
(A-Z) applicable to ALL machines (ModelIndex).

solution: add another table, as

tblJobCodes
CodeID (pk)
CodeName

Not every component uses ALL "JobCodes" (Hence the formula to calculate only
the "A1" "JobCode" that pertains to the component, else "Not apllicable"
message)

solution: add another table, as

tblComponentJobCodes
CompCodeID (pk)
ComponentID (fk from tblComponents)
JobCodeID (fk from tblJobCodes)
<other fields that describe a specific job code and specific component
combination>

relationships are
tblComponents.ComponentID 1:n tblComponentJobCodes.ComponentID
tblJobCodes.JobCodeID 1:n tblComponentJobCodes.JobCodeID

What I visualize here is a table for every ""JobCode" (A-Z), with the fields
being:
Table "JobCodeA" - ComponentCode | MachineCode | LaborTime
Table "JobCodeB" - ComponentCode | MachineCode | LaborTime
etc, etc...

no, no, bad idea. Excel utilizes a flat file design. Access was specifically
created to support a *relational* design. the two structures are very
different, so if you try to impose a flat file structure on Access, you're
going to lose much of the power of the software and have a horrendous time
trying to develop an application that works right.

Massive redundancy, massive "nulls", every table would inculde ALL
components and All Machine Codes... Hmmm. (not very efficient)

not at all efficient, and a nightmare to develop, maintain, and expand as
needed.

Excel allowed me to put a formula (above) in "LaborTime" cell, where I could
display "LaborTime" based on "JobCode", using only the JobCode that pertained
to THAT component on THAT machine. (No redundancy, some (acceptable) "nulls").

Labor Times may change in the future (Table Update).
Requested expansion to many other machines. (Migrating to DBMS from Excel).

LaborTime table design? Duh...dazed and confused.

you haven't provided enough information about how LaborTime relates to the
rest of the data - is each value related to a JobCode only? or does the
value vary based on other variables as well as (or instead of) the JobCode?
so i can't suggest anything here.

also, you began by using the term "Machine" as seemingly interchangeable
with the term "ModelIndex", but much later used the term "Machine" and
"Machine Code" as seemingly interchangeable. so i'm lost; i don't know if
you're using different terms to describe the same entity, or if you're
describing distinct entities with each term.

at any rate, i strongly recommend that you read up (or read more) on
relational design principles. i think you're going to be the best person to
model your process, once you understand how to do it correctly for a RDBMS.
for more information, see http://home.att.net/~california.db/tips.html.
suggest you read at least the first two tips, though you'll find the next
seven helpful as well when you begin building the tables/relationships
structure.

hth

 

[CBartman]

My Goodness, thanks for the timely response (figured I'd be waitin' till
after the holiday).
Still piecing all the tables together per your suggestions. When mapping out
all the tables in Visio, I noticed a lot of M:N relationships. Your
suggestion seems to have addressed that issue.

If I can master this application, it will be expended greatly (additional
company divisions), so I appreciate your insight into expandability.

End application will be quite simple: What machine did you work on, what
area of the machine was worked on, what physical component part failure was
addressed, and what did you DO to it to solve problem? Based on users
selections, application returns published labor time stating how long this
procedure should have taken.

Thanks again.

 

[tina]

well, you're welcome - though i hope you're taking my design suggestions
with a grain of salt, and reading up on relational design so you can be sure
to do it right (rather than just doing it the way i suggested). good luck
with it, and happy holidays!

 

[Jamie Collins]

Access was specifically
created to support a *relational* design.

no, no, bad idea. Jet is founded on SQL DBMS. Relational theory and
SQL DBMS are as different (perhaps more so) than Access vs Excel e.g.

some (acceptable) "nulls"

nulls are not acceptable to relational theorists.

Good luck trying to impose a pure relational design in dirty old SQL
<g>.

Jamie.

--

 

[Tony Toews [MVP]]

nulls are not acceptable to relational theorists.

Then I suggest theorists get out of the way of those who have to
implement real world solutions.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[CBartman]

Hi there,

[you haven't provided enough information about how LaborTime relates to the
rest of the data - is each value related to a JobCode only? or does the
value vary based on other variables as well as (or instead of) the JobCode?
so i can't suggest anything here.]

This particular area is the snag I'm having. Sorry if I didn't "splain" very
well. The rest of the table designs are very similar to what I was piecing
together, other than the M:N relationships. (I finally have my brain wrapper
around how to utilize the "composite" tables, thank you).

The final product will return a "LaborTime" based on a specific code derived
from "tblMAIN, tblGROUP, & tblCOMPONENT tables, user selected. Once we know
WHAT part (component) was addressed, now I need to return a "LaborTime" based
on the "JobCode" (what did you DO to the compnent, codes A-Z). So far so good.

Problem I can't quite wrap my brain around: LaborTime might be different for
each MACHINE, even with matching (combined) component code and matching
JobCode.

i.e. (code B) = "Replace" , (code A03B [A+03+B] "alternator belt", on
MACHINE "A" might be 2.5 hrs, Same Codes ("Replace", A03B) on MACHINE "B"
might take only 1.5 hrs.
(Code D) = "Adjust" might have posted times, again, different for each
machine.

I say "might" because; 1- not all components are on ALL machines (electric
truck doesn't HAVE an engine, therefore any "engine component codes will not
apply), and 2 - not ALL JobCodes apply to ALL components (you may have an
engine, but you CAN'T "adjust" it).

LaborTime table - must be able to match "ComponentCode", "JobCode", AND
"Machine" code to arrive at / return a published LaborTime.

I was successful at creating this scheme in Excel, being able to include
"JobCode" in a formula directly in the cell. (each component has only approx
5 "JobCodes that actually apply - nested "IF" statements)

I'm not taking credit for the code scheme, nor labor times we're using
(although it seems to be working out relatively well). I would like to (was
requested to) design a more suitable data access method. VBA in Excel is
working well at this time, but I do anticipate this "ComponentCode" /
"LaborTime" schema to be migrating to other divisions in the company.
Potentially thousands of components, machines, labor times.

I need help understanding what the "tblLABORTIME" layout would look like.

Yes, I'm still reading: "Database Systems - Design, Implementation, &
Management, 5th edition" (Rob Coronel - 2002) Was a good course, although I
don't claim to be fluent.