X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.193])
	by svr1.postgresql.org (Postfix) with ESMTP id 40BB630F8A8
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 24 Jun 2003 05:19:28 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new,
	port 10024) with ESMTP id 20371-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 24 Jun 2003 02:19:17 -0300 (ADT)
Received: from water.oasis.net.au (water.oasis.net.au [210.8.139.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 8E80730F88D
	for <pgsql-sql@postgresql.org>; Tue, 24 Jun 2003 02:19:16 -0300 (ADT)
Received: from oasis.net.au (guardian [210.8.139.5])
	by water.oasis.net.au (8.9.3/8.9.3/Debian 8.9.3-21) with ESMTP id
	PAA24969
	for <pgsql-sql@postgresql.org>; Tue, 24 Jun 2003 15:35:36 +1000
X-Authentication-Warning: water.oasis.net.au: Host guardian [210.8.139.5]
	claimed to be oasis.net.au
Message-ID: <3EF7DF74.40700@oasis.net.au>
Date: Tue, 24 Jun 2003 15:19:48 +1000
From: Rudi Starcevic <rudi@oasis.net.au>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.3) Gecko/20030312
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Database design - AGAIN
Content-Type: multipart/alternative;
	boundary="------------030307000701020308000904"
X-Spam-Status: No, hits=2.2 tagged_above=0.0 required=5.0 tests=HTML_10_20,
	HTML_MESSAGE, UPPERCASE_25_50, USER_AGENT_MOZILLA_UA, X_ACCEPT_LANG,
	X_AUTH_WARNING
X-Spam-Level: **
X-Archive-Number: 200306/278
X-Sequence-Number: 14036

This is a multi-part message in MIME format.
--------------030307000701020308000904
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

Sorry - I make some typo's - here is the question again.

I have a Spread Sheet which I need to make a searchable directory out of.
I'm in two minds on a couple things so I thought I'd ask.

I"m not sure whether to have on larger table or 2 smaller one with a 
join table.
This is a trimmed down version of the data.
The directory is of Camping grounds/parks.

Here is a sample of the fields:
Park Name
Info Kiosk
infokiosk
tent
caravan
toilets
wheelchair
water
shower
telephone

One large table may look like:

CREATE TABLE grounds
(
id serial PRIMARY KEY,
parkname varchar(120),
infokiosk bool NOT NULL,
tent bool NOT NULL,
caravan bool NOT NULL,
toilets bool NOT NULL,
wheelchair bool NOT NULL,
water bool NOT NULL,
shower bool NOT NULL,
telephone bool NOT NULL
);

Two smaller tables with a join table may look like:

CREATE TABLE grounds
(
gid serial PRIMARY KEY,
parkname varchar(120)
);

CREATE TABLE features
(
fid serial PRIMARY KEY,
feature varchar NOT NULL
);
INSERT INTO features ( feature ) VALUES ( 'infokiosk' );
INSERT INTO features ( feature ) VALUES ( ' tent' );
INSERT INTO features ( feature ) VALUES ( ' caravan' );
INSERT INTO features ( feature ) VALUES ( ' toilets' );
INSERT INTO features ( feature ) VALUES ( ' wheelchair' );
INSERT INTO features ( feature ) VALUES ( ' water' );
INSERT INTO features ( feature ) VALUES ( ' shower' );
INSERT INTO features ( feature ) VALUES ( ' telephone' );
);

-- join table
CREATE TABLE grounds_features
(
gid int4 REFERENCES grounds ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
fid int4 REFERENCES features ON UPDATE CASCADE ON DELETE CASCADE NOT NULL
);

For this task, a searchable directory, which design would be better.
In know the second is more flexible as new features can be added but I 
have very limited time and
I think the one larger table design would be quicker to build.

The front end will be either ColdFusion or PHP - I haven't decided yet.

Thank you kindly

Best regards
Rudi.









--------------030307000701020308000904
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
  <title></title>
</head>
<body>
<div class="moz-text-flowed"
 style="font-family: -moz-fixed; font-size: 16px;" lang="x-western">Hi, <br>
<br>
Sorry - I make some typo's - here is the question again.<br>
<br>
I have a Spread Sheet which I need to make a searchable directory out
of. <br>
I'm in two minds on a couple things so I thought I'd ask. <br>
<br>
I"m not sure whether to have on larger table or 2 smaller one with a
join table. <br>
This is a trimmed down version of the data. <br>
The directory is of Camping grounds/parks. <br>
<br>
Here is a sample of the fields: <br>
Park Name <br>
Info Kiosk <br>
infokiosk<br>
tent<br>
caravan<br>
toilets<br>
wheelchair<br>
water<br>
shower<br>
telephone<br>
<br>
One large table may look like: <br>
<br>
CREATE TABLE grounds <br>
( <br>
id serial PRIMARY KEY, <br>
parkname varchar(120), <br>
infokiosk bool NOT NULL, <br>
tent bool NOT NULL, <br>
caravan bool NOT NULL, <br>
toilets bool NOT NULL, <br>
wheelchair bool NOT NULL, <br>
water bool NOT NULL, <br>
shower bool NOT NULL, <br>
telephone bool NOT NULL <br>
); <br>
<br>
Two smaller tables with a join table may look like: <br>
<br>
CREATE TABLE grounds <br>
( <br>
gid serial PRIMARY KEY, <br>
parkname varchar(120) <br>
); <br>
<br>
CREATE TABLE features <br>
( <br>
fid serial PRIMARY KEY, <br>
feature varchar NOT NULL <br>
); <br>
INSERT INTO features ( feature ) VALUES ( 'infokiosk' ); <br>
INSERT INTO features ( feature ) VALUES ( ' tent' ); <br>
INSERT INTO features ( feature ) VALUES ( ' caravan' ); <br>
INSERT INTO features ( feature ) VALUES ( ' toilets' ); <br>
INSERT INTO features ( feature ) VALUES ( ' wheelchair' ); <br>
INSERT INTO features ( feature ) VALUES ( ' water' ); <br>
INSERT INTO features ( feature ) VALUES ( ' shower' ); <br>
INSERT INTO features ( feature ) VALUES ( ' telephone' ); <br>
); <br>
<br>
-- join table <br>
CREATE TABLE grounds_features <br>
( <br>
gid int4 REFERENCES grounds ON UPDATE CASCADE ON DELETE CASCADE NOT
NULL, <br>
fid int4 REFERENCES features ON UPDATE CASCADE ON DELETE CASCADE NOT
NULL <br>
); <br>
<br>
For this task, a searchable directory, which design would be better. <br>
In know the second is more flexible as new features can be added but I
have very limited time and <br>
I think the one larger table design would be quicker to build. <br>
<br>
The front end will be either ColdFusion or PHP - I haven't decided yet. <br>
<br>
Thank you kindly <br>
<br>
Best regards <br>
Rudi. <br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
</div>
</body>
</html>

--------------030307000701020308000904--