Database design - AGAIN - Mailing list pgsql-sql

From Rudi Starcevic
Subject Database design - AGAIN
Date
Msg-id 3EF7DF74.40700@oasis.net.au
Whole thread Raw
Responses Re: Database design - AGAIN
Re: Database design - AGAIN
List pgsql-sql
<div class="moz-text-flowed" lang="x-western" style="font-family: -moz-fixed; font-size: 16px;">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
directoryout 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
haveon larger table or 2 smaller one with a join table. <br /> This is a trimmed down version of the data. <br /> The
directoryis 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 />
parknamevarchar(120), <br /> infokiosk bool NOT NULL, <br /> tent bool NOT NULL, <br /> caravan bool NOT NULL, <br />
toiletsbool NOT NULL, <br /> wheelchair bool NOT NULL, <br /> water bool NOT NULL, <br /> shower bool NOT NULL, <br />
telephonebool NOT NULL <br /> ); <br /><br /> Two smaller tables with a join table may look like: <br /><br /> CREATE
TABLEgrounds <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
INTOfeatures ( feature ) VALUES ( ' wheelchair' ); <br /> INSERT INTO features ( feature ) VALUES ( ' water' ); <br />
INSERTINTO 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
UPDATECASCADE 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
secondis more flexible as new features can be added but I have very limited time and <br /> I think the one larger
tabledesign 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> 

pgsql-sql by date:

Previous
From: Rudi Starcevic
Date:
Subject: Database Design
Next
From: "Nicolas JOUANIN"
Date:
Subject: Re: TR: Like and =