1

I have a table for tax implementation in my application, but I am not sure the design I have done for the table. Can someone review it?

val_taxes is the table which has all types of tax listed in the table

CREATE TABLE `val_taxes` (
  `id` int(255) NOT NULL,
  `tax_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `tax_value` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `val_taxes` (`id`, `tax_name`, `tax_value`) VALUES
(1, 'SGST', '9.00'),
(2, 'CGST', '9.00'),
(3, 'Service', '1.00'),
(4, 'State', '0.25');

val_tax_group is the table where multiple tax can be grouped

CREATE TABLE `val_tax_groups` (
  `id` int(255) NOT NULL,
  `tax_group_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `val_tax_groups` (`id`, `tax_group_name`) VALUES
(2, 'GST'),
(3, 'Chennai Tax');

tax_mapping is the table for mapping the val_taxes to val_tax_groups

CREATE TABLE `tax_mapping` (
  `id` int(255) NOT NULL,
  `valtaxgroups_id` int(255) NOT NULL,
  `valtaxes_id` int(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `tax_mapping` (`id`, `valtaxgroups_id`, `valtaxes_id`) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 2, 3),
(4, 2, 4);

Given all the tables and their descriptions, is the architecture correct? Or can we reduce it down to two tables without any duplications?

1

You have 2 entities (taxes and groups) joined as M:N. 3-table structure for to store this data is optimal in most cases. Additional recommendations:

1) declare id field in val_taxes and val_tax_groups as a primary key and alter their datatype to INT or BIGINT;

2) remove id field from tax_mapping table structure;

3) alter valtaxgroups_id and valtaxes_id datatype to INT or BIGINT;

4) define (valtaxgroups_id,valtaxes_id) field combination as primary key;

5) create foreign key references from valtaxgroups_id to val_tax_groups.id and from valtaxes_id to val_taxes.id.

Akina

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.