Re: Design Table & Search Question

Thank you, Michael! I'm looking some examples and doing tests to find the
best search solution.

Best,

On 5/30/07, Michael Glaesemann <grzm(at)seespotcode(dot)net> wrote:
>
>
> On May 30, 2007, at 13:59 , Gabriel Laet wrote:
>
> > I'm developing an application where basically I need to store cars.
> > Every car has a Make and Model association. Right now, I have three
> > tables: MAKE, MODEL (make_id) and CAR (model_id).
> >
> > 1) I'm not sure if I need or not to include "make_id" to the CAR
> > table. To me, it's clear to associate just the Model. Am I right?
>
> Based on your rough sketch, I believe so. Here's what I imagine your
> schema being:
>
> CREATE TABLE make
> (
> make_id INTEGER PRIMARY KEY
> , make_name TEXT NOT NULL UNIQUE
> );
>
> CREATE TABLE model
> (
> model_id INTEGER PRIMARY KEY
> , model_name TEXT NOT NULL UNIQUE
> , make_id INTEGER NOT NULL
> REFERENCES make
> );
>
> CREATE TABLE car
> (
> car_id INTEGER PRIMARY KEY
> , vin TEXT NOT NULL UNIQUE
> , model_id INTEGER NOT NULL
> REFERENCES model
> );
>
> In this schema, you can find the make of a given car by joining
> through the model table, e.g.,
>
>
> SELECT make_name, model_name, vin
> FROM make
> NATURAL JOIN model
> NATURAL JOIN car;
>
> > 2) I'm thinking in the best way to search content. I'll need to search
> > data across multiple-tables, and I'm not sure about the best way to do
> > that. Should I use TSearch2 or just a bunch of LIKEs and JOINs
> > statements?
>
> This isn't really an area I have much experience with, so I'll leave
> it for someone else. You might want to think of adding a column on
> the car table that includes the make and model names so they could be
> easily searched by hitting a single table. I think you'd need
> triggers to update that search column, but it might help. The key is
> to benchmark the app and see how it performs using different strategies.
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>

--
~Gabriel Laet