-1

I have two tables says, val_taxes and val_tax_groups the first table has all the tax separately like so

val_taxes

| id | tax_name | tax_value |
-----------------------------
| 1  | sgst     | 2.4       |
| 2  | cgst     | 2.4       |
| 3  | service  | 2.4       |

val_tax_groups

| id | tax_group_name |
-----------------------
| 1  | group_1        |
| 1  | group_2        | 

The question is that, how do I connect these two tables? Should I add a foreign key in the val_taxes table or in val_tax_groups table?

I don't want to use comma separated values. If I introduce a foreign key in val_tax_groups then it might have duplicate tax_group_name as any val_taxes can be associated with any val_tax_groups.

For example; sgst could be present in group1 as well as group2, this will create a duplicate? How can I overcome this?

  • 3
    What is the meaning of val_tax_group.id? – eckes Feb 12 at 12:34
1

any val_taxes can be associated with any val_tax_groups

It sounds like you have a many-to-many relationship. You'll need a new table with which to join these two. I agree with Lennart that you should use more explicit names rather than just "ID". Something like this:

CREATE TABLE TaxGroupMemberships
(
TaxID       INT NOT NULL REFERENCES val_taxes (TaxID)
TaxGroupID  INT NOT NULL REFERENCES val_tax_groups (TaxGroupID),
PRIMARY KEY (TaxID, TaxGroupID),
UNIQUE (TaxGroupID, TaxID)
)

My MySQL DDL skills are very rusty, so apologies if this syntax isn't perfect.

Note that the UNIQUE constraint isn't necessary for data integrity, it's just an optimization for when you run queries against TaxGroupMemberships.TaxGroupID.

Also note that taxes can change. If you'd like to be flexible, you should add a DateEffective field, and include it in both constraints, so you can query "what taxes were in which groups as of date X?"

1

First, let's name the columns properly:

CREATE TABLE val_taxes
( tax_id ...
, ...
);

CREATE TABLE val_tax_groups
( tax_group_id ...
, ...
);

Now, I assume that a val_tax belongs to a val_tax_group, so we need an attribute for that:

CREATE TABLE val_taxes
( tax_id ...
, ...
, tax_group_id ...
);

It is now possible to add a foreign key in val_taxes as:

ALTER TABLE val_taxes ADD CONSTRAINT ...
    FOREIGN KEY (tax_group_id)
    REFERENCES val_tax_groups (tax_group_id)

or have it directly in the table definition

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.