schema.sql

DROP TABLE IF EXISTS Description;
DROP TABLE IF EXISTS Product;

CREATE TABLE Product (
   id INTEGER NOT NULL PRIMARY KEY
  ,name VARCHAR(255) NOT NULL
  ,age SMALLINT
);

CREATE TABLE Description (
   product INTEGER NOT NULL REFERENCES Product
  ,orderIndex int NOT NULL   -- preserving the order of descriptions belonging to a given product
  ,text VARCHAR(255) NOT NULL
  ,UNIQUE(product, orderIndex)
);

-- example data corresponding to products.xml --

-- Product lacking age property --
INSERT INTO Product (id, name) VALUES (1, 'Monkey Picked Tea');
INSERT INTO Description VALUES(1, 1, 'Picked only by specially trained monkeys');
INSERT INTO Description VALUES(1, 2, 'Rare wild Chinese tea');

INSERT INTO Product VALUES (2, '4-Person Instant Tent', 15);
INSERT INTO Description VALUES(2, 0, 'Exclusive WeatherTec system.');
INSERT INTO Description VALUES(2, 1, '4-person, 1-room tent');
INSERT INTO Description VALUES(2, 2, 'Pre-attached tent poles');