SQL table design help

Question

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?

Answer 1

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

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

Answer 2

Hi,
if it was me i loud do it like this

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

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

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

Best Regards,
Iordan

Answer 3

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)
);

Answer 4

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

Answer 5

• 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

Answer 6

• 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