pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddilemma
3+ messages / 3 participants
[nested] [flat]
* dilemma
@ 1998-10-14 02:24 Eric McKeown <ericm@palaver.net>
0 siblings, 2 replies; 3+ messages in thread
From: Eric McKeown @ 1998-10-14 02:24 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] dilemma
@ 1998-10-14 09:46 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Eric McKeown <ericm@palaver.net>
1 sibling, 0 replies; 3+ messages in thread
From: Herouth Maoz @ 1998-10-14 09:46 UTC (permalink / raw)
To: Eric McKeown <ericm@palaver.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] dilemma
@ 1998-10-15 07:35 Michael Shields <shields@crosslink.net>
parent: Eric McKeown <ericm@palaver.net>
1 sibling, 0 replies; 3+ messages in thread
From: shields@crosslink.net @ 1998-10-15 07:35 UTC (permalink / raw)
To: Eric McKeown <ericm@palaver.net>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~1998-10-15 07:35 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-10-14 02:24 dilemma Eric McKeown <ericm@palaver.net>
1998-10-14 09:46 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1998-10-15 07:35 ` Michael Shields <shields@crosslink.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox