Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW

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]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (9)
As of 2017-06-25 19:17 GMT
Find Nodes?
    Voting Booth?
    How many monitors do you use while coding?

    Results (569 votes). Check out past polls.