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 |开发者_JAVA技巧 
-------------------------------------------- 
|    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?

---

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

---

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>

---

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

---

• 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

---

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

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

---

• 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