UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Table/design Feedback, Access 2013    
 
   
clavalla
post Apr 16 2019, 04:45 PM
Post#1



Posts: 36
Joined: 7-January 16



Hi everyone,
I'm developing a DB for use in our shop to track maintenance of parachutes. The DB only has 4 tables, one a list of available programs, one of parachute types, one for unique parachutes and their info, and another for storing the maintenance information. The end result is that our maintenance being done can be tracked by program for billing purposes and also be able to track and see how much outstanding work (hours) is left to do on each program and then see how much work was done on a parachute over its life.

I've attached the relationships screen shot and I would appreciate any feed back on the issues/questions I have.

One issue is that there could be multiple different parachute types with the same serial number, so as designed now would that cause an issue with making a form for the maintenance tracking? I'm thinking main form with project and subform and then will it be possible to filter the available info in a subform based off of parachute type, to then assign the hours to that serial number of that parachute type?

Another question I have is in the design of the table and also a form question, what would the best way to design it so that I can track how many hours are still awaiting maintenance. The work flow in the shop is a parachute gets inspected and hours of required maintenance is determined, the work is done, and then a final inspection is completed and work is closed out and charged to the program. Any suggestions on way to handle that, my initial thoughts were to just use 2 check boxes of "initial" and "final" and filter my results, if it doesn't have a "final" then its still awaiting hours. Thanks for any constructive criticism on this project.


Attached File(s)
Attached File  Capture.PNG ( 22.38K )Number of downloads: 14
 
Go to the top of the page
 
tina t
post Apr 16 2019, 11:26 PM
Post#2



Posts: 5,963
Joined: 11-November 10
From: SoCal, USA


hmm, well, first and foremost - IMHO - is that you haven't enforced referential integrity between your tables. so the lines linking the tables are just that: lines. they don't do anything.

next, some questions for you. and remember, there are probably a whole lot of us here who don't know anything at all about parachutes except the obvious - that they (hopefully) keep you from falling to earth and being splatted like a bug hitting a windshield, after you fall out of a plane. so:

1. what's a program?
1a. what's a project?
1b. and how do you differentiate between a current project and a past project?
1c. and where are you keeping info, if any, about projects?
2. how are programs and projects related to each other?
3. how are programs and projects related to maintenance?

i don't want "database" answers to these questions, pls. programs and projects are real-world entities in your business model. to successfully support them and their interactions with other entities in the model, you have to define them and understand what they have to do with other entities in the model. so tell me a (reasonably short) story.

next, you have the Program field in tblPrograms linked to a Project field in tblMaintenance. that's confusing, when you also have a CurrentProject field in tblPrograms. and it's likely moot, because you should be using the primary key field in tblPrograms as the foreign key field in tblMaintenance - assuming that the entities Projects and Maintenance do actually have a one-to-many relationship with Projects as the one-side parent table, and Maintenance as the many-sided child table. we won't know that for sure until we have a basic understanding of those three entities.

ditto tblParachutes and tblMaintenance: you have a primary key designated in tblParachutes, but you're using the SerialNum field in both tables to link them. it should be the pk field from tblParachutes that's stored as a foreign key field in tblMaintenance.

from tblParachuteTypes, you're linking the pk field ParaType into tblParachutes on field ParaType. okay, pk to fk (foreign key), looks reasonable. but here you say

QUOTE
One issue is that there could be multiple different parachute types with the same serial number

your current table setup won't support the relationship you describe above. and more questions:

4. a serial number is not specific to a parachute? (like a VIN on a car is specific to that car, and serves to uniquely identify that instance of the car entity.)
5. if a serial number is not unique to a specific parachute, how do you identify individual parachutes over time?
6. and if you can have multiple parachute types for a single serial number, then we really need to understand what parachute types are - in the real world - and what parachute serial numbers are and what their purpose is, again in the real world.

QUOTE
Any suggestions on way to handle that, my initial thoughts were to just use 2 check boxes of "initial" and "final" and filter my results, if it doesn't have a "final" then its still awaiting hours.

that's a flat-file setup, not relational. the telltale sign is that it stores data ("initial", "final") in fieldnames. i'm not saying it's never done, especially on a small scale of two opposing fields (it must be one or the other, can't be both), because it seems quick and easy. but a large percentage of the time, you're going to regret taking that shortcut, even if you're convinced at the outset that it will never happen. recommend you bite the bullet and set it up relationally: put the data into a table (tblStatuses), one record for each of the two statuses you've currently identified. link that table's pk field into tblMaintenance as a foreign key field. later, when you decide you need to designate a record as more than just started and finished, you'll be so glad you did it right the first time.

a final note. looks like you allowed Access to create the tables, with each pk field named the default ID (except for tblParachuteTypes). that gets miserably confusing when you're working with multiple tables in a single query, or in code (you'll get there eventually). recommend you give each ID-named field a more table-specific name, such as

ProgID
PTID
ParaID
MaintID

something along the above lines will make it clear what ID field (from what table) you're working with, at any time. you'll notice that i went ahead and included a separate ID field for tblParachuteTypes. i assume that the pk field Paratype is a Text data type. using a text field as a table's primary key is not wrong, per se, but can become a PITA at times. my personal preference is to use only Number data type (including but not limited to the Autonumber data type) for all my primary key fields. that kind of feeds into the great (inconclusive) debate over "natural" keys vs "surrogate" keys. i'm not about to get involved in that discussion; if you're interested, you can no doubt find many many links in an internet search.

hth
tina
This post has been edited by tina t: Apr 16 2019, 11:33 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
clavalla
post Apr 17 2019, 09:42 AM
Post#3



Posts: 36
Joined: 7-January 16



Tina T,
Thanks for the answer, there is a lot of good info in here. I'll start by answering your questions. So a program/project is interchangeable, I had first labeled everything project, but renamed everything program but forgot a few field names. I've changed everything to Program for naming conventions.

1.) Background: a customer hires us to conduct testing with parachutes, we set a up a test program for them and set a name for that program so everyone knows what the test is. If any damage happens to the parachutes, we charge the program(customer) for the repair work. Why don't I call it customer? We often have the same customers with multiple programs that all have their own pot of money so I need to track by program. That is a program.

1a.) Explained, oversight on my part. Everything will use program naming convention.

1b.) In the DB for filter results my plan was to use a yes/no field or similar to update programs. In the tblPrograms once the testing is complete and all maintenance is done and program is complete, it will be set to "no" as an active program and it will no longer be available for selection in the form for maintenance work.

1c.) I don't need to keep any info in the DB about the program. The name of the program will suffice for tracking how many hours of maintenance was done at the end, and how much is remaining until it is complete.

2.) explained

3.) explained, I think.

4.) A serial number is unique to that type of parachute, but not among ALL parachutes. We currently have about 15 types of parachutes that have different uses.

5.) That is one of the questions I had about designing the DB, as a parachute can only be identified uniquely by both Type and Serial number and its Date of Manufacture (DOM in the DB). We currently do everything with paper, and we don't really track the amount of maintenance hours over the lifetime of the parachute. That is one of goals of the DB, to be able to track the total hours of maintenance done over the life span of a parachute, which we currently don't do.

6.) As explained above we have about 15 different types of parachutes, of different design and size, that have different uses and applications. Each canopy when manufactured has a unique serial number for that type of parachute, but there are different manufactures of the different types. This leads to the same serial number being used on different types.

I took your advice and renamed the PK in the fields so they aren't just ID, as Access had default named them. I will also take your advice on making a status table, or if there is any other suggestion to how to keep track on how much maintenance is still awaiting vs. complete I'm all for suggestions. I hope these answers clarify what I'm trying to do and what I'm trying to accomplish. If there are any others, after this let me know.
Go to the top of the page
 
tina t
post Apr 17 2019, 08:21 PM
Post#4



Posts: 5,963
Joined: 11-November 10
From: SoCal, USA


QUOTE
a parachute can only be identified uniquely by both Type and Serial number and its Date of Manufacture (DOM in the DB)

hmm, okay. so this parchute sitting in front of me is Type = A and Serial number = 111 and DOM = 4/10/19. I might also have another parchute sitting in front of me (big desk!) with Type = B and Serial number = 111 and DOM = 4/10/19. but i could never have two parachutes sitting in front of me, both with Type = A, Serial number = 111, and DOM = 4/10/19. are these statements correct? could i ever have two parachutes sitting in front of me, both with Type = A and Serial number = 111, leaving out any consideration of DOM? in other words, is the DOM an essential part of the unique identification of the parachute?

another thought that occurs: might you test a specific parachute for Customer A, then customer A sells/gives/whatever that specific parachute to Customer B, who then sends it to you for a test at some point? And if that can happen, do you want to be able to track what tests were performed over the life of a specific parachute, regardless of who-all might have owned it over that life? the answer makes a difference in what records belong to what entity(s).

QUOTE
a customer hires us to conduct testing with parachutes, we set a up a test program for them and set a name for that program so everyone knows what the test is. If any damage happens to the parachutes, we charge the program(customer) for the repair work.

so more questions: does a program refer to a single test on some number of parachutes? like, Program A is test R on 10 parachutes for Customer X? or can a program include two tests (or three, or...) on 10 parachutes for Customer X? or maybe Program A has test R on 5 of the parachutes and test T on 8 of the parachutes, so a few parachutes get both tests, and some get one or the other - all in Program A? these points need to be considered if you want to track testing/repairs on a parachute over its' life, and if you want to track whether or not all testing has been completed for a program. if you want to have specific info to slice/dice different ways, you have to track that level of granularity in your tables in the first place.

your setup so far seems to be excluding any details about programs or customers. can we assume those details are stored in some other program in your business model? if those specifics are outside of the intended scope of the proposed database, then so be it. otherwise, you need to consider how to house that data as well.

the above gives you a fair idea of how detailed and tedious a process analysis can be, and how important it is to get the details right, before you begin designing the tables/relationships for a relational database. it's not something best conducted by remote contact such as a forum board, and really impossible to do it well in such an environment. at this point, my questions are not so much for you to answer me, but for you to consider for yourself. process analysis is a learned skill - partly by reading instructions on how to do it well, and partly by doing it, and doing it, and doing it - you learn from experience. my point here is that when you have conducted a thorough process analysis, the table design pretty much falls into place, because you'll already have (most of) the answers you need to design a strong and flexible data model that will support your real-world business process.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
clavalla
post Apr 18 2019, 09:45 AM
Post#5



Posts: 36
Joined: 7-January 16



tina t,

Your first statements are correct, and DOM is not an essential part of the unique identification. As to your second statement, that can't happen. We own all the parachutes, but they are used on different tests, so yes, tracking what projects a parachute has been used in would be part of the data stored. I guess that is the crux of where I'm stuck at in designing the tables and DB, how do I set up tables to track items that require two sets of info (parachute type, and serial number), I thought has occurred to me while we've had this discussion of when inputting the parachutes info into the DB that maybe I join the info to make a unique identifier for that parachute. An example, a parachutes type is known as a G11 and its Serial Number is 12357, another parachute is known as a T11 and its serial number is 12357 I could join those into G11-12357 and T11-12357 in a field for that parachute and then I have one unique identifier. The thought hadn't occurred to me until having these talks with you. I'm assuming this would be possible?
Go to the top of the page
 
tina t
post Apr 18 2019, 11:01 AM
Post#6



Posts: 5,963
Joined: 11-November 10
From: SoCal, USA


yes, it would be possible, not not desirable and not necessary. you need to read up (or more) on relational design principles, hon. you keep making suggestions that go against those guidelines. i'm not criticizing you, i'm saying that you need more information so you have a better understanding of the choices you need to make, and the ramifications of those choices.

QUOTE
I guess that is the crux of where I'm stuck at in designing the tables and DB, how do I set up tables to track items that require two sets of info (parachute type, and serial number)

ok, you already have a tblParachutes, which i assume is a list of all the parachutes your company owns. you have a tblParaTypes, which i assume is a list of all the various types of parachutes that are made (or that your company tests, anyway). so, consider the following design, as

tblParaTypes
PTID (primary key, Autonumber data type)
TypeName (Text data type)
<other fields that describe a specific parachute type, as needed, but no fields that describe any particular parachute>

tblParachutes
ParaID (pk, Autonumber)
SerialNum
PTIDfk (foreign key from tblParaTypes. putting fk at the end of the field name is a commonly-used naming convention that makes it easy to spot a pk field and its' matching fk field, in multi-table queries. it's not a written-in-stone convention; my personal naming convention happens to be different.)
DOM
PIS (i assume this is a piece of data that describes a specific parachute)
InService (a date/time field, am i right?)
<other fields, as needed, that describe a specific parachute, but no fields that describe anything you do to/with a parachute>

now, ParaID is the primary key of tblParachutes. it uniquely identifies each record in this table, to the computer. that's its' purpose, and it has no other purpose. to make sure that each record identifies a unique parachute, use both fields SerialNum and PTIDfk as a single unique index in the table properties. so each field by itself may have duplicate entries - multiple records with the same value in the field. but the two fields together cannot have the same value in more than one record.

by following the normalization rule of keeping data atomic - one value per field, no more - it becomes much much easier to slice-and-dice your data to get whatever information you need from it. using a multi-field unique index at the table level (and Access allows up to ten fields in a single index, IIRC) supports both the normalization goal and the goal of identifying a unique instance of an entity, when there is no atomic (single) value that will do so.

again, really, i strongly encourage you to stop and study up on relational design principles before you go further. it will be time very well invested, i promise you. there is a beginner's reading list here at UA, compiled for just this purpose. the link is on the Home page, i believe.

hth
tina
This post has been edited by tina t: Apr 18 2019, 11:04 AM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
gemmathehusky
post May 22 2019, 04:51 AM
Post#7


UtterAccess VIP
Posts: 4,693
Joined: 5-June 07
From: UK



QUOTE
One issue is that there could be multiple different parachute types with the same serial number,



not sure what you mean. do you mean a serial identification number, or a manufacturers part number for the parachute.
Clearly you need a way to identify a specific parachute. If a serial number is not unique, then it's no good.




--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th June 2019 - 12:49 PM