Horizontal Attribute Storage in Oracle: Examples and Benefits

When designing databases, one of the key decisions is how to organize data storage. A common choice is between horizontal and vertical data storage. Both approaches have their own features, advantages, and areas of application.

Vertical data storage assumes that each attribute of an entity is represented as a separate column in a table. This is the traditional approach used in most relational databases.

Horizontal attribute storage, on the other hand, is a database design approach where entity attributes are stored as rows in a table rather than as separate columns. This method is often used to store dynamic or user-defined attributes, where the number and structure of attributes are not known in advance. Let’s explore this approach using a table that stores location information (LOCATION).

Example Table Structure

Let’s consider the following table, LOCATION_REFNUM, which is used to store location attributes:

Column Name	Data Type	Required	Description
`LOCATION_GID`	`VARCHAR2(101 BYTE)`	Yes	Unique identifier for the location.
`LOCATION_REFNUM_QUAL_GID`	`VARCHAR2(101 BYTE)`	Yes	Global identifier for the attribute (e.g., attribute type).
`LOCATION_REFNUM_VALUE`	`VARCHAR2(240 CHAR)`	Yes	Value of the attribute.
`DOMAIN_NAME`	`VARCHAR2(50 BYTE)`	Yes (default `'PUBLIC'`)	Domain name to which the record belongs.
`INSERT_USER`	`VARCHAR2(128 BYTE)`	Yes	User who created the record.
`INSERT_DATE`	`DATE`	Yes	Date the record was created.
`UPDATE_USER`	`VARCHAR2(128 BYTE)`	No	User who last updated the record.
`UPDATE_DATE`	`DATE`	No	Date the record was last updated.

Example Data

Suppose we have two locations: a warehouse and an office. For each, we want to store working hours and address (without adding these as predefined attributes in the main table). Let’s insert the data into the table:

INSERT INTO LOCATION_REFNUM (LOCATION_GID, LOCATION_REFNUM_QUAL_GID, LOCATION_REFNUM_VALUE, DOMAIN_NAME, INSERT_USER, INSERT_DATE)
VALUES ('WAREHOUSE_001', 'WORKING_HOURS', '8:00-18:00', 'PUBLIC', 'admin', SYSDATE);

INSERT INTO LOCATION_REFNUM (LOCATION_GID, LOCATION_REFNUM_QUAL_GID, LOCATION_REFNUM_VALUE, DOMAIN_NAME, INSERT_USER, INSERT_DATE)
VALUES ('WAREHOUSE_001', 'ADDRESS', '123 Main St', 'PUBLIC', 'admin', SYSDATE);

INSERT INTO LOCATION_REFNUM (LOCATION_GID, LOCATION_REFNUM_QUAL_GID, LOCATION_REFNUM_VALUE, DOMAIN_NAME, INSERT_USER, INSERT_DATE)
VALUES ('WAREHOUSE_002', 'WORKING_HOURS', '9:00-17:00', 'PUBLIC', 'admin', SYSDATE);

That is, from the data, we have the identifier of the source object (to which we want to add a dynamic attribute), the attribute identifier (LOCATION_REFNUM_QUAL_GID), and its value (LOCATION_REFNUM_VALUE).

Data Retrieval

To retrieve all attributes of a specific location, you can use the following query:

SELECT LOCATION_REFNUM_QUAL_GID, LOCATION_REFNUM_VALUE
FROM LOCATION_REFNUM
WHERE LOCATION_GID = 'WAREHOUSE_001';

Result:

LOCATION_REFNUM_QUAL_GID	LOCATION_REFNUM_VALUE
WORKING_HOURS	8:30-18:00
ADDRESS	123 Main St.

Transforming to Vertical Format

However, there is often a need to transform data from horizontal storage to vertical (for reports, for example),

Method 1. Use the PIVOT operator:

 SELECT * FROM (
  SELECT LOCATION_GID, LOCATION_REFNUM_QUAL_GID, LOCATION_REFNUM_VALUE
  FROM LOCATION_REFNUM
) 
PIVOT (
  MAX(LOCATION_REFNUM_VALUE)
  FOR LOCATION_REFNUM_QUAL_GID IN ('WORKING_HOURS' AS ATTRIBUTE_1, 'ADDRESS' AS ATTRIBUTE_2)
)
WHERE ATTRIBUTE_1 is not null;

Method 2. Through GROUP BY aggregation

select l.location_gid,
 MAX(CASE WHEN lr.LOCATION_REFNUM_QUAL_GID = 'WORKING_HOURS' THEN lr.LOCATION_REFNUM_VALUE END) AS DELLIN_ADDRESSID,
    MAX(CASE WHEN lr.LOCATION_REFNUM_QUAL_GID = 'ADDRESS' THEN lr.LOCATION_REFNUM_VALUE END) AS DELLIN_COUNTERAGENTID
    FROM 
    location l
LEFT JOIN 
    location_refnum lr ON lr.location_gid = l.location_gid
    AND lr.LOCATION_REFNUM_QUAL_GID IN ('WORKING_HOURS', 'ADDRESS')
GROUP BY 
    l.location_gid

The same query can be used when there is a need for "data reversal", when it is necessary to obtain one row from several. It is important to understand that if there are multiple values, additional logic or aggregation may be needed to consolidate multiple rows into one.

Method 3. Through subqueries (less preferred for large datasets due to performance considerations)

SELECT 
    lr.LOCATION_GID,
    (SELECT LOCATION_REFNUM_VALUE 
     FROM LOCATION_REFNUM 
     WHERE LOCATION_GID = lr.LOCATION_GID 
       AND LOCATION_REFNUM_QUAL_GID = 'WORKING_HOURS') AS WORKING_HOURS,
    (SELECT LOCATION_REFNUM_VALUE 
     FROM LOCATION_REFNUM 
     WHERE LOCATION_GID = lr.LOCATION_GID 
       AND LOCATION_REFNUM_QUAL_GID = 'ADDRESS') AS ADDRESS
FROM 
    (SELECT DISTINCT LOCATION_GID FROM LOCATION_REFNUM) lr;

Result:

LOCATION_GID	WORKING_HOURS	ADDRESS
WAREHOUSE_001	8:30-18:00	123 Main St.
WAREHOUSE_002	9:00-17:00	NULL

Conclusion

In Oracle ERP, the use of horizontal attribute storage is widespread, as this approach allows for efficient management of dynamic data. However, it can complicate queries and data processing, as seen, the amount of code required to retrieve this data increases compared to the approach where attributes are stored in the main table.

Advantages of Horizontal Storage

Flexibility: Easily add new attributes without changing the table structure.
Scalability: Suitable for systems with a large number of dynamic or user-defined attributes.
Versatility: The same approach can be applied to different types of data.

Disadvantages of Horizontal Storage

Query Complexity: Retrieving data requires more SQL queries or complex transformations.
Performance: With large volumes of data, queries may execute slower due to the need to join rows.