Schema design: array type vs. normalised?

[marko]

Hi,

I'm designing a schema for storing route info in a H2 database.

A route consists of metadata as well as a sequence of coordinates (from hundreds to thousands of coordinate pairs).

From a modelling and performance perspective what would be the recommended way to store the data?

Using a normalised database schema or packing the coordinates in H2 ARRAY elements?

Once a route is created, waypoints associated with it are never updated, but other metadata will be.

CREATE TABLE route (

  id IDENTITY,

  name VARCHAR(50)

);

CREATE TABLE waypoint (

  route_id BIGINT,

  lat DOUBLE NOT NULL,

  lon DOUBLE NOT NULL,

  timestamp BIGINT,

  FOREIGN KEY (route_id) REFERENCES route(id)

);

or

CREATE TABLE route2 (

  id IDENTITY,

  name VARCHAR(50),

  waypoints ARRAY

);

[Evgenij Ryazanov]

Hello.

When database reads a row from a table, the whole row is constructed and all its values are read, with exception for large CLOB and BLOB objects. It means that query like

SELECT id FROM route2 WHERE name LIKE '%something%'

will read a lot of unnecessary data from waypoints column.

Not all queries will be affected, if you have a compatible index with all necessary columns, the rows from the table will not be read, but usually very large columns slow down the things.

Please also note that mixed-type arrays such as

ARRAY[1, 3.0, TIMESTAMP '2000-01-01 00:00:00']

should be considered as deprecated and may not work any more in the future versions of H2.