9EWzsqh3

1-- phpMyAdmin SQL Dump
2-- version 4.9.2
3-- https://www.phpmyadmin.net/
4--
5-- Host: 127.0.0.1:3306
6-- Generation Time: May 15, 2021 at 07:58 PM
7-- Server version: 10.4.10-MariaDB
8-- PHP Version: 7.4.0
9
10SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
11SET AUTOCOMMIT = 0;
12START TRANSACTION;
13SET time_zone = "+00:00";
14
15--
16-- Database: `test3`
17--
18
19-- --------------------------------------------------------
20
21--
22-- Table structure for table `products`
23--
24
25DROP TABLE IF EXISTS `products`;
26CREATE TABLE IF NOT EXISTS `products` (
27  `id` int(11) NOT NULL AUTO_INCREMENT,
28  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
29  PRIMARY KEY (`id`)
30) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
31
32--
33-- Dumping data for table `products`
34--
35
36INSERT INTO `products` (`id`, `name`) VALUES
37(1, 'Pencil'),
38(2, 'Calculator');
39
40-- --------------------------------------------------------
41
42--
43-- Table structure for table `product_units`
44--
45
46DROP TABLE IF EXISTS `product_units`;
47CREATE TABLE IF NOT EXISTS `product_units` (
48  `id` int(11) NOT NULL AUTO_INCREMENT,
49  `product_id` int(11) NOT NULL,
50  `unit` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
51  `qty` int(11) NOT NULL,
52  `parent_unit` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
53  PRIMARY KEY (`id`),
54  KEY `product_id` (`product_id`),
55  KEY `unit` (`unit`)
56) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
57
58--
59-- Dumping data for table `product_units`
60--
61
62INSERT INTO `product_units` (`id`, `product_id`, `unit`, `qty`, `parent_unit`) VALUES
63(1, 1, 'Carton', 100, 'Box'),
64(2, 1, 'Box', 10, 'Pcs'),
65(3, 1, 'Pcs', 1, '');
66
67-- --------------------------------------------------------
68
69--
70-- Table structure for table `purchases`
71--
72
73DROP TABLE IF EXISTS `purchases`;
74CREATE TABLE IF NOT EXISTS `purchases` (
75  `id` int(11) NOT NULL AUTO_INCREMENT,
76  `product_id` int(11) NOT NULL,
77  `unit` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
78  `qty` int(11) NOT NULL,
79  PRIMARY KEY (`id`),
80  KEY `product_id` (`product_id`),
81  KEY `unit` (`unit`)
82) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
83
84--
85-- Dumping data for table `purchases`
86--
87
88INSERT INTO `purchases` (`id`, `product_id`, `unit`, `qty`) VALUES
89(1, 1, 'Carton', 100),
90(2, 1, 'Box', 10);
91
92-- --------------------------------------------------------
93
94--
95-- Table structure for table `sales`
96--
97
98DROP TABLE IF EXISTS `sales`;
99CREATE TABLE IF NOT EXISTS `sales` (
100  `id` int(11) NOT NULL AUTO_INCREMENT,
101  `product_id` int(11) NOT NULL,
102  `unit` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
103  `qty` int(11) NOT NULL,
104  PRIMARY KEY (`id`),
105  KEY `product_id` (`product_id`),
106  KEY `unit` (`unit`)
107) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
108
109--
110-- Dumping data for table `sales`
111--
112
113INSERT INTO `sales` (`id`, `product_id`, `unit`, `qty`) VALUES
114(1, 1, 'Box', 10),
115(2, 1, 'Pcs', 5);
116
117--
118-- Constraints for dumped tables
119--
120
121--
122-- Constraints for table `product_units`
123--
124ALTER TABLE `product_units`
125  ADD CONSTRAINT `product_units_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`);
126
127--
128-- Constraints for table `purchases`
129--
130ALTER TABLE `purchases`
131  ADD CONSTRAINT `purchases_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
132  ADD CONSTRAINT `purchases_ibfk_2` FOREIGN KEY (`unit`) REFERENCES `product_units` (`unit`);
133
134--
135-- Constraints for table `sales`
136--
137ALTER TABLE `sales`
138  ADD CONSTRAINT `sales_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
139  ADD CONSTRAINT `sales_ibfk_2` FOREIGN KEY (`unit`) REFERENCES `product_units` (`unit`);
140COMMIT;
141