There are several ways to implement database structure for storing configuration objects.
In this page we would like to analyze all the alternatives and identify what would be the best option for the database structure to store configurable objects

Existing database schema

Existing database schema contains 10 tables to store the following:

  1. current schema version (VERSION table)
  2. exchanges (EXCHANGES table)
  3. queues (QUEUES table)
  4. queue bindings (QUEUE_BINDINGS table)
  5. queue messages (DELIVERIES table)
  6. messages (MESSAGE_META_DATA and MESSAGE_CONTENT tables)
  7. federation links (LINKS table)
  8. federation bridges (BRIDGES table)
  9. distributed transactions (XIDS table)

In order to store the entire broker configuration with such schema more tables are actually needs to be created for storing configuration entities like broker, virtual hosts etc.

That's seems impractical and require extra development efforts to implement functionality for each configurable object.

Database schema with storing relationship in the tables

variant 1 - table per relationship

With this design configurable objects (broker, virtual hosts, queues, queue bindings, exchanges, exchange bindings etc) can be stored in CONFIGURATION table.

Each configurable object should have a unique ID and all its attributes are stored in attributes column as BLOB.
One-to-many relationship are stored using PARENT_ID column.
For storing many-to-many relationships (like queue bindings, exchange bindings etc) special tables are created for each of the relationship.

On the design above QUEUE_BINDINGS table is added to store queue bindings and QUEUE_ALTERNATE_EXCHANGES is added to store queue alternate exchanges

Advantages

  • easier to understand the relationship between objects
  • relationships are isolated from attributes

Disadvantages

  • implementation of each relationship requires writing new code

variant 2 - single table for storing all relationships

With this design configurable objects (broker, virtual hosts, queues, queue bindings, exchanges, exchange bindings etc) can be stored in CONFIGURATION table.

Each configurable object have a unique ID and all its attributes are stored in attributes column as BLOB.
One-to-many relationship are stored using PARENT_ID column.
All many-to-many relationships (like queue bindings, exchange bindings etc) are stored in the special ASSOCIATIONS table.

ASSOCIATION_TYPE column is used to distinguish the association type.

Advantages

  • less coding required for the implementation of each individual relationship, relative to variant 1
  • relationships are isolated from attributes

Disadvantages

  • extra code is required to evaluate and set the relationship between configurable objects

Database schema with storing relationship in JSON format

With this design configurable objects are stored in CONFIGURATION table.
All relationship (one-to-many, many-to-many) are stored in attributes field in JSON format as IDs.

Advantages

  • less coding required for the implementation of each individual relationship

Disadvantages

  • the attributes JSON is polluted with object IDs, which aren't really "attributes"
  • the configuration POJOs are required to have both IDs and relationship fields
  • relationship can only be established after parsing the JSON and creating configuration entities for all the configurable objects which implies to have 2-stage configuration loading process:
    1. instantiate configurable objects from JSON without relationship
    2. set relationship using IDs

Summary

The last approach to store relationships as part of attributes JSON seems the most preferable and does not requires a lot of efforts to implement.

  • No labels