0

I want to introduce a queue functionality in an existing application built on Access VBA with an SQL Server 2012 backend. It should allow the application to store open tasks with 1:n parameters in a queue table and process them later on. It deserves mentioning that for some tasks, it might take several process steps until all information needed for their processing is available.

Some more information on my current situation:

  • The data needs to be persisted in the database for compliance reasons
  • No more than 1500 tasks will be processed each day
  • The application will be rebuild (except for the backend), the new application will make much more heavy use of this queue functionality
  • The total number of different tasks to be queued, as well as the no. of parameters they might need, is unknown

My currently best approach - however in EAV schema - would consist of three tables:

1. Table "tblQueueItemType"

It contains definitions for each type (or category) of task.

It contains an id, a name and an attribute count. This attribute count defines the number of attributes for this task. I want to use it later on to ensure data consistency for all tasks with status "READY".

Example for an entry in this table:

"1", "Generate Book Database Entry", "5"

2. Table "tblQueueItemHeader"

It which represents the instantiated tasks defined in the tblQeueItemType. They have a task id, their corresponding task type defined in tblQeueItemType, a status as well as a timestamp.

The status is either OPEN (not all information available), READY (all information available to process task), and DONE (when processed).

Example for an entry in this table:

"2", "1", "OPEN"

3. Table "tblQueueItemAttribute"

It contains all the information the tasks need to be processed. It contains an id, the id of the header, an attribute type and an attribute value.

Example entries for this table:

"1","2", "Author", "H.G. Wells" "1","2", "No. Pages", "1234"

My table definitions so far:

CREATE TABLE [dbo].[tblQueueItemType](
    id INT NOT NULL IDENTITY (1,1) PRIMARY KEY,
    Name NVARCHAR(20) NOT NULL,
    AttributeCount INT NOT NULL
    )

CREATE TABLE [dbo].[tblQueueItemHeader](
    id INT NOT NULL IDENTITY (1,1) PRIMARY KEY,
    QueueItemTypeId INT NOT NULL,
    Status NVARCHAR(5) NOT NULL,
    Timestamp DATETIME NOT NULL
CONSTRAINT QueueTypeHeader
    FOREIGN KEY (QueueItemTypeId)
    REFERENCES tblQueueItemType (id)
    )

CREATE TABLE [dbo].[tblQueueItemAttribute](
    id INT NOT NULL IDENTITY (1,1) PRIMARY KEY,
    QueueItemHeaderId INT NOT NULL,
    Attribute NVARCHAR(5) NOT NULL,
    Value NVARCHAR(50) NOT NULL,
    Timestamp DATETIME NOT NULL
CONSTRAINT QueueHeaderAttribute
    FOREIGN KEY (QueueItemHeaderId)
    REFERENCES tblQueueItemHeader (id)
    )

ALTER TABLE tblQueueItemHeader 
ADD CONSTRAINT QueueItemHeaderStatus 
CHECK (Status IN ('OPEN', 'READY', 'DONE'));

Obviously the current design is suboptimal. What would be best schema for this kind of use-case? How feasible is my current approach?

Thank you very much!

Banana One
  • 1
  • 1
  • Off-topic for SO - perhaps SuperUser would be better. But a review starts with complete DDL, even if it missing items. Define your primary keys and your unique constraints - foreign keys if you can. Note that your current DDL is already EAV - so you have already ventured away from proper normalization. Lastly, at least one of your table names in the description do not match your DDL - which is not an encouraging sign. Formatting for readability will encourage others to review - yours needs work. And yes - that is difficult to do with the limitations of this site. – SMor May 18 '19 at 15:30
  • @Smor, thanks for your feedback. I have integrated your remarks into the post and improved the content & formatting. I tried posting it to SuperUser, however it has been closed for being off-topic. :-) – Banana One May 18 '19 at 16:58
  • Coding SQL is on-topic. Define tables that describe your application state. Avoid EAV, that defines tables that describe tables that describe your application state. [What are the options for storing hierarchical data in a relational database?](https://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database) How is this queue data supposed to fit into the rest of your application? What other task data do you have? It's hard to know whether EAV is appropriate. Why counts? If a task has an attribute, just say so. Where's your queue state? – philipxy May 18 '19 at 19:04

0 Answers0