Horton

Thread

  1. dilemma

    Eric J McKeown <ericm@palaver.net> — 1998-10-14T02:24:42Z

    Hi folks, 

I'm facing a little db design dilemma.  I'm constructing a used car
database, and one of the pieces of information that I need to store about
each car is what options it comes with.  The options themselves are stored
in a table, and the structure of that table looks like this:

option_id       int4
display         varchar(40)
description     text

Now, I also have a cars table, and each car has a basic set of information
that is stored there.  The problem is this:  the number of options that
will be associated with any given vehicle is variable.  So, my initial
inclination was to create a separate table to store the options for each
car and put two fields in that table:

car_id     int4
option_id  int4

Then, to get the options for any given vehicle, I just select all its rows
out of the car_options table, and join that with the options table to get
the description for each option.

Trouble is, I anticipate there might be as many as 10 options for any
given vehicle, and once I get 500 or so cars in the database, then I've
got 500,000 rows in that table.  If I'm allowing people to search based on
the options that might come with a vehicle, I'm afraid that my search
might take an awfully long time.

Is there a better way to design this?  I thought about making the option
ids for each vehicle a comma-delimited text field, but maybe the search
would be even slower if I did that.  Since I'm not a SQL expert, I don't
know, but I'm asking all the experts out there--how should I set this
thing up??

Many TIA...

_______________________
Eric McKeown
ericm@palaver.net
http://www.palaver.net

  2. Re: [SQL] dilemma

    Herouth Maoz <herouth@oumail.openu.ac.il> — 1998-10-14T09:46:13Z

    At 4:24 +0200 on 14/10/98, Eric McKeown wrote:


> Is there a better way to design this?  I thought about making the option
> ids for each vehicle a comma-delimited text field, but maybe the search
> would be even slower if I did that.  Since I'm not a SQL expert, I don't
> know, but I'm asking all the experts out there--how should I set this
> thing up??

Your solution is the best, most classic solution. You just need to index
the relational table properly (Put an index on each of the fields).

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

  3. Using the IN predicate in an UPDATE...

    Thomas Good <tomg@admin.nrnet.org> — 1998-10-14T13:06:01Z

    Herouth,

This query takes 20 years (poetic licence invoked ;-).
Do you have a smarter way to accomplish this end:

UPDATE table1 SET id = 2
WHERE rec_num IN
 ( SELECT rec_num 
   FROM table1
   WHERE id = 1
  );     

This is an attempt to cleanup some user error...I have an old
foxpro db that uses a char str as an index and this index has no
check constraints (like, making the index unique...ouch.)
The new pg db is performing nicely, now that it's live...and 
housing 12 years worth of data.  But I am saddled with quite a bit
of housekeeping - correcting anomalies that were part and parcel
of the original (foxpro) design.  Or absence thereof.

Thanks much!
Tom

    ---------- Sisters of Charity Medical Center ----------
                   Department of Psychiatry
                            ----     
    Thomas Good                          <tomg@q8.nrnet.org>
    Coordinator, North Richmond C.M.H.C. Information Systems
    75 Vanderbilt Ave, Quarters 8        Phone: 718-354-5528
    Staten Island, NY   10304            Fax:   718-354-5056

  4. Re: Using the IN predicate in an UPDATE...

    Herouth Maoz <herouth@oumail.openu.ac.il> — 1998-10-14T14:42:32Z

    At 15:06 +0200 on 14/10/98, Thomas Good wrote:


> This query takes 20 years (poetic licence invoked ;-).
> Do you have a smarter way to accomplish this end:
>
> UPDATE table1 SET id = 2
> WHERE rec_num IN
>  ( SELECT rec_num
>    FROM table1
>    WHERE id = 1
>   );
>
> This is an attempt to cleanup some user error...I have an old
> foxpro db that uses a char str as an index and this index has no
> check constraints (like, making the index unique...ouch.)
> The new pg db is performing nicely, now that it's live...and
> housing 12 years worth of data.  But I am saddled with quite a bit
> of housekeeping - correcting anomalies that were part and parcel
> of the original (foxpro) design.  Or absence thereof.

I didn't know I became an SQL guru... I don't even have the proper version
of Postgres for subqueries...

It all depends on what you have in mind. First, is this an operation you
are likely to perform frequently, or is this a one-time fix you want to run
on your system?

Which fields are indexed? If the id field is indexed, it seems to me the
above query should not be *that* lengthy. In any case, an EXISTS query is
supposed to be more efficient. Especially if the only index is on rec_num.
Try this:

UPDATE table1 SET id = 2
WHERE EXISTS (
    SELECT *
    FROM table1 t1
    WHERE t1.rec_num = table1.rec_num
      AND id = 1
);

I hope the above table aliasing scopes rec_num correctly... I have no way
of trying it myself, because, as I said, I don't have 6.3 as yet.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

  5. Re: [SQL] Using the IN predicate in an UPDATE...

    Leslie Mikesell <les@mcs.net> — 1998-10-15T03:21:09Z

    According to Thomas Good:
> This query takes 20 years (poetic licence invoked ;-).
> Do you have a smarter way to accomplish this end:
> 
> UPDATE table1 SET id = 2
> WHERE rec_num IN
>  ( SELECT rec_num 
>    FROM table1
>    WHERE id = 1
>   );     

Am I missing something here or is this the same as:

UPDATE table1 SET id = 2 WHERE id = 1;

??
  Les Mikesell
    les@mcs.com

  6. Re: [SQL] dilemma

    Michael Shields <shields@crosslink.net> — 1998-10-15T07:35:35Z

    In article <Pine.LNX.3.96.981013211531.20289F-100000@farout.palaver.net>,
Eric McKeown <ericm@palaver.net> wrote:
> Trouble is, I anticipate there might be as many as 10 options for any
> given vehicle, and once I get 500 or so cars in the database, then I've
> got 500,000 rows in that table.

How is that?  10 * 500 = 5,000 not 500,000.
-- 
Shields, CrossLink.

  7. Re: [SQL] Using the IN predicate in an UPDATE...

    Thomas Good <tomg@admin.nrnet.org> — 1998-10-15T12:37:53Z

    RE: optimizing this query... ;-)

> UPDATE table1 SET id = 2
> WHERE rec_num IN
>  ( SELECT rec_num
>    FROM table1
>    WHERE id = 1
>   );

1) Herouth, thanks...I aim to try this where I really need to do
table aliasing in an update (it will come up as I continue to port
the foxpro stuff...)  I know how to do it in a SELECT but was 
befuddled (per usual) re aliasing in an UPDATE.  Thanks again.

> Try this:
> 
> UPDATE table1 SET id = 2
> WHERE EXISTS (
>     SELECT *
>     FROM table1 t1
>     WHERE t1.rec_num = table1.rec_num
>       AND id = 1
> );
> 
> I hope the above table aliasing scopes rec_num correctly... I have no way
> of trying it myself, because, as I said, I don't have 6.3 as yet.
> 
> Herouth

2)  Gene - you're my kind of guy...but I've been scolded so many times
by my perl buddies for being an incurable shell scripter that I really
do try to make SQL do the work whenever possible...thanks for the reply!

> I think the urge to do everything you might need inside the server 
> can be disabling. I would export the table to a file, fix it 
> (with perl, sed, whatever) and import it back again.
> --Gene

> Am I missing something here or is this the same as:
> UPDATE table1 SET id = 2 WHERE id = 1;
> ??
>  Les Mikesell
>    les@mcs.com

3) Les,  What are ya, some kinda smarta**?  Yeah, somebody was missing
something, but it weren't you...  ;-)

BTW, I never did get UnixWare to work right re UUCP via TCP (sending).
But receiving is happening so my Linux boxes poll the UW box to grab
files and exchange the mail.  Dodgy workaround but it is UnixWare, after
all...the original white elephant.

Thanks for your help (again...!)

Cheers,
Tom

    ---------- Sisters of Charity Medical Center ----------
                   Department of Psychiatry
                            ----     
    Thomas Good                          <tomg@q8.nrnet.org>
    Coordinator, North Richmond C.M.H.C. Information Systems
    75 Vanderbilt Ave, Quarters 8        Phone: 718-354-5528
    Staten Island, NY   10304            Fax:   718-354-5056

  8. Re: [SQL] Using the IN predicate in an UPDATE...

    Herouth Maoz <herouth@oumail.openu.ac.il> — 1998-10-18T09:24:53Z

    At 14:37 +0200 on 15/10/98, Thomas Good wrote:


> > Am I missing something here or is this the same as:
> > UPDATE table1 SET id = 2 WHERE id = 1;
> > ??
> >  Les Mikesell
> >    les@mcs.com
>
> 3) Les,  What are ya, some kinda smarta**?  Yeah, somebody was missing
> something, but it weren't you...  ;-)

Hmmm. Are you sure? The original query you presented had different
semantics. It meant "If the recnum has any occurence with id=1, change all
its occurences (even those with id=17) to id=2".

That was the reason why I suggested an "exists" query instead. The above is
only equivalent when each recnum has only one id.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma