Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

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


Comment on Re: Re: Yet Another Half Perl Half mySQL Question
Download Code
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 = '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 }
    But thanks for pointing out a nicer way of doing it than I did.

    Thanks,

    EEjack

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://94535]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (7)
As of 2015-07-30 02:24 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (269 votes), past polls