Обсуждение: dilemma
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
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
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
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
> 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 > ); 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
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
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.
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
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