> 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.
