Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
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
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 perusing the Monastery: (11)
As of 2014-09-19 19:02 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (144 votes), past polls