word lists schema

wordlists.create.sql

CREATE TABLE `WordLists` (
	`id` int NOT NULL AUTO_INCREMENT,
	`name` varchar(100) NOT NULL,
	`description` varchar(300),
	PRIMARY KEY (`id`)
);

CREATE TABLE `WordGroups` (
	`id` int NOT NULL AUTO_INCREMENT,
	`wordListId` int NOT NULL,
	`name` varchar(100) NOT NULL,
	`description` varchar(300),
	`orderNum` int NOT NULL,
	PRIMARY KEY (`id`)
);

CREATE TABLE `WordListItems` (
	`id` int NOT NULL AUTO_INCREMENT,
	`phrase` varchar(100) NOT NULL,
	`translation` varchar(200),
	PRIMARY KEY (`id`)
);

CREATE TABLE `WordListItemData` (
	`id` int NOT NULL AUTO_INCREMENT,
	`wordListItemId` int NOT NULL,
	`dataBlob` JSON NOT NULL,
	PRIMARY KEY (`id`)
);

CREATE TABLE `WordListAssignments` (
	`id` int NOT NULL AUTO_INCREMENT,
	`wordGroupId` int NOT NULL,
	`wordListItemId` int NOT NULL,
	PRIMARY KEY (`id`)
);

ALTER TABLE `WordGroups` ADD CONSTRAINT `WordGroups_fk0` FOREIGN KEY (`wordListId`) REFERENCES `WordLists`(`id`);

ALTER TABLE `WordListItemData` ADD CONSTRAINT `WordListItemData_fk0` FOREIGN KEY (`wordListItemId`) REFERENCES `WordListItems`(`id`);

ALTER TABLE `WordListAssignments` ADD CONSTRAINT `WordListAssignments_fk0` FOREIGN KEY (`wordGroupId`) REFERENCES `WordGroups`(`id`);

ALTER TABLE `WordListAssignments` ADD CONSTRAINT `WordListAssignments_fk1` FOREIGN KEY (`wordListItemId`) REFERENCES `WordListItems`(`id`);

wordlists.drop.sql

ALTER TABLE `WordGroups` DROP FOREIGN KEY `WordGroups_fk0`;

ALTER TABLE `WordListItemData` DROP FOREIGN KEY `WordListItemData_fk0`;

ALTER TABLE `WordListAssignments` DROP FOREIGN KEY `WordListAssignments_fk0`;

ALTER TABLE `WordListAssignments` DROP FOREIGN KEY `WordListAssignments_fk1`;

DROP TABLE IF EXISTS `WordLists`;

DROP TABLE IF EXISTS `WordGroups`;

DROP TABLE IF EXISTS `WordListItems`;

DROP TABLE IF EXISTS `WordListItemData`;

DROP TABLE IF EXISTS `WordListAssignments`;

wordlists.queries.sql

--  Select definition from an assignment id when
--   the definition is in the Defintions table
SELECT Assignments.id, Defs.phrase, Defs.definition
    FROM Definitions AS Defs
    INNER JOIN
        WordListItems AS Items
    ON
        Items.definitionId = Defs.id
        
    INNER JOIN
        WordListAssignments AS Assignments
    ON
        Assignments.id = 5
        AND
        Items.id = Assignments.wordListItemId;

-- Select definition from an assignment id when 
--  the def is in the word items table
SELECT Assignments.id, Items.phrase, Items.translation
    FROM WordListItems AS Items 
    INNER JOIN WordListAssignments AS Assignments 
    ON 
        Assignments.id = 3
        AND Items.id = Assignments.wordListItemId;

-- Select word list from an assignment id
SELECT  Lists.id, Lists.name, Lists.description
    FROM WordLists AS Lists
    INNER JOIN
        WordGroups AS Groups
    ON
        Lists.id = Groups.wordListId
        
    INNER JOIN
        WordListAssignments AS Assignments
    ON
        Assignments.id = 5
        AND
        Groups.id = Assignments.wordGroupId;

-- Select sentences from an assignment id
SELECT ItemData.id, ItemData.dataBlob from WordListItemData AS ItemData
    LEFT JOIN
        WordListItems AS Items
    ON
        ItemData.type = "sentence"
        AND
        ItemData.wordListItemId = Items.id
    
    LEFT JOIN
        WordListAssignments AS Asses
    ON
        Items.id = Asses.wordListItemId
        AND
        Asses.id = 3;
        
-- Select all sentences from an assignment id
SELECT
    Data.id, Data.dataBlob AS sentenceJson, null AS sentence, null AS translation
  FROM
    WordListItemData AS Data
  INNER JOIN
    WordListItems AS Items 
    ON Data.wordListItemid = Items.id
    
  INNER JOIN WordListAssignments AS Asses 
    ON Asses.id = 9
    AND Items.id = Asses.wordListItemId

UNION

SELECT
    S.id, null AS sentenceJson, S.sentence, S.sentence_translation AS translation
  FROM
    Sentences AS S
  INNER JOIN
    Connotations AS Conns
    ON S.connotation_id = Conns.id
  
  INNER JOIN
    Definitions AS Defs 
    ON Defs.id = Conns.definition_id
    
  INNER JOIN
    WordListItems AS Items 
    ON Defs.id = Items.definitionId
    
  INNER JOIN
    WordListAssignments AS Asses
    ON Asses.wordListItemId = Items.id
    AND Asses.id = 9;

-- Select card from assignment id
SELECT 
  Asses.id, Defs.phrase, Defs.definition AS translation
FROM Definitions AS Defs
INNER JOIN
  WordListItems AS Items
  ON Items.definitionId = Defs.id

INNER JOIN
  WordListAssignments AS Asses
  ON Asses.id = 3
  AND Items.id = Asses.wordListItemId
LIMIT 1

UNION

SELECT 
  Asses.id, Items.phrase, Items.translation
FROM WordListItems AS Items 
INNER JOIN WordListAssignments AS Asses 
  ON Asses.id = 3
  AND Items.id = Asses.wordListItemId
LIMIT 1;