We have a table that many processes read from. Some of them want to keep track of the fact they have processed this data, whereas others simply rely on fetching the data from the table based on user actions.

I think ideally, the consumers would interface with this data indirectly: probably via a web service, and then they could just keep track of what resources (URIs) they have discovered and processed, all internally to themselves.

The other options seem to be either adding a column to the existing table, or adding a new table - either way, one for each consumer. I think adding columns is easier and probably performs the best, but adding a new table keeps the clutter away from the original table.

Aside from those points, are there any others that I should be aware of?


To elaborate..

I have a table foo that is populated by an external process, and read:

  • By a web-service that simply exposes data depending on criteria (show me the latest... show me the one for this particular date, etc)
  • By a post-processor, that looks for foo records that it has not seen before, processes them (which writes data somewhere else, unrelated to the web service) and then wants to mark them as processed.

In order to mark them as processed, we can either have a column on the foo table (processed = 1), or we can have a foo_processed table (that just has the ids of the foo that have been processed, and use an outer join to find unprocessed foo records)

At this time there is only one post-processor, but I can't guarantee that.

  • Please clarify what you mean by "consumer": a user or client, meaning there are an arbitrary number of consumers, and the number of consumers can vary independently from the design of your program. Or is a consumer just one process from a small, fixed number of processes, well known at the time when you create your database design? – Doc Brown Aug 1 '12 at 15:27

Your instincts about indirect access are correct. Adding columns for clients to the table flies in the face of this and is a generally bad idea. From a mechanical point of view, you are taking on lots of DDL changes to a key table -- how much do you want to touch it? Will all the tracking map to something you can keep in a single row? Will someone stuffing data into this decide to dump a big nasty serialized XML object in there destroying performance? What happens when two of the apps want a field called "report_status"?

The correct course of action is to push these folks to separate tables or other data stores for their tracking. Then they can track what they need to track without interfering with the core data or with anyone else.

| improve this answer | |

As I understand this, you have a M-M relationship between your PrimaryTable and User as follows:

  • User reads zero, one more rows from PrimaryTable, and

  • PrimaryTable is read by zero, one or more Users

So, you need to create a junction table and have it contain the PKs of the user table and your PrimaryTable and optionally add a timestamp recording access time. Now you can either keep track of each access or only the last access.

The above is the Normalized way using a table. You may consider other storage options as indicated by Wyatt Barnett.

Edit: Added picture to make it clear

enter image description here

| improve this answer | |
  • I guess your solution is good for a different kind of "consumer" than the OP meant, but oversized for this case. – Doc Brown Aug 1 '12 at 16:23
  • As Wyatt says, the benefit of an additional table is that the existing database table won't break. As an aside, if it is primarily users who care about their own last accesses, Option 1 may be a bit faster if UserID_PK_FK is the first indexed column rather than the last. – Brian Aug 1 '12 at 16:38
  • @Brian, you are quite correct. – NoChance Aug 1 '12 at 22:53

As long as you have just one post-processor needing just a boolean "processed" flag, it may be reasonable and "YAGNI" to use just an additional column, especially for that. Your web service does not have to expose that column, so other users probabaly won't even notice the additional column.

But as soon as you get the requirement for a second "processed" column, you should move that information to something like a different table. But beware: I would use this approach only if you can easily refactor your application (and database changes sometimes don't lead to easy refactoring). If that's not the case, and if you think the chances are high there will be more such status flags in the future, I agree totally to @Wyatt's answer - keep the status information apart from the "real thing".

| improve this answer | |

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.