Re: Design Table & Search Question

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