DROP TABLE IF EXISTS tng_dna_tests;
CREATE TABLE `tng_dna_tests` (
  `testID` int NOT NULL AUTO_INCREMENT,
  `test_type` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `test_number` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `vendor` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `test_date` date NOT NULL,
  `match_date` date NOT NULL,
  `personID` varchar(22) COLLATE utf8mb4_unicode_ci NOT NULL,
  `gedcom` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `person_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `urls` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `mtdna_haplogroup` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ydna_haplogroup` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `significant_snp` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `terminal_snp` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
  `markers` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `y_results` varchar(512) COLLATE utf8mb4_unicode_ci NOT NULL,
  `hvr1_results` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `hvr2_results` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mtdna_confirmed` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ydna_confirmed` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL,
  `markeropt` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL,
  `notesopt` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL,
  `linksopt` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL,
  `surnamesopt` tinyint NOT NULL,
  `private_dna` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL,
  `private_test` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL,
  `dna_group` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `dna_group_desc` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `surnames` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `MD_ancestorID` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `MRC_ancestorID` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `admin_notes` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `medialinks` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `ref_seq` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `xtra_mut` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `coding_reg` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `GEDmatchID` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `relationship_range` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
  `suggested_relationship` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
  `actual_relationship` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `related_side` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL,
  `shared_cMs` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `shared_segments` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `chromosome` varchar(4) COLLATE utf8mb4_unicode_ci NOT NULL,
  `segment_start` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `segment_end` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `centiMorgans` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `matching_SNPs` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `x_match` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`testID`),
  KEY `test_date` (`test_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;