CREATE TABLE `calculations` (
  `id` int(9) NOT NULL,
  `name` varchar(256) DEFAULT NULL,
  `type` enum('1','2','3') NOT NULL,
  `registration-nr` varchar(10) DEFAULT NULL,
  `notes` varchar(1024) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `companies` (
  `id` int(9) NOT NULL,
  `name` varchar(64) NOT NULL,
  `letter` varchar(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `companies`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `litera` (`letter`);


CREATE TABLE `computings` (
  `id` int(9) NOT NULL,
  `company_id` int(9) NOT NULL,
  `single-fee` float(6,2) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `assignments` (
  `id` int(9) NOT NULL,
  `calculation-id` int(9) NOT NULL,
  `computing-id` int(9) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


--
-- Indexes for table `assignments`
--
ALTER TABLE `assignments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `calculation-id` (`calculation-id`),
  ADD KEY `computing-id` (`computing-id`);

--
-- Indexes for table `calculations`
--
ALTER TABLE `calculations`
  ADD PRIMARY KEY (`id`),
  ADD KEY `registration-nr` (`registration-nr`),
  ADD KEY `name` (`name`(255));

--
-- Indexes for table `companies`
--
ALTER TABLE `companies`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `litera` (`letter`);

--
-- Indexes for table `computings`
--
ALTER TABLE `computings`
  ADD PRIMARY KEY (`id`),
  ADD KEY `company_id` (`company_id`);



--
-- Ograniczenia dla zrzutów tabel
--

--
-- Ograniczenia dla tabeli `assignments`
--
ALTER TABLE `assignments`
  ADD CONSTRAINT `assignments_ibfk_4` FOREIGN KEY (`computing-id`) REFERENCES `computings` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `assignments_ibfk_3` FOREIGN KEY (`calculation-id`) REFERENCES `calculations` (`id`) ON DELETE CASCADE;

--
-- Ograniczenia dla tabeli `computings`
--
ALTER TABLE `computings`
  ADD CONSTRAINT `computings_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE;
COMMIT;