Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

Re: Re: Yet Another Half Perl Half mySQL Question

by jreades (Friar)
on Jul 06, 2001 at 22:08 UTC ( #94535=note: print w/replies, xml ) Need Help??

in reply to Re: Yet Another Half Perl Half mySQL Question
in thread Yet Another Half Perl Half mySQL Question

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 );

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).

Replies are listed 'Best First'.
Re: Re: Re: Yet Another Half Perl Half mySQL Question
by eejack (Hermit) on Jul 06, 2001 at 23:15 UTC
    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 = '' 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 }
    But thanks for pointing out a nicer way of doing it than I did.



Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://94535]
[james28909]: i guess i should have done it in perl. i bet it woulnt have taken me 5 hours to figure out haha
[choroba]: In the end, I was able to upgrade Perl from 5.8.3 to 5.22 and install Net::SFTP::Foreign , at least for the task involved
[james28909]: what would be the best way to capture that stream with perl? ffmpeg args -rtp rtp://
[LanX]: if I was forced to talk about all security risks I encountered oO
[james28909]: if i did that with ffmpeg, i could then listen on the port with perl right?
[LanX]: one of my clients filtered a menu linking to Web pages according to user rights. .. but he didn't secure the access to those unlisted pages

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (9)
As of 2017-05-22 21:51 GMT
Find Nodes?
    Voting Booth?