CODEWITHSUNDEEP
sql-server
Olivarsham
Olivarsham

Reputation: 1731

Maintaining ''Items to Groups relationship" in SQL Server

I am using two tables for maintaining Items-Groups relationship.

I have items table which will have two columns ItemName and Groups.

The other table is Group which has a column GroupName

User will add one or more groups to each item. The user will then query for no of items assigned to that group.

Currently I am appending the GroupName to Groups column and while querying for no of groups I use %Groups% in where query.

But if the user adds the same group twice, I need to check and remove.

Is there any better way to maintain these relationships?

Upvotes: 0

Views: 109

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239754

Don't try to store multiple items in a single column(*).

Instead, create 3 tables - Items, Groups and ItemGroups. ItemGroups will store 1 row for each group that an item belongs to, e.g.:

CREATE TABLE ItemGroups (
     ItemID int not null,
     GroupID int not null,
     constraint FK_ItemGroups_Items FOREIGN KEY (ItemID) references Items (ItemID),
     constraint FK_ItemGroups_Groups FOREIGN KEY (GroupID) references Groups (GroupID),
     constraint PK_ItemGroups PRIMARY KEY (ItemID,GroupID)
)

Note that, by adding the primary key on ItemID,GroupID, we've prevented duplicates from occurring, with no further work.

(*) SQL has one data structure designed for containing multiple items. It doesn't have arrays, lists, dictionaries, etc. It just has one structure. And it's called a table. Things work well if you use this structure. Things don't work nearly so well if you try to fake some other kind of structure using e.g. string values stored in a single column.

Upvotes: 1

Bernhard Barker
Bernhard Barker

Reputation: 55619

Having a concatenated field of groups prevents automated database integrity enforcing and disallows indexing, thus certain queries will be very slow.

Add (or change) primary key ItemId to Items and have a foreign key ItemId in Group. Then you can do SELECT COUNT(*) FROM Group WHERE ItemId = X to get the number of groups assigned to an item.

The above assumes a one-to-many relationship of items to group, if it's a many-to-many relationship:

Instead of the foreign key in Group, have another table ItemGroupMapping(ItemId FK, GroupId FK).

Also adding primary key GroupId to Group might be a good idea i.t.o good database design.

Upvotes: 1

Related Questions