Thread: Design Table & Search Question
Hi, 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? 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? I appreciate any help :) Thanks!
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
Thank you, Michael! I'm looking some examples and doing tests to find the best search solution.
Best,
--
~Gabriel Laet
Best,
On 5/30/07, Michael Glaesemann < grzm@seespotcode.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
tsearch indexes have to reside in the table where the data is, for the automagical functions that come with it to work. You can define a view that joins the tables, then search each of the index columns for the values you are looking for. In my experience, the LIKE searches are fast for relatively small datasets, but they are often implemented funny, where a search for 'ING' (the company) would return zillions of records with verbs (and gerunds) in them. tsearch is smarter than me. It knows where to stem words and rarely gives unexpected results. Automotive nomenclature will seldom be stemmed so you will get whole word searches which is usually fine. Good luck! - Ian On 30 May 2007 11:59:04 -0700, Gabriel Laet <gabriel.laet@gmail.com> wrote: > Hi, > > 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? > > 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? > > I appreciate any help :) > Thanks! > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >
Ian Harding wrote:
> tsearch indexes have to reside in the table where the data is, for the
> automagical functions that come with it to work. You can define a
> view that joins the tables, then search each of the index columns for
> the values you are looking for.
No they don't.
Joshua D. Drake
>
> In my experience, the LIKE searches are fast for relatively small
> datasets, but they are often implemented funny, where a search for
> 'ING' (the company) would return zillions of records with verbs (and
> gerunds) in them. tsearch is smarter than me. It knows where to stem
> words and rarely gives unexpected results. Automotive nomenclature
> will seldom be stemmed so you will get whole word searches which is
> usually fine.
>
> Good luck!
>
> - Ian
>
> On 30 May 2007 11:59:04 -0700, Gabriel Laet <gabriel.laet@gmail.com> wrote:
>> Hi,
>>
>> 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?
>>
>> 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?
>>
>> I appreciate any help :)
>> Thanks!
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>> choose an index scan if your joining column's datatypes do not
>> match
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
On 5/31/07, Joshua D. Drake <jd@commandprompt.com> wrote: > Ian Harding wrote: > > tsearch indexes have to reside in the table where the data is, for the > > automagical functions that come with it to work. You can define a > > view that joins the tables, then search each of the index columns for > > the values you are looking for. > > No they don't. > Hm? Allow me to clarify. To the best of my knowledge, and in my limited experience, the tsearch2() trigger function provided with tsearch will only index fields present in the table upon which it is created, in an index column present in the table upon which it is created. If I am incorrect in this assessment I would be glad to be corrected. Of course, custom triggers can be written to do anything from soup to nuts. - Ian A. Harding > Joshua D. Drake > >
Ian Harding wrote: > On 5/31/07, Joshua D. Drake <jd@commandprompt.com> wrote: >> Ian Harding wrote: >> > tsearch indexes have to reside in the table where the data is, for the >> > automagical functions that come with it to work. You can define a >> > view that joins the tables, then search each of the index columns for >> > the values you are looking for. >> >> No they don't. >> > > Hm? Allow me to clarify. > > To the best of my knowledge, and in my limited experience, the > tsearch2() trigger function provided with tsearch will only index > fields present in the table upon which it is created, in an index > column present in the table upon which it is created. If I am > incorrect in this assessment I would be glad to be corrected. > > Of course, custom triggers can be written to do anything from soup to nuts. Yes you would need a trigger to keep a separate search table but you can certainly join on that table for selects. J > > - Ian A. Harding > >> Joshua D. Drake >> >> > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/