Make sure that you have some unique ID for each row of the main table in which you need this 'set'. Call this "mainUniqueID"

Create a new table, with a different uniqueID (this is optional, but I still like unique ID's on all my tables), "otherUniqueID", so that the columsn will be "otherUniqueID" (your index), "mainUniqueID" which will NOT be unique, and "yourData". For each item that is in your list for that mainUniqueID, you'll have a row in this second table.

To get the list, just "SELECT yourData FROM secondTable WHERE mainUniqueID=?". Other options are similar.

I know that some databases have SETs that you can use, but this is not necessarily reliable.

Optionally, you can always store the list as a string delimiated by characters you know will not be in it. If you have IPs, for example, you can easily convert these from 2^32 to ints, and the join ';' them. Mind you, these makes it a bit harder to select a specific IP out of a list.

Note: I'm a Postgres user, so don't know much about mySQL -- somethings below may not make sense in mySQL

since everybody else is suggesting an approach with two tables, I'll try to be creative and suggest a way with one table. I don't know the pros and cons between two tables and one table, but here goes...

If you want to push the processing to the backend DB, then you probably will have make table like so:

   CREATE TABLE name2ip (
          name   text -- don't make this a primary key!
          ip     text -- or inet, or whatever mySQL has
   );
[download]

Then just insert all the IP's in name:ip pairs. So it would be like ( John Doe, ip1 ), ( John Doe, ip2 ), ( Mary Jane, ip3 ), etc.... When you retrieve all IP's for a name, you do

   SELECT ip FROM name2ip WHERE name = somename;
[download]

This way you can push the processing of the ip's to the DB, withou doing any manual comparison. Like this

   SELECT name FROM name2ip WHERE ip = someip; -- or, LIKE blah or wha
+tever
[download]

Other ways that I can think of is maybe using an array for the type of the IP address column, but that suffers the same disadvantages as just storing a long text, with each IP address concatnated with some delimiter. Namely, since you can't do a "Does this array contain element X?" type of call from the SQL statement ( at least not in Postgres! ), you're back to processing your data manualy in perl, and you also most likely have some sort of upper limit to the size of the column. ( I think in Postgres it was 4k or 8k )

You may be able to use large objects as well, but again, you need to do some manual processing in perl, plus it's kind of annoying ( in my experience dealing with large objects, anyway )

split and join could do the task very well, or they are totally unsuitable. But that depends on how you are going to use the data. If you always want to return all IP numbers, and are never using them in a (database) join, then putting them in a string might work very well. If you need to search on IP number, or need them in a join, you need to do something else. Perhaps a one to many table, with two or three columns: first column is the name, second column is an IP number. You repeat the name for each IP number belonging to said name. You could use an optional third column with a sequence number.

-- Abigail

create table tbl_names (
id_name INT NOT NULL auto_increment,
str_name VARCHAR(255),
);

create table tbl_ipaddresses (
str_ipaddress VARCHAR(255),
id_name INT,
);
[download]

Then you can do schtuff like

@names = qw( firstname secondname );
@ips = ( "123.132.123.121" , "198.21.252.2" , "123.123.123.132"); 

$sql_statement = qq|INSERT INTO tbl_names (str_name) VALUES (?)|;
$sth = $dbh->prepare($sql_statement);

$sql_statement = qq|INSERT INTO tbl_ipaddresses (id_name, str_ipaddres
+s) VALUES (?,?)|;
$sth2 = $dbh->prepare($sql_statement);

foreach $name (@names){
    $rv = $sth->execute($name);
    my $id_name = $sth->{'insert_id'};
    
    foreach $ip (@ips){
        $rv = $sth2->execute($id_name, $ip);
    }
    
}
[download]

$sql_statement = qq|select str_ipaddress 
    from tbl_ipaddresses, tbl_names
    where tbl_names.str_name = 'firstname'
    and tbl_names.id_name = tbl_ipaddresses.id_name|;
$sth = $dbh->prepare($sql_statement);
$rv = $sth->execute;

while (    ($ipaddress) = $sth->fetchrow_array ){
    # do whatever
}
[download]

EEjack

This really isn't a great table design since you're not actually gaining anything by splitting the information this way. The lookups are slower than on a single table using a straighforward SELECT query along the lines of "SELECT ipaddress WHERE name=?", but you haven't gained any additional flexibility since everything is bound to the name_id.,

If you really want to use ids to track this information a better schema would be:

create table names (
   name_id INT NOT NULL auto_increment,
   name_str VARCHAR(255)
   );

create table ipaddresses (
   ipaddress_id INT NOT NULL auto_increment,
   ipaddress_value VARCHAT(255)
   );

create table ip_names (
   name_id INT NOT NULL,
   ipaddress_id INT NOT NULL
   );
[download]

This gives you a lot more flexibility -- reverse look ups on virtual hosts, for instance, and allows for extensibility at a later date (associating machine-specific information with an IP address id, and domain specific information with the name id).

I would tend to agree with you, but you really only need the two tables if the assumption is a one to many relationship.

But assuming a many to many relationship your schema is nicer, and certainly if you expand the scope, having an id on both the ip address and name is a good thing (most probably a necessary thing).

One minor point though, you can still do reverse lookups on my schema:

$sql_statement = qq|select tbl_names.str_name
           from tbl_ipaddresses, tbl_names
           where tbl_ipaddresses.str_ipaddress = '201.2.23.2'
           and tbl_names.id_name = tbl_ipaddresses.id_name|;
       $sth = $dbh->prepare($sql_statement);
       $rv = $sth->execute;

       while (    ($str_name) = $sth->fetchrow_array ){
           # do whatever
       }
[download]

But thanks for pointing out a nicer way of doing it than I did.

Thanks,

EEjack

Table Creation :

   $sql = "CREATE TABLE depend (\
      usedby VARCHAR(80) NOT NULL ,\
      uses   VARCHAR(80) NOT NULL \
      )";
   $dbh->do($sql);
[download]

   $sql = "SELECT usedby FROM depend where uses=$name";
   $aref  = $dbh->selectall_arrayref($sql);
   my @uses = map {$_->[0]} @$aref;

   $sql = "SELECT uses FROM depend where usedby=$name";
   $aref = $dbh->selectall_arrayref($sql);
   my @usedby = map {$_->[0]} @$aref;
[download]

For the pedants, there is replication of the name in the columns, but the duplication is outweighed by the ease of access (see how easy in lines of code it is to retrieve a list of names) and reduction in complexity by not having a name-to-id mapping.

BTW, the previous iteration of the module used a single entry in another table and used join/split, which is quicker, but less elegant in DB terms.

If you need raw speed, use Perl to do the split, if you want it cleaner and more DBish, use a table as above.

Full module is Depend::Module
--
Brovnik