Storing of Row labelled as "problematic"

[RuleBritannia]

Hello

 

If we had multiple tables which hold data to be processed

IMAGE
+----+-----------+  
| id |   image   |
+----+-----------+
| 4  | test.png  |
| 5  | bird.png  |
+----+-----------+

ORDER
+----+-----------+
| id |   order   |
+----+-----------+
| 4  | 100000045 |
| 5  | 100000046 |
+----+-----------+

When a processing error occurs, I thought to move the row to a table called error/attention/concern etc, To keep the table clean.

 

Which would look something like this.

ERROR
+----+-----------+----------+-----------+
| id | table_id  |  image   |   order   |
+----+-----------+----------+-----------+
| 1  |  4        | test.png |   EMPTY   |
| 2  |  5        |  EMPTY   | 100000046 |
+----+-----------+----------+-----------+

However, This table will have to have corresponding columns for IMAGE and ORDER, But then we have a problem of storing empty/null values for the columns which are not needed on insertion here, This does not seem right.

 

Has anybody had a similar issue before like this? Im sure there maybe a more definitive way of doing this.

I also thought possibly just have 2 rows here, and enter a serialized array of data, but that seems even worse than this.

 

Or finally I could possibly make 2 more tables, IMAGE_ERROR, ORDER_ERROR.

But upon adding new processing technologies, I would need to add 2 new tables each time.

 

Thanks in advance for any help/advice/answers.

 

[ginerjm]

What do you mean by a processing error?  Do you mean you can't add the data to the table?  Perhaps you should solve the problem rather than shuffling the data somewhere else.  Check for valid data entry before attempting to post it to the db.

[RuleBritannia]

What do you mean by a processing error?  Do you mean you can't add the data to the table?  Perhaps you should solve the problem rather than shuffling the data somewhere else.  Check for valid data entry before attempting to post it to the db.

 

Processing error just within the script logic, eg image type not reconised, with regards to solving the problems, some problems I cannot foresee so it may not be possible to solve them, ontop of this the server must use its resources elsewhere, so we move the row to errors, and move on.

 

I thought to have this column as a place I can consolidate all errors to, and check here every now and then to see any problems.

[RuleBritannia]

When i say errors its not the best word, I mean it in a scenario that if we use for example a libary to process some data, if that libary does not have the capabilities for the image file type, we will move it to error/concern table, so we can deal with it later, rather that leaving it in the que, to be included again a the next loop iteration, or marking it as "problem" and cluttering the table, it seems best moved to error/concern table, as it is not possible to solve this with our current libaries.

Edited January 20, 2014 by RuleBritannia

[kicken]

or marking it as "problem" and cluttering the table, it seems best moved to error/concern table

No, don't move it. The data belongs where it is, just because you can't currently handle it doesn't mean it doesn't belong there. You should just mark it as processed and optionally include an error code that will indicate it needs to be re-visited.

 

Trying to shuffle stuff around between tables is only going to cause you headaches in the future.

[RuleBritannia]

No, don't move it. The data belongs where it is, just because you can't currently handle it doesn't mean it doesn't belong there. You should just mark it as processed and optionally include an error code that will indicate it needs to be re-visited.

 

Trying to shuffle stuff around between tables is only going to cause you headaches in the future.

hmm, But we can be inserting thousands of rows here daily, Not sure if its a good idea to clutter it with potentially thousands of rows which at that current time cannot be processed.

 

Im still thinking of the best option for this, Thanks for your input.

[ginerjm]

Sounds to me like you are NOT handling this project wisely.  With the possibility for "thousands of rows here daily" having any significant number of errors, that you don't want these things to be processed, but rather rejected at input.  You really should step back and think about what you can do to help validate the input while the user is in the act of doing it so as to prevent as many 'rejections' as possible.

 

Do you really want to be reviewing dozens(?) of errors every day?

 

PS - this is the first time I've heard of your approach to error handling in 30+ years of design and development.  Very strange.

Edited January 21, 2014 by ginerjm

[RuleBritannia]

Sounds to me like you are NOT handling this project wisely.  With the possibility for "thousands of rows here daily" having any significant number of errors, that you don't want these things to be processed, but rather rejected at input.  You really should step back and think about what you can do to help validate the input while the user is in the act of doing it so as to prevent as many 'rejections' as possible.

 

Do you really want to be reviewing dozens(?) of errors every day?

 

PS - this is the first time I've heard of your approach to error handling in 30+ years of design and development.  Very strange.

 

They most likley will not have thousands of errors, But its not impossible.

 

@that you don't want these things to be processed, but rather rejected at input.

 

They will not be rejected at input, Once they error, they will be moved to another table so they dont clutter the Image table whilst they are not possible to be processed.

 

The project is also only for me.

 

@help validate the input while the user is in the act of doing it so as to prevent as many 'rejections' as possible.

 

This is not possible, That would mean processing thousands of rows at once.

 

The current method is rows get put in the Image table, processed at a reasonable rate within a parallel limit, If there is some file meta/file corrupt/file type/libary error etc, This will never fix itself by just reattempting to process it, The only way of fixing this is manually checking/deleting/updating/etc the file or libary which processes it.

 

@Do you really want to be reviewing dozens(?) of errors every day?

This is irrelevant to wherever/however they are handled/placed.

 

Also its worth mentioning as I said before, I cannot foresee every possible "error", the plan is to reduce as many as possible, And each time a error does surface, I examine it and update the script to rule it out if possible before processing, But sometimes this is not possible without processing.

Edited January 21, 2014 by RuleBritannia

[ginerjm]

Have fun!

[RuleBritannia]

Have fun!

 

When you want to share your method of how you obtain spontaneous unlimited computational power please PM ME

 

@kicken I think your idea is suffiicient,.

Edited January 21, 2014 by RuleBritannia