Creating a comprehensive search that queries multiple tables - Mailing list pgsql-general

Hi all,

I am attempting to build an application which returns a list of items where some piece of information associated with the item matches the entered text. My database is set up in the following way:

CREATE TABLE "public"."items"(

"id" int4 NOT NULL DEFAULT nextval('items_id_seq'::regclass),

"name" varchar(255) NOT NULL DEFAULT 'Unknown'::character varying,

"description_id" int4 ,

PRIMARY KEY ("id")

);

CREATE TABLE "public"."notes"(

"id" int4 NOT NULL DEFAULT nextval('notes_id_seq'::regclass),

"item_id" int4 ,

"kind" varchar(255) NOT NULL ,

"event_id" int4 ,

"text" text NOT NULL ,

PRIMARY KEY ("id")

) ;

CREATE TABLE "public"."item_categories"(

"id" int4 NOT NULL DEFAULT nextval('item_categories_id_seq'::regclass),

"item_id" int4 ,

"category_id" int4 ,

"association" varchar(255) DEFAULT NULL::character varying,

PRIMARY KEY ("id")

);

CREATE TABLE "public"."categories"(

"id" int4 NOT NULL DEFAULT nextval('categories_id_seq'::regclass),

"name" varchar(255) NOT NULL ,

PRIMARY KEY ("id")

);

CREATE TABLE "public"."events"(

"id" int4 NOT NULL DEFAULT nextval('events_id_seq'::regclass),

"association" varchar(255) DEFAULT NULL::character varying,

"item_id" int4 ,

PRIMARY KEY ("id")

) ;

CREATE TABLE "public"."event_locations"(

"id" int4 NOT NULL DEFAULT nextval('event_locations_id_seq'::regclass),

"event_id" int4 ,

"location_id" int4 ,

"association" varchar(255) DEFAULT NULL::character varying,

PRIMARY KEY ("id")

);

CREATE TABLE "public"."event_people"(

"id" int4 NOT NULL DEFAULT nextval('event_people_id_seq'::regclass),

"event_id" int4 ,

"person_id" int4 ,

"association" varchar(255) DEFAULT NULL::character varying,

PRIMARY KEY ("id")

);

CREATE TABLE "public"."people"(

"id" int4 NOT NULL DEFAULT nextval('people_id_seq'::regclass),

"first_name" varchar(255) DEFAULT NULL::character varying,

"middle_name" varchar(255) DEFAULT NULL::character varying,

"last_name" varchar(255) DEFAULT NULL::character varying,

"suffix" varchar(255) DEFAULT NULL::character varying,

"prefix" varchar(255) DEFAULT NULL::character varying,

"culture" varchar(255) DEFAULT NULL::character varying,

"description" text ,

PRIMARY KEY ("id")

) ;

CREATE TABLE "public"."locations"(

"id" int4 NOT NULL DEFAULT nextval('locations_id_seq'::regclass),

"name" varchar(255) DEFAULT NULL::character varying,

PRIMARY KEY ("id")

)  WITHOUT OIDS;

Items have many notes and events, and events in turn have many people and locations associated with them. For example, if a user types in the word ‘dog’, I would like to be able to find all items which were either made in ‘Dog River’, made by ‘Dog the Bounty Hunter’, have the name ‘dog collar’, or have a note attached to them stating that they were ‘used to tame wild dogs’.

I would also like to know which field the text was matched in. The data associated with the item doesn’t change very often, so I was thinking of creating a tsvector column in the items table which holds all the text associated with each item, for the purpose of searching. I am not sure how I would be able to figure out which field was matched using this approach, however. Does anyone have any thoughts or ideas on this issue?

Any pokes in the right direction would be much appreciated.

Thanks,

Ryan Wallace