• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

multiple entries in one field

Hi Experts

I created a table in mySQL. The purpose is to groups servers I want to add multiple servers to a group. How do I achieve this? See my table below:

CREATE TABLE servergroups_tbl(`servergrpid` VARCHAR(100), servergrp VARCHAR(100) NOT NULL, servername VARCHAR(100), servertype VARCHAR(50) NOT NULL, userid VARCHAR(100), PRIMARY KEY (`servergrpid`))ENGINE=InnoDB;

I want to be able to add multiple servers in the servername field, as these servers will belong to a specific group field in servergrp which contains the name of the server group.
What is the SQL syntax to achieve this.
0
jw124210
Asked:
jw124210
  • 4
  • 3
1 Solution
 
Dan CraciunIT ConsultantCommented:
Normally, you don't.
You need to create another table, called servers_tbl or whatever works for you, with at least 3 fields:
id
servername
group_id

where group_id is the foreign key to the servergroups_tbl
You'll probably want to add servertype here too.

And delete the server details from the servergroups table.

After that, just join the tables when you need to pull data.

HTH,
Dan
0
 
jw124210Author Commented:
Thanks Dan

I am not quite clued up with SQL. I will try that. will let you know.
0
 
jw124210Author Commented:
Hi Dan

I am still new to SQL, this is what I intend to do. Insert into servergroups_tbl a list of servers from the server_tbl servername field. I have added the servers_tbl table and aded the foreign key.
How do I insert the data into the servergroup_tbl making use of foreign key for a specific group name. I want to able to retrieve servergroups and servers under that servergroup.
If possible an example would help on insert and select.

Here are my tables.
servergroups_tbl
==============
CREATE TABLE servergroups_tbl( id unasigned int(10) NOT NULL, `group_id` VARCHAR(10), servergrpname VARCHAR(100) NOT NULL, servername VARCHAR(100), userid VARCHAR(100), PRIMARY KEY (`group_id`))ENGINE=InnoDB;

servers_tbl
=========
CREATE TABLE servers_tbl(`id` VARCHAR(10) NOT NULL, servername VARCHAR(255)NOT NULL, servertype VARCHAR(255)NOT NULL, group_id INT(10), PRIMARY KEY (`id`), Foreign Key (group_id) REFERENCES servergroups_tbl(group_id));)ENGINE=InnoDB;
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
Dan CraciunIT ConsultantCommented:
You need to normalize your data:
servergroups_tbl
`id` unsigned int(10) NOT NULL AUTO INCREMENT,
servergrpname VARCHAR(100) NOT NULL,
userid VARCHAR(100),
PRIMARY KEY (`id`)

servers_tbl
`id` unsigned int(10) NOT NULL AUTO INCREMENT,
servername VARCHAR(255) NOT NULL,
servertype VARCHAR(255) NOT NULL,
group_id INT(10),
PRIMARY KEY (`id`),
Foreign Key (group_id)

If a user is common for a whole group, then it should stay in the group table, otherwise you should put it in the servers table.

You will first need to insert servergroups data (you enumerate your server groups and your group users, if you kept the users in that table).
INSERT INTO servergroups_tbl (servergrpname, userid) VALUES ("group 1", "1")

Open in new window


Then you insert the server data in the server table:
INSERT INTO servers_tbl (servername, servertype, group_id) VALUES ("server1", "fileserver", "1")

Open in new window


If you want to retrieve all the servers inside a server group:
SELECT servergrpname, servername, servertype 
    FROM servergroups_tbl 
    JOIN servers_tbl ON servergroups_tbl.id = servers_tbl.group_id
    WHERE servergrpname = "group 1"

Open in new window


HTH,
Dan
0
 
jw124210Author Commented:
Hey Dan!! Thanks, seems it's working, just need to run a few more tests and I will close this one.
0
 
jw124210Author Commented:
Thanks Dan! much appreciated, all working fine. I will apply the same method for users and roles.
0
 
Dan CraciunIT ConsultantCommented:
Glad I could help!
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now