pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Eric McKeown <ericm@palaver.net>
To: PGsql <pgsql-sql@postgreSQL.org>
Subject: dilemma
Date: Tue, 13 Oct 1998 21:24:42 -0500 (EST)
Message-ID: <Pine.LNX.3.96.981013211531.20289F-100000@farout.palaver.net> (raw)

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




view thread (3+ messages)  latest in thread

Message-ID: <Pine.LNX.3.96.981013211531.20289F-100000@farout.palaver.net>
Permalink:  ../Pine.LNX.3.96.981013211531.20289F-100000@farout.palaver.net/
Also on:    postgresql.org/message-id/Pine.LNX.3.96.981013211531.20289F-100000@farout.palaver.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: ericm@palaver.net
  Subject: Re: dilemma
  In-Reply-To: <Pine.LNX.3.96.981013211531.20289F-100000@farout.palaver.net>
  References: <Pine.LNX.3.96.981013211531.20289F-100000@farout.palaver.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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