1

I am storing pedigree data in a single table and each entry has reference to its two parents. Each time I add an entry I discover two new ones I need to collect. I am on the fence between two options to handle this:

  1. Create a separate table that acts as a queue so that retrieval processes can simply delete an entry an fetch it, then add the two new entries to the end.
  2. Add two flag bits to the main table: complete and processing. When an entry is fetched, we fill in its row and create two rows for its parents that have the completed flag set false. The processing flag is for parallel processing purposes.

Which method makes more sense?

2

Separate table.

Using a flag as 'is processed' to simulate a queue is an anti-pattern. Because, as the table grows, finding the rows that need to be processed becomes increasingly difficult. An index on the flag field is useless due to very low selectivity. Using a table as a queue alleviates the problem as the rows in the queue are deleted when processed.

You can make the case also at a more theoretical level as tables are for data (long lived, queried in various ways) and queues are for events (short lived, always dequeued and not queried etc) and they should be stored separately. Also separating the processing into queues will allow you to make much more complex workflows (basically, add more queues), while the same based on row state (flags or status field) is more difficult to achieve (again, the retrieval performance issue) and more invasive (workflow changes affect the row info, hence the model representation in the app).

See Using Tables as Queues and 5 subtle ways you're using MySQL as a queue, and why it'll bite you (I don't agree with everything in the second link, but is still a good read).

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.