Hi,

I was wondering if anyone could help me out with this.
I have a tb_my_prefs table with an array (int) type field:
name | cars_i_like
dave | {1,3}

I also have a tb_cars table with the data that describes each car:

car_id | make | model | year | top_speed
1 | ford | mustang | 1996 | 140
2 | toyota | camry | 2001 | 140
3 | audi | A4 | 1998 | 160

I'm trying to create a view:
CREATE OR REPLACE VIEW vi_my_prefs (name, cars_i_like, the_makes,
the_models, the_top_speeds)
AS
SELECT name, cars_i_like, make, model, top_speed
FROM (tb_my_prefs JOIN tb_cars ON cars_i_like=car_id);

Considering the tb_cars columns are not an array type, I'm having trouble
creating the view. Here is what I'd like for a query to return from this
view:

SELECT * FROM vi_my_prefs WHERE name='dave';

name | cars_i_like | the_makes | the_models | the_top_speeds
dave | {1,2} | {"ford","audi"} | {"mustang","A4"} | {"140","160"}

If the view is not possible, can I make this query some other way?

Thanks a bunch

Dave

Dave <withheld (AT) nospam (DOT) thanks> wrote:
What you plan to do is a bad idea.
Your database design is wrong.

You are trying to make a join over a field that cannot be indexed
(the array) so that you will end up with full table scans whenever this
join is performed.

You should redesign the database to adhere to first normal form,
by doing away with that array and creating a 'link' table with
fields (name, car_id) that models the m-n relation between people and
cars.

Yours,
Laurenz Albe

Laurenz,

Thanks for your reply. I'm not quite understanding this. Are you suggestion
I create a table with a preferences field not an array? Example:
name | cars_i_like
dave | 1
dave | 3

and then I do a join on cars_i_like field?
If I'm not following you, can you please elaborate a bit more?

Thanks,

Dave



"Laurenz Albe" <invite (AT) spam (DOT) to.invalid> wrote

Dave <withheld (AT) nospam (DOT) thanks> wrote:
Gladly. But please don't top-post.

I suggest that you define three tables (the definitions are samples):

create table ppl(pid integer primary key, name text not null)

create table car(cid integer primary key, make text, model text, ...)

create table likes(
pid integer not null
constraint likes_fk1 references ppl(pid) on delete cascade,
cid integer not null
constraint likes_fk2 references car(cid) on delete cascade,
constraint likes_pkey primary key(pid, cid))

and an index

create index likes_fk2 on likes(cid)


To stick with your example in the original post,
let's assume that "dave" has pid 42.

Then "likes" should contain the rows (42, 1) and (42, 3).


You can then write efficient queries like:

select name, make, model, ... from ppl, likes, car
where name='dave'
and ppl.pid = likes.pid and likes.cid=car.cid

Yours,
Laurenz Albe