Manufacturing process cycle time database design

I want to create a database to store process cycle time data. For example:

Say a particular process for a certain product, say welding, theoretically takes about 10 seconds to do (the process cycle time). Due to various issues, the machine's actual cycle time would vary throughout the day. I would like to store the machine's actual cycle time throughout the day and analyze it over time (days, weeks, months). How would i go about designing the database for this?

I considered using a time series database, but i figured it isn't suitable - cycle time data has a start time and an end time - basically i'm measuring time performance over time - if this even makes sense. At the same time, I was also worried that using relational database to store and then display/analyze time related data is inefficient.

Any thoughts on a good database structure would be greatly appreciated. Let me know if any more info is needed and i will gladly edit this question

Solution

You are tracking the occurrence of an event. The event (weld) starts at some time and ends at some time. It might be tempting to model the event entity like so:

StationID StartTime StopTime

with each welding station having a unique identifier. Some sample data might look like this:

      17  08:00:00  09:00:00
      17  09:00:00  10:00:00

For simplicity, I've set the times to large values (1 hour each) and removed the date values. This tells you that welding station #17 started a weld at 8am and finished at 9am, at which time the second weld started which finished at 10am.

This seems simple enough. Notice, however, that the StopTime of the first entry matches the StartTime of the second entry. Of course it does, the end of one weld signals the start of the next weld. That's how the system was designed.

But this sets up what I call the Row Spanning Dependency antipattern: where the value of one field of a row must be synchronized with the value of another field in a different row.

This can create any number of problems. For example, what if the StartTime of the second entry showed '09:15:00'? Now we have a 15 minute gap between the end of the first weld and the start of the next. The system does not allow for gaps -- the end of each weld also starts the next weld. How should be interpret this gap. Is the StopTime of the first row wrong. Is the StartTime of the second row wrong? Are both wrong? Or was there another row between them that was somehow deleted? There is no way to tell which is the correct interpretation.

What if the StartTime of the second entry showed '08:45'? This is an overlap where the start of the second cycle supposedly started before the first cycle ended. Again, we can't know which row contains the erroneous data.

A row spanning dependency allows for gaps and overlaps, neither of which is allowed in the data. There would need to be a large amount of database and application code required to prevent such a situation from ever occurring, and when it does (as assuredly it will) there is no way to determine which data is correct and which is wrong -- not from within the database, that is.

An easy solution is to do away with the StopTime field altogether:

StationID StartTime
      17  08:00:00 
      17  09:00:00

Each entry signals the start of a weld. The end of the weld is indicated by the start of the next weld. This simplifies the data model, makes it impossible to have a gap or overlap, and more precisely matches the system we are modeling.

But we need the data from two rows to determine the length of a weld.

select  w1.StartTime, w2.StartTime as StopTime
from    Welds w1
join    Welds w2
    on  w2.StationID = w1.StationID
    and w2.StartTime =(
        select  Max( StartTime )
        from    Welds
        where   StationID = w2.StationID
            and StartTime < w2.StartTime );

This may seem like a more complicated query that if the start and stop times were in the same row -- and, well, it is -- but think of all that checking code that no longer has to be written, and executed at every DML operation. And since the combination of StationID and StartTime would be the obvious PK, the query would use only indexed data.

There is one addition to suggest. What about the first weld of the day or after a break (like lunch) and the last weld of the day or before a break? We must make an effort not to include the break time as a cycle time. We could include the intelligence to detect such situation in the query, but that would increase the complexity even more.

Another way would be to include a status value in the record.

StationID StartTime Status
      17  08:00:00    C
      17  09:00:00    C
      17  10:00:00    C
      17  11:00:00    C
      17  12:00:00    B
      17  13:00:00    C
      17  14:00:00    C
      17  15:00:00    C
      17  16:00:00    C
      17  17:00:00    B

So the first few entries represent the start of a cycle, whereas the entry for noon and 5pm represents the start of a break. Now we just need to append the line

where  w1.Status = 'C'

to the end of the query above. Thus the 'B' entries supply the end times of the previous cycle but do not start another cycle.