I am designing a database system + application that deals with pipe objects who are part of a larger grid network. There are about 1000-5000 pipes for each network. Let's assume each pipe can have 2 states (clean, dirty).

The application is able to change the pipe status after a maintenance job has been done at that pipe. Each maintenance job has an ID and several other information behind it. The same is true for the pipes.

My design for this problem is as follows:

table: pipe_status

id     maintenance_id         pipe_id        status
1      1                      1              clean
2      1                      2              clean
3      1                      3              dirty
4      1                      4              dirty
1000   1                      1000           dirty
1001   2                      1              clean
1002   2                      2              clean
1003   2                      3              dirty
1004   2                      4              clean
2000   2                      2000           dirty

So for every maintenance job, each individual pipe should have a status attribute, depending on whether they have been affected by this particular maintenance job or not. This means that for every maintenance job there are as much status entries as there are pipes, resulting in a quickly growing amount of data.

Example: 20+ grids with 1000-5000 pipes. 500+ maintenance jobs per grid and growing, results in 10-50 million entries in this table at the moment.

Is there better way to implement this problem in a database? It is important to note that the status of each pipe is visualized in the application, so even if a maintenance job only affects 5 pipes, the other 995 are still shown in the app. In this application the user can select a specific maintenance job and see the corresponding pipes and their status.

  • 1
    I guess I don't understand your problem. You said 10-50 million entries. If you had 20 grids with 5000 pipes each, that's 20 * 5000, or 100000. The 500 maintenance jobs are distributed amongst the existing grid entries, right? Aren't you just changing the status of existing entries? – Phil N DeBlanc Oct 18 '18 at 9:19
  • 3
    Do you have to store records for pipes that weren't affected by the maintenance job? And if so, why? Can't you get all the info you need to display unaffected pipes in the UI from the pipe table (where pipe_id comes from)? – Ken Halbert Oct 18 '18 at 21:17
  • Why would you have new entries for every pipe for one job? Wouldn't the Maintenance job know which pipe it had to clean and just update that one pipe? – johnny Nov 2 '18 at 20:49
  • Is this a homework assignment? Any real system for this would keep track of what work was done when. The status of clean or dirty is a snapshot in time. Without knowing when that assessment was made, it's meaningless. – JimmyJames Nov 2 '18 at 20:59
  • Since the OP seems to have abandoned the question, and this question requires further information to be answered in a sensible manner, I think it should be deleted by a mod. – Doc Brown Jan 2 '19 at 7:03

Based on my understanding of the problem, one could see that this is a typical M-M association between a pipe and maintenance job.

  • Each pipe is maintained on zero, one or more maintenance jobs.

  • Each maintenance job affects one or more pipes.

This calls for creating an intermediate table for resolving the M-M association (sometimes called a junction table). This table would include among other attributes the following:





You say that "for every maintenance job, each individual pipe should have a status attribute" This could mean that after every job some of the pipes will be cleaned, some will remain dirty. Instead of inserting the status of every pipe with each job (which is logical but will be very inefficient to process), we could decide to limit the scope of contents of the table to "cleaned" pipes only. Accordingly, you will not need to store the status value in each row since it would be "clean" for all rows. Appropriate indexes must be created on the ID and timestamp columns of course.

This will save time in insert processing, however, it will affect the processing of inquiry.

so even if a maintenance job only affects 5 pipes, the other 995 are still shown in the app.

For this case, you will need to select the cleaned pipes IDs, set the status of those to clean and select the rest of the pipes (all except the filtered ones) and assign a "dirty" status for them.

This should be very fast because, you will use an index to get the affected pipes, and you access the entire table of pipes sequentially (maybe ordered by pipeID) which should be very fast as well. The intersection processing could either be via SQL or by programming logic. In either case, it should not be a big deal.

To complete the logical view here, the program should make an assertion that every pipe not appearing on the new table has the status of "dirty".

In general, we can only judge whether this mode is good or bad based on not-only its performance but also on its ability to respond to "all" expected inquiries. For example, what if you wanted to know the status of a given pipe between 2 dates, or list the status of each system pipe across different months...etc. All such queries must be considered before deciding on the model to use, this is so because you have fast growing data.

I assume that based on the limited information provided that this approach is better than the approach where every pipe has to be inserted with a status for every maintenance job, because this approach will limit the number of rows and I assume that it could perform better.

Maybe testing the the approach will give a sold proof, a test should be easy to set up for this case.

Good luck.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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