Queue Processing (Database structure)
-
I'm working on a project that stores addresses, and ultimately groups them by Locality/Territories. But along with this there are different stages where an address is first keyed and is "up-for-review". Once approved it would be assigned a Locale/Territory. Along with this a further queue or holding would store Exceptions (addresses that need to marked as "Invalid"). I'm hoping to get some suggestions on how structure the tables. Currently I have the queue's separated by tables. But as I'm moving along in development this is becoming tedious. For the most part 80-90% of the field names are shared between the different tables (Queues). My thought was to avoid any possible anomalies by separating the queues into different tables. Questions: -Would you suggest using one table to store all addresses and a foreign key to determine stage or queue? -Is this a common way of storing data that is used in Queue processing? Thanks for your help!!
-
I'm working on a project that stores addresses, and ultimately groups them by Locality/Territories. But along with this there are different stages where an address is first keyed and is "up-for-review". Once approved it would be assigned a Locale/Territory. Along with this a further queue or holding would store Exceptions (addresses that need to marked as "Invalid"). I'm hoping to get some suggestions on how structure the tables. Currently I have the queue's separated by tables. But as I'm moving along in development this is becoming tedious. For the most part 80-90% of the field names are shared between the different tables (Queues). My thought was to avoid any possible anomalies by separating the queues into different tables. Questions: -Would you suggest using one table to store all addresses and a foreign key to determine stage or queue? -Is this a common way of storing data that is used in Queue processing? Thanks for your help!!
I would not duplicate data. And I would avoid the need to move data from one table to another one, so yes I would be inclined to have only one table, and one or more status fields. I also would make sure to have a simple test for "record is valid", best a single "OK" value in a single overall status field, which means the extra status fields only are relevant when the overall isn't "OK". (Of course I prefer numeric statuses!) FWIW: I avoid multiple tables with the same set of fields; it is only huge amounts of data that would make me come back on this. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read formatted code with indentation, so please use PRE tags for code snippets.
I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).
-
I would not duplicate data. And I would avoid the need to move data from one table to another one, so yes I would be inclined to have only one table, and one or more status fields. I also would make sure to have a simple test for "record is valid", best a single "OK" value in a single overall status field, which means the extra status fields only are relevant when the overall isn't "OK". (Of course I prefer numeric statuses!) FWIW: I avoid multiple tables with the same set of fields; it is only huge amounts of data that would make me come back on this. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read formatted code with indentation, so please use PRE tags for code snippets.
I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).
Luc, I appreciate your help. Could you clarify though the purpose of your, perhaps boolean field "record valid"? For my specific application an invalid record (perhaps incorrect address) would be moved to a different stageID or queue. Do you also have an additional purpose in mind for this "field value" status field? Thank You!
-
Luc, I appreciate your help. Could you clarify though the purpose of your, perhaps boolean field "record valid"? For my specific application an invalid record (perhaps incorrect address) would be moved to a different stageID or queue. Do you also have an additional purpose in mind for this "field value" status field? Thank You!
I would have one field that says: this record is completely OK, it can be processed by all reporting or whatever actions your data deserves. So all existing, normal queries only must check one (or one more) field. All other data is not yet completely OK; it is still in some input stage, and those stages could be identified using one or more fields, as appropriate. Separating the input stage codes from the "all OK" one makes sure your preparation and processing actions are independent of each other. Just my 2ct. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read formatted code with indentation, so please use PRE tags for code snippets.
I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).
-
I would have one field that says: this record is completely OK, it can be processed by all reporting or whatever actions your data deserves. So all existing, normal queries only must check one (or one more) field. All other data is not yet completely OK; it is still in some input stage, and those stages could be identified using one or more fields, as appropriate. Separating the input stage codes from the "all OK" one makes sure your preparation and processing actions are independent of each other. Just my 2ct. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read formatted code with indentation, so please use PRE tags for code snippets.
I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).
Ahhh. Thank you for your 2ct's. Its great hearing different ideas as I was thinking about it from a different angle.