0

I am working on a checklist app for inspection sheets for users on our shop floor. It's a paper process currently and they want to digitize it. I am using Postgres SQL for the database. I started out just making tables as I go but then they told me the number of inspections is over 100 so I'm hoping to find a more efficient way to do things.

to start, we have different units that inspections fall under. For example, 1621 is a unit and we have I think 19 units. Within each unit there are at least 15 inspection sheets (assembly, roof enclosure, generator assembly, etc) and they have a different number of questions in each one. There is some overlap to the the inspections per unit (what I mean is most of the units have a generator assembly, roof assembly, and some others) but the questions for the inspection can/are be different per unit.

I started by making 6 tables for 3 of the inspections before they told me about the units portion of the project. So I had a table with the questions for an inspection and a table for the submissions. I really don't want to go about it this way but if that's the best option I will stick with it.

Can anyone guide me towards some already created schema's I can build off of or resources to read/watch/etc that may help?

2
  • This answer and the links included therein may be of some help. Feb 19 at 12:01
  • Schema design is an iterative process - have a look there and links within! You start with design 1, code, test, fix errors (and there will be errors), produce design 2... - rinse and repeat.
    – Vérace
    Feb 28 at 10:18

2 Answers 2

0

So, to summarize, you have units, general inspection sheets (questions) and answers for specific inspection sheets and specific units.

There is a many-to-many relationship between inspection sheets and units. A unit may have to comply with multiple sheets, and each inspection sheet is typically used for many units. This kind of relationship leads to an additional table in the Entity Relationship diagram.

Therefore, I would do something like this:

  • Unit table: unit ID, maybe address, contact,...

  • Sheet table: sheet ID, Question 1, Question 2, Question 3,...

  • UnitSheet table: unit ID, sheet ID, Answer 1, Answer 2, Answer 3,...

The only possible pain point is that different sheets have a different number of questions, and the relational model requires a fixed structure for all rows in the same table. Therefore, using this approach the Sheet table would have a number of columns equal to the maximum number of questions in an inspection sheet, although any question field can be optional (NULL).

You might want to investigate document-oriented databases, but at this point it may not be necessary.

0

Any database structure where each "item" ends up as a column will be a nightmare. The reason is the database structure is rather rigid and also married to your code. Even worse if you use one table per sheet, you will need to duplicate and edit your application code for each sheet, which dramatically multiplies the amount of work.

When designing a database schema, you must normalize it: adding new stuff must mean adding rows in tables, not changing the table structure with new columns (or worse, new tables).

This way, your existing code will work fine when adding new sheets or questions, because the structure is the same, only the data is different. All you need is application code that can handle generic sheets of questions, and it will work.

Model your questions

I suppose you'll have yes/no questions, like "top left plug door bolt is tightened to ... Nm".

Also numeric questions like "measure idle rpm/hydraulic pressure/whatever" and report the number, then check it is within a specified interval.

Maybe some questions need a free-form text answer.

Maybe some are multiple-choice.

If you have other types of questions, the time to think about it is now. Same if you would like to include images in the questions (for example a drawing showing where the bolt should be).

For numeric questions, we could add min/max columns to store the valid range, and of course units.

Table questions:

question_id      INT PRIMARY KEY
sheet_id         INT REFERENCES sheets(sheet_id)
question_number  INT
question_label   TEXT   
question_image   INT  
question_type    ENUM   checkbox, numeric, text...

measurement_unit       TEXT
measurement_range_min  DOUBLE PRECISION
measurement_range_max  DOUBLE PRECISION

For multiple choice questions, you'd need a separate table to store the different options, referencing question_id. Or you could put it in a TEXT[] array in the questions table too.

Table questions_multiplechoice:

question_id    INT REFERENCES questions(question_id)
choice_id      INT
choice_label   TEXT
PRIMARY KEY (question_id,choice_id)

Sheets

Questions are grouped into sheets, so we have to decide whether we use a one-many or many-many relationship.

With a one-many relationship, one sheet contains many questions, but one question can only belong to one sheet. Therefore to reuse a question in two sheets it has to be duplicated.

With a many-many relationship, one sheet contains many questions, and one question can belong to many sheets. Thus questions can be reused. Modifying a question will change it in all sheets that use this question.

In my opinion, in your case, one-many would be preferable because you will probably have near-identical but slightly customized questions on each sheet, and you definitely want to avoid someone modifying a question that is used in several sheets while only intending to modify it for one sheet. So let's go with one-many.

Table sheets
sheet_id    INT PRIMARY KEY
sububit_id     references the unit this sheet is about (you'll need a table for subunits)
... several columns like sheet description, an image, some text...

To implement the one-many relationship, I have added sheet_id to table questions, and a question_number column, both to order the questions as you want and to refer to a technical drawing or form where the items will be numbered.

Inspection

Let's create a table "procedures" to group sheets into an inspection procedure.

As you said a unit can be composed of several subunits (generator, etc) so perhaps you want to reuse sheets between inspection procedures, which means this time you'd need a many-many relationship, it's up to you.

If you want a many-many relationship, then you need an "inspections" table:

procedure_id
unit_id
...

and an "procedures_sheets" table:

procedure_id
sheet_id
sheet_number

One line in this table means the sheet_id belongs to this inpection_id. You also need an order number to display them in the right order.

Model your answers

With the above, you can display the complete questionnaire. Starting from an inspection procedure, you can list the sheets and questions. If you use web as user interface you can use a form library to build forms on-demand.

But you can't record answers yet, so let's do that.

When the inspector starts an inspection, we need to record that. Thus we need a table for that, for example table "inspections" with the serial number of the unit being inspected, its type, date, inspector name, etc. And of course the id of the inspection procedure that was done.

Then we need a table "inspections_sheets" in which a row is inserted every time the inspector does a sheet.

And finally a table "inspections_answers" in which answers are recorded.

If the procedure is changed, or questions modified, this must not influence recorded inspections using the previous version of the procedure, because that would be hell for proper record keeping. So you have two options. Either never modify the inspection procedures (instead, create a copy as a new version), or include a copy of the inspection procedure the way it was when the inspection took place.

The second is simpler and safer. This is how it's done on e-commerce website: when you buy a product, a snapshot of the product price, description etc is included in the recorded order. This way, if the seller changes product description or price, your recorded order still has the exact information about the product you actually ordered, not the new version.

So, in the above tables, add columns to keep a copy of the row of the corresponding inspections, and sheets.

For example, table inspections_answers should contain:

inspection_id
inspection_sheet_id
inspection_question_id

...plus a copy of the row in the questions table, and the actual answer.

Since the answer is going to be either boolean, numeric, text, or multiple choice, you can have one DOUBLE PRECISION column which will be 1 or 0 if it's a checkbox, or the numeric value, or the id of the multiple choice, or NULL if the answer should be text. Then add a TEXT column for questions that are answere by text (example: Question "Visual inspection:" answer "dent on left side panel").

I think this text column should always be available even if the question expects a numeric answer, for example the inspector could write "I can't torque the plug door bolt because no bolt is installed."

This also implies there should be a way to explicitly fail a question or a sheet when an anomaly occurs that does not fit the form but is important, so perhaps add a bool column "explicit_fail" or something like that.


OK. From your question you sound a bit inexperienced so this may appear a bit complicated or even overwhelming. But I assure you, you must do it. If you keep going in the way you're going, you'll end up with 100 different tables, each having its own custom application code file, each needing custom manual special treatment, which is a recipe for endless suffering.

I recommend finding a good tutorial on database relations, and doing it right. It takes longer to setup, but once it's done, it just runs and adding more sheets and questions is very little work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.