Thread: Database design - AGAIN

Database design - AGAIN

From
Rudi Starcevic
Date:
<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> 

Re: Database design - AGAIN

From
Josh Berkus
Date:
Rudi,

> 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.

Sounds like you already have a clear grasp of the situation.

Here's one more question to ask yourself:  how long will this app be around?  
If the answer is "no more than 3 months, probably less", do it the most 
expedient way.  If the answer is "6 months to indefinitely", do it the 
normalized way -- eventually, either you or your successor will need the 
flexibility as the users demand new features.  If you are an employee, this 
is a no-brainer ... it is 90% likely that even if this is a temporary measure 
you will still be maintaining it 2 years from now, and will thank yourself if 
you built it "right" in the first place.

Unless, of course, you are a consultant and your client is paying you $500 
flat fee, in which case do it whatever way is fastest, but make sure your 
client knows that they are not getting an expandable product.

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

See what I said about expediency above.   My web guru, who is an expert in 
both PHP and Cold Fusion, would be delighted to never use Cold Fusion again. 
So that's one expert opinion.  And don't forget the license fees.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco


Re: Database design - AGAIN

From
Jonathan Gardner
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Monday 23 June 2003 22:19, Rudi Starcevic wrote:
> I"m not sure whether to have on larger table or 2 smaller one with a
> join table.

Theory says you are better off with the join tables. Practical reality agrees
with this. It takes a bit more work to write your select statements, but the
data is better for at least these two reasons:1) You won't have a lot of empty columns.2) You will be able to add,
rename,and remove features pretty easily. 
There are more reasons, but they are not immediately obvious to the untrained
DBA. (Words like "data integrity" and "data consistency" start to mean things
when you become trained.)

With that said, it sounds like you are really intermediate with database
design. If you were a beginner, and if you barely understood what a table
was, (and if you were the guy who would be using it all the time,) I would
probably suggest you use the former method. It will work pretty well until
you want to add more features or until you start worrying about your database
size. At that point, I would explain how and why to use joins, and how to
convert your data over to the new design.

- --
Jonathan Gardner <jgardner@jonathangardner.net>
(was jgardn@alumni.washington.edu)
Live Free, Use Linux!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE++HBcWgwF3QvpWNwRAgjPAJ48syjGQahHvU4zi38WVheFbVFC5ACfQw5S
0qO67ZB2ToO4zFJKoh5GtrU=
=PnFr
-----END PGP SIGNATURE-----


Re: Database design - AGAIN

From
"Rudi Starcevic"
Date:
Josh, Jonathan ..

Thanks for your replies.
You mail was exactly what I was hoping for - your best practise opinion.

I do have a grasp of the theory involved with SQL but your input on
those 'other' constraints like time, language, longevity etc was just
what I was interested in.

I'll be using smaller tables with appropriate join tables and PHP.
Just for interests sake I'll be using Dia to plan the schema. 
I prefer to use the UML drawing tools instead of the ER ones.

Thank you.
Best regards
Rudi.




> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> On Monday 23 June 2003 22:19, Rudi Starcevic wrote:
> > I"m not sure whether to have on larger table or 2 smaller one with a
> > join table.
> 
> Theory says you are better off with the join tables. Practical 
> reality agrees with this. It takes a bit more work to write your 
> select statements, but the data is better for at least these two reasons:
>     1) You won't have a lot of empty columns.
>     2) You will be able to add, rename, and remove features pretty easily.
> There are more reasons, but they are not immediately obvious to the 
> untrained DBA. (Words like "data integrity" and "data consistency" 
> start to mean things when you become trained.)
> 
> With that said, it sounds like you are really intermediate with 
> database design. If you were a beginner, and if you barely 
> understood what a table was, (and if you were the guy who would be 
> using it all the time,) I would probably suggest you use the former 
> method. It will work pretty well until you want to add more features 
> or until you start worrying about your database size. At that point, 
> I would explain how and why to use joins, and how to convert your 
> data over to the new design.
> 
> - -- 
> Jonathan Gardner <jgardner@jonathangardner.net>
> (was jgardn@alumni.washington.edu)
> Live Free, Use Linux!
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.2.1 (GNU/Linux)
> 
> iD8DBQE++HBcWgwF3QvpWNwRAgjPAJ48syjGQahHvU4zi38WVheFbVFC5ACfQw5S
> 0qO67ZB2ToO4zFJKoh5GtrU=
> =PnFr
> -----END PGP SIGNATURE-----
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>                http://archives.postgresql.org





Re: Database design - AGAIN

From
Michael A Nachbaur
Date:
On Tuesday 24 June 2003 03:15 pm, Rudi Starcevic wrote:
<snip>
> I'll be using smaller tables with appropriate join tables and PHP.
> Just for interests sake I'll be using Dia to plan the schema.
> I prefer to use the UML drawing tools instead of the ER ones.

Off topic, but if you're using KDE you might want to look into Umbrello.  It's 
a UML modelling app for KDE, and is quite good, even has the beginnings of 
code generation (for SQL, Perl, Java, etc).

-- 
Michael A Nachbaur <mike@nachbaur.com>



Re: Database design - AGAIN

From
Josh Berkus
Date:
Rudi,

> I'll be using smaller tables with appropriate join tables and PHP.
> Just for interests sake I'll be using Dia to plan the schema.
> I prefer to use the UML drawing tools instead of the ER ones.

<grin>  I still use a simple flowcharting program to plan my databases.  Never 
saw the value in UML, mainly because of the confusing terminology.

But if it works for you, go for it.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco