CODEWITHSUNDEEP
sqlsql-serverdatabase-designmany-to-many
zeroef
zeroef

Reputation: 1969

SQL table design help

I've taken over an application that has a SQL backend. There are multiple tables, but the two that I'm concerned about are these:

QAProfile
---------
ProfileID <pk> -int
ProfileName
SecurityGroups -varchar(max)


SecurityGroups
--------------
GroupID <pk> -int
GroupName

My issue is that the the SecurityGroups field is a comma delimited list of GroupID values.

So the Profile table looks like this:

-------------------------------------------- 
| ProfileID | ProfileName | SecurityGroups | 
-------------------------------------------- 
|    1      |     foo     |  ,1,13,34,56,  | 
-------------------------------------------- 
|    2      |     bar     |  ,4,13,29,56,  | 
--------------------------------------------

A profile can have muliple security groups and a security group can be on muliple profiles

Any suggestions on how to re-engineer this?

Upvotes: 3

Views: 198

Answers (6)

IordanTanev
IordanTanev

Reputation: 6250

If it was me, I would do it like this:

QAProfile
---------
ProfileID <pk> -int
ProfileName

SecurityGroups
--------------
GroupID <pk> -int
GroupName

QASecurityGroups
----------------
ProfileID<pk>
GroupID <pk>

Upvotes: 4

Daniel Vassallo
Daniel Vassallo

Reputation: 344571

It's good that you're looking to re-engineer this, since in general comma-delimited lists don't belong to SQL databases.

It looks like a simple junction table can replace the SecurityGroups column of the QAProfile table:

CREATE TABLE SecurityGroups_Profiles (
    GroupID     int    NOT NULL,
    ProfileID   int    NOT NULL,
    PRIMARY KEY (GroupID, ProfileID),
    FOREIGN KEY (GroupID) REFERENCES SecurityGroups (GroupID),
    FOREIGN KEY (ProfileID) REFERENCES QAProfile (ProfileID)
);

Upvotes: 10

Damir Sudarevic
Damir Sudarevic

Reputation: 22177

  • add table ProfileSecurityGroups
  • parse that string for each profile and add pairs to the table
  • change the app layer to use new structure
  • drop the SecurityGroups column from the Profile table

alt text

Upvotes: 2

Keith
Keith

Reputation: 5391

  • A new Table ProfileToGroup should be added:

    ProfileID GroupID

  • You will need to write a SQL script to parse out the list of groups and insert records into the new table for each Profile/Group combination.
  • Finally, the SecurityGroups column in the QAProfile table should be removed

Upvotes: 1

Sage
Sage

Reputation: 4937

Create Table QAProfileSecurityGroup (
 ProfileID int,
 GroupID int,
 PRIMARY KEY ( ProfileID, GroupID)
)

Upvotes: 2

James
James

Reputation: 12806

Yes piece of cake you could just create a junction table like this:

ProfileSecurityGroups
---------------------
Id, <pk> -int
ProfileId <fk>,
SecurityGroupId <fk>

Upvotes: 1

Related Questions