Sample data for user/userdetails + n devices/devicedetails

sample-data-one-to-many.sql

-- DROP all data tables
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS userdetails;
DROP TABLE IF EXISTS devices;
DROP TABLE IF EXISTS devicesdetails;

-- USERS and USER DETAILS
CREATE TABLE users
	(`row_id` int, `id` int, `owner_id` int, `name` varchar(55))
;
	
INSERT INTO users
	(`row_id`, `id`, `owner_id`, `name`)
VALUES
	(1, 1553, 551, 'CI 1'),
	(2, 1003, 551, 'CI 2'),
	(3, 1200, 551, 'CI 3')
;

CREATE TABLE userdetails
	(`row_id` int, `user_id` int, `somedetail` varchar(55))
;
	
INSERT INTO userdetails
	(`row_id`, `user_id`, `somedetail`)
VALUES
	(7, 1003, 'These are some CI 2 details.'),
	(8, 1200, 'These are some CI 3 details.'),
  (9, 1553, 'These are some CI 1 details.')
;

-- DEVICES UND DEVICE DETAILS
-- type is an enum
CREATE TABLE devices
	(`row_id` int, `user_id` int, `device_id` int, `type` varchar(5))
;
-- one to n:   user > n devices
INSERT INTO devices
	(`row_id`, `user_id`, `device_id`, `type`)
VALUES
	(23, 1003, 345, 'PHONE'),
	(24, 1200, 347, 'PHONE'),
  (25, 1200, 756, 'PHONE'),
  (27, 1200, 754, 'PHONE'),
  (26, 1200, 343, 'FAX'),
  (22, 1553, 123, 'FAX'),
  (14, 1553, 223, 'PHONE')
;

CREATE TABLE devicesdetails
	(`row_id` int, `user_id` int, `device_id` int, `call_limit` int)
;


INSERT INTO devicesdetails
	(`row_id`, `user_id`, `device_id`, `call_limit`)
VALUES
	(23, 1003, 345, 0),
	(24, 1200, 347, 2),
  (25, 1200, 756, 1),
  (27, 1200, 754, 2),
  (26, 1200, 343, 1),
  (22, 1553, 123, 1),
  (14, 1553, 223, 4)
;