SQL table creation help

Question

I took an application that has an SQL server. There are a few tables, but two of which I am worried about are the following:

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


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

My problem is that the SecurityGroups field is a list of GroupID values, separated by commas.

So, the Profile table is as follows:

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

A profile can have multiple security groups, and a security group can be in multiple profiles

Any suggestions on how to do this?

Answer 1

It’s good that you want to rebuild this, because in general, comma-delimited lists do not apply to SQL databases.

, SecurityGroups QAProfile:

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 2

, :

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

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

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

Answer 3

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

Answer 4

• ProfileSecurityGroups
• SecurityGroups Profile.

Answer 5

, , :

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

Answer 6

• ​​ ProfileToGroup:

  ProfileID GroupID

• SQL script, "/".
• , SecurityGroups QAProfile