Re^2: OT - SQL choosing a schema for index tables

[bangor]

Thank you erix for your helpful reply. I am using MySQL.

- It seems unwise to name a table with 'index' or an '_index' suffix.
Noted - I will change

- ...Concentrate on getting easy-to-understand queries.
This is exactly what I am trying to do as I know some one else will be maintaining.

- Your option 2 (everything in one table) is called EAV and is generally frowned upon.
Thanks for the links, and yes Option 2 seems to be a bad idea.

Maybe you should try to show us the SQL-'hairiness' that you fear. It probably isn't as bad as you think :)

I don't have any hairiness at the moment, what I have is a Perl loop which queries each table in turn:

my ($self,$params) = @_;
my %tables = (
    origin      => 'tree_origin',
    category    => 'tree_flowering',
    ripe        => 'tree_ripe',
    usage       => 'tree_usage',
);
my $ids;
for my $name (keys %tables) {
    my $value = $params->{$name} or next;
    $ids = $self->search_index_table( $tables{$name}, $value, $ids );
    last unless $ids;
}

And the sub to query the database is something like this...

sub search_index_table {
    my ( $self, $table, $value, $ids ) = @_;

    my $query = "SELECT id FROM $table WHERE name=?";
    if ($ids) {
        my $id_string = join(',', @$ids );
        $query .= " AND id IN ($id_string)";
    }

    my @results;
    my $dbh = $self->{'dbh'};
    my $sth = $dbh->prepare($query);
    $sth->execute($value);
    while ( my ($id) = $sth->fetchrow_array() ) {
        push(@results,$id);
    }
    return (@results ? \@results : undef);
}

This all works correctly, but I can't help thinking that all these calls to the database server (which is on another machine) is very inefficient. I suppose my real itch is that there are only a couple of hundred rows in the main table, and I have another 10 or so tables to index the multiple-valued fields - from my reading this seems the correct way to do things, but at the same time feels over-engineered for such a small dataset.

[erix]

It's a bit unusual but if it works well and performs well, I wouldn't worry about it (because the data is so small).

But it would indeed (as already mentioned upthread) make more sense to have an SQL statement that JOINs the tables and that retrieves the rows that you want in one trip to the databaseserver. (But it's quite possible that you'd hardly notice a speed difference.)