SQL Database Design » 7. Standard Data Types » 7.8 Structured Types

7.8 Structured Types

Many DBMSs offer so-called structured data types, also called composite, aggregate, or compound types. These types are composed of more than one attribute, with each one potentially having another data type. They are basically like classes or C/C++ structs. This is why they are more commonly known as object-relational extensions.

As an example, PostgreSQL and MySQL have non-standard geometric types like POINT and LINE. A point includes at least two coordinates, both probably being some kind of floating-point type. There are other types you see around, like STRUCT. Avoid them as well, they are not standard SQL.

To use structured types, you put them into a CREATE TABLE statement like simple data types:

CREATE TABLE Locations
(
  ...
  coordinate POINT,
  ...
)

To insert a row with that type, you must use a constructor-like syntax:

INSERT INTO Locations VALUES (..., POINT(23.53, 98.16), ...)

To otherwise use, e.g. query, a structured type's column, you must use the dot operator:

SELECT ..., coordinate.x, coordinate.y, ... FROM Locations WHERE ...

Note, that using a predefined structured type uses uppercase. When using user-defined types, you will probably want to adjust the capitalization.

The object-relational concept of the SQL standard is rather unmethodical. It is somewhat inconsistent with the relational model, but porting from relational databases is not trivial. Overall, it made more sense to stay in the relational world, into which many man-month had already been invested. Structured types generally have normalization issues associated with them. Please refer to the sections about 8. User-Defined Types and 14. Normalization for more information.

References for Structured Types:

Last updated: 2010-08-04