How best to set up!!

[Sleepymum]

I would like to set up a database that I can use to record results carried out at different work centres.

My problem is in how best to lay out the tables.

Should I just have 1 main table for each work centre? Or should I have a job table and then link each work centre from here?



Also I am used to setting up a database where only 1 record is being saved at a time. This time I will need to save multiple records back to the same batch, and then the batches back to the job number.

Any recommendations on how best to lay this out???

Thanks a million for your help!

[Rod]

Hi,

From your description I can identify two entities: work centre (ah - love the British spelling) and job. From my experience I know that job probably has a life cycle within the scope of your interest. Thus a job has different states (not statuses!): scheduled, started, cancelled, completed, etc. I now have a third entity: job state.

There has to be many more related entities but for now let's stay with the three we have, so far, identified.


Ignoring all the disciplines and jumping straight into physical database design you need at least three tables:

• tblWorkCentre
• tblJob
• tblJobState

Don't worry yet about multiple updates - it can be done. Concentrate instead on collecting all the attributes for each table.

Are you new to Access? Do you need help defining all this? Post back and we will help.

[Sleepymum]

Sorry I think I may have confused things, and following up with my boss my criteria has changed somewhat…. So this is basically what I would like to get…..

· Results are entered after measurements are carried out. So the job is complete at time of entry.
· We have 2 main types of measurements: “Set up” and “In process”, so I would like to be able to distinguish between them
· There are 2 main work centres/processes. They are different to each other (the same job will never go thru both), and different measurements will be carried out in each. Analysis of results will be carried out on both.
· The “Other” work centres will only have issues recorded for the “Job number” – More like a record/log of any problems that happen (so that we can keep track of them)…..
· Fields common to each work centre would be: Date, Shift, Op, Job Number, Product
· Then each work centre would have its own set of measurements
· Measurements are basically the dimensions of the products – example Length, and some visual attributes –example Fibres
· We take measurements across multiple lines and multiple samples per Batch
· There can be multiple batches in each Job


Hopefully that makes some sense and gives some background into the project.

I have some access experience – Setting up tables, queries, form, reports
But I just seem to be going round in circles as how best to lay this out…….

[Rod]

I don't fully understand.

OK, JobState is not relevant and may be discarded.

Now I hear other entities such as: batch, sample, line, issue, shift, op (operation?), product and measurement. Your scenario has escalated. Perhaps not all of these entities need be carried through to a physical database design.

Er ... the examples of the attributes you give for a work centre do not relate to a work centre as I understand it. You must be able to give a clear, unambiguous description of each and every entity.


For example:

• "A work centre is a physical location where measurements are made on a sample of products."
• "A sample is ..."
• and so on.

(Please don't take my descriptions, I may be wildly wrong, but form your own.)

So what are these 'other' work centres? They cannot be work centres as above but something else.

With all respect, I think you are having trouble because you are not clear yourself.

I can double-guess and describe from my experience what I think. Here goes.

There are one or more production LINES where PRODUCTS are manufactured in BATCHES. One or more SAMPLES are taken from each BATCH and tested at a WORK CENTRE. The TEST consists of a series of MEASUREMENTS against standard. Deviations from standard are recorded as ISSUES.

But I could be completely wrong; for example, you could be talking about a product line and not a production line.

Keep talking and we'll get there.

[Rod]

Hi.

It's gone somewhat quiet.

I followed my own train of thought - which could of course be way off track - and came up with the following ERD.

Attachment 3694

I have not shown an entity for 'Job.'

I guess that you are not interested in formalising Product, Batch or Production Line. So my first cut physical database design is as follows.

Attachment 3695

Product, batch, production line and job all become unnormalised attributes in the Sample table.

I also think there is no need for a Work Centre table per se; work centre may become an unnormalised item in the Measurement table or, indeed, the Sample table if all measurements for a single sample are carried out in the same work centre. There is a table for issues allowing more than one entry for each measurement.

The Test Template table is there because I guess you do not want to keep entering the text or whatever for the same measurement over and over again.

NB The relationship between Test Template and Measurement should of course be one-to-many.

[Sleepymum]

Getting it hard to flesh out exactly what’s needed as the project keeps being changed by the boss.
I have attached a pic of how I think it will look. Buy am open to changes and better layouts.

This is the understanding I have now on what is needed.

Database to store measurements from 1 WorkCentre (1 & 2)
The same measurements are not carried out on each WorkCentre.
The main data entry screen will record issues that occur at other Work Centre’s (but no measurements will be recorded) – This is to be used mainly as a track of production issues
The main table “QA tech” links to Work Centre 1 & 2 thru the Job number.

I am thinking that I now should create a table for the batch number and the inspection type?
If I do this then how do I set up the data entry forms?

I have one set up for the “QA tech database” table, and would like to have on the same entry screen Work Centre 1 and Work Centre 2 measurements, but for each job number I could have multiple batch number and then could have up to 8 different lane entries.

Even reading this back is confusing but hopefully it makes some sense.
Looking forward to your reply and thanks so much for your advice so far!

[Sleepymum]

Also each product has an associated size, although sizes are limited (we may only have 5 sizes, but there would be 100 products associated to that size.
Say product A = 10*20cm
If I entered Length 19.985, this would be within spec limits, if 199.85 was entered this would be out side spec limits.
Can I get a message to pop up saying outside spec limits?


I can send you a copy of what I have put together so fat if that would be of help!

[orange]

If the requirements keep changing, you absolutely need a data model. This will let you associate new entities, attributes before adjusting your physical tables.

There is an old saying, if you can define it, you/I can't design it.
These is nothing like scope creep; changing "requirements" etc to have you fall into the trap -- it's 95% complete. Such projects are never completed.

Perhaps there should be a list of those things to be done in version 1; then a list of would like to haves. Once version 1 is operational, then start setting priorities of things on the list of "would like to haves". Then set the boundary for version2.....

Also, if you haven't done this, a good practice is to define test data and expected outcomes. I know it's tedious,but, if you're building a quality product (hopefully for a long life and not continued daily maintenance (read design changes)), then spend the time to create meaningful test data with outcomes. Ideally get the boss involved,since he seems to know "what the system should be". Test your data against the data model, then test as you build. Any variations between expected outcomes and actual outcomes should be reconciled and appropriate changes made to either the test data, the table structures/relationships or the business process involved.

[Rod]

First, I concur with everything Orange says. It is frustrating and tedious to ‘get it right’ at the beginning, especially when the nature of Access encourages you to leap in and start prototyping from the outset. (There’s nothing wrong with prototyping per se as long as it’s done to test a specific concept.) Believe me when I say, from bitter experience, that time spent getting it right at the start repays you a hundredfold in later stages of your development.

Getting it right means designing a robust, flexible database that fulfils the current requirements and has room for future enhancement. There is no one solution. Unlike data modelling where there is only one ‘true’ model, there are many physical design variations that fulfil your current requirements. I prefer to make my database designs as close to the data model as possible.

The documentation you produce while designing your database may be used to walk through your solution with your boss. It’s a good discipline to explain to yourself – even better to someone else – how you are going to achieve the requirements and roughly what the screens and reports look like. This becomes a verbal (or documented) scope of work. Once agreed, do not be tempted to vary from that scope of work; ‘scope creep’ Orange rightly calls it. I do however sympathise with beginners who don’t necessarily have the experience to imagine how they will achieve the requirement. This is why you came to this forum.

I will describe here one general observation. I feel you are trying to make your database table look like your input/output requirements and like the forms that you may currently use. A good database design follows (more or less) the concepts of data normalisation. Sometimes this means that data you feel belong together are spread across two or more tables. This is not a problem. Forms do not have to be based on a table; they can be based on a query that itself incorporates data from more than one table. Some relational database management systems call these queries, views.

I now realise that the work centres you talk about are production work centres. I had for some reason assumed they were QA work centres. This may explain why I have been a little off course.

Now, if you’re up to it, a commentary on your design.

• You have included spaces in many of your names. This is permissible but I guarantee you will get fed up typing all those brackets. Just concatenate the words and let the capitalisation serve to split the elements of the name. For example QATechnicianID is better than [QA Technician ID].
• There are naming standards that most of us follow most of the time. The convention is called Hungarian and basically tells us to form names as: [prefix]tag[name][suffix]. Notice that tag is the only mandatory component. An exception to Hungarian is that most of us do not bother with tags when naming table fields.
• The prefix, tag and suffix values are given in a standard called Reddick. You will quickly become familiar with the more common tags. (tbl = table, ctl = control, lng = long and so on)
• I feel the QA Technician Database is misnamed. Accurate names help us when we return to a project later on and certainly help others in understanding your intentions. Looking at the table contents I believe this is a QA/QC test (where my definition of test is a series of measurements). If I’m right, why not call it tblQATest?
• Many of your field names end in ‘ID’ (uppercase). A convention is to reserve an uppercase ‘ID’ suffix for fields that are an Access autoincrement long number or foreign keys related to an autoincrement. If they are not autoincrement, can you find alternate names?
• The table is keyed on Job Number. Is this your job number or a production job number? I assume it’s your job number otherwise you may only have one test per production job. I personally subscribe to the convention of having one ‘meaningless’ key as the primary key for each table. Thus I would have a field named ‘ID’ as primary key of an autoincrement type. Job Number becomes just another attribute on the table but you can specify that it has an index and is to be unique.
• Date is a reserved word. Avoid it for field names.
• Issue Resolved – presumably a Y/N field – looks out of place unless Comments is where the Issue is recorded. If so this design only allows for one issue per test. OK, you could record more than one issue and number them but then when do you decide the issue is resolved? I think your design would be more flexible if you had a separate tblIssue. More on this later.
• The foregoing comments also apply to some degree or other to the two work centre measurement tables.
• Batch Number belongs in tblQATest; move it there. This together with a ‘meaningless’ primary key solves your multiple batch concern. (Also Lane ID?)
• Can you arrange an out-of-size pop-up? Yes, but you need now to introduce a product table of some kind where the tolerances are specified.

OK, here’s my design. I have followed your thoughts as closely as possible; no way is this a fully normalised design but it will work and has potential for enhancement.

Attachment 3724

tblQATest, tblMeasurementsWC1 and tblMeasurementsWC2 are very much like your design. However note that the relationships are now one-to-one and not one-to-many. The existence of a related row in either measurement table is optional but if a row exists then only one row is possible. These rules have to be implemented programmatically, the database manager cannot enforce them for you.

tblTestPlan provides the information for this programmatic control. When the user enters a work centre the table is accessed. If the TableOfMeasurements field is empty then the program follows an issue-only procedure. If the field contains a table name then the appropriate form is displayed allowing entry of the measurement results.

tblProduct is there to store tolerance information. The trapping of a measurement for tolerance checking has to be done programmatically.

Issues are recorded in tblIssue. Note that tblTestIssue allows for more than one issue for each test.

The ‘strange’ relationship between tblIssue and tblIssue_1 is to provide for a daisy-chain of follow-ups for any issue. You may or may not want this.

That’s enough for now. Let us know how you are getting on.