[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

multiple entries in one field

Posted on 2014-02-13
7
Medium Priority
?
275 Views
Last Modified: 2014-02-14
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
Comment
Question by:jw124210
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39857682
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
 

Author Comment

by:jw124210
ID: 39857747
Thanks Dan

I am not quite clued up with SQL. I will try that. will let you know.
0
 

Author Comment

by:jw124210
ID: 39858855
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 35

Accepted Solution

by:
Dan Craciun earned 260 total points
ID: 39859265
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
 

Author Comment

by:jw124210
ID: 39859937
Hey Dan!! Thanks, seems it's working, just need to run a few more tests and I will close this one.
0
 

Author Comment

by:jw124210
ID: 39860073
Thanks Dan! much appreciated, all working fine. I will apply the same method for users and roles.
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39860087
Glad I could help!
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this article, we’ll look at how to deploy ProxySQL.
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question