schema.sql 1006 Bytes
Newer Older
1
DROP TABLE IF EXISTS Description, Product;
Dr. Martin Goik's avatar
Dr. Martin Goik committed
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

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

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

-- 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, 0, 'Picked only by specially trained monkeys');
INSERT INTO Description VALUES(1, 1, '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-compartment tent');
INSERT INTO Description VALUES(2, 2, 'Pre-attached tent poles');