Solved

multiple entries in one field

Posted on 2014-02-13
7
251 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
  • 4
  • 3
7 Comments
 
LVL 34

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 34

Accepted Solution

by:
Dan Craciun earned 65 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 34

Expert Comment

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

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
groupNoAdj 7 84
advertisement module in core php 4 144
[MYSQL]: Delete is very slow 4 53
reverse digits of a number using for loop 5 36
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
The viewer will learn how to implement Singleton Design Pattern in Java.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.

919 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now