pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDatabase design - AGAIN
6+ messages / 4 participants
[nested] [flat]
* Database design - AGAIN
@ 2003-06-24 05:19 Rudi Starcevic <rudi@oasis.net.au>
2003-06-24 15:25 ` Re: Database design - AGAIN Josh Berkus <josh@agliodbs.com>
2003-06-24 15:38 ` Re: Database design - AGAIN Jonathan Gardner <jgardner@jonathangardner.net>
0 siblings, 2 replies; 6+ messages in thread
From: Rudi Starcevic @ 2003-06-24 05:19 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Database design - AGAIN
2003-06-24 05:19 Database design - AGAIN Rudi Starcevic <rudi@oasis.net.au>
@ 2003-06-24 15:25 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 6+ messages in thread
From: Josh Berkus @ 2003-06-24 15:25 UTC (permalink / raw)
To: Rudi Starcevic <rudi@oasis.net.au>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Database design - AGAIN
2003-06-24 05:19 Database design - AGAIN Rudi Starcevic <rudi@oasis.net.au>
@ 2003-06-24 15:38 ` Jonathan Gardner <jgardner@jonathangardner.net>
2003-06-24 22:15 ` Re: Database design - AGAIN Rudi Starcevic <rudi@oasis.net.au>
1 sibling, 1 reply; 6+ messages in thread
From: Jonathan Gardner @ 2003-06-24 15:38 UTC (permalink / raw)
To: Rudi Starcevic <rudi@oasis.net.au>; pgsql-sql
-----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-----
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Database design - AGAIN
2003-06-24 05:19 Database design - AGAIN Rudi Starcevic <rudi@oasis.net.au>
2003-06-24 15:38 ` Re: Database design - AGAIN Jonathan Gardner <jgardner@jonathangardner.net>
@ 2003-06-24 22:15 ` Rudi Starcevic <rudi@oasis.net.au>
2003-06-24 23:02 ` Re: Database design - AGAIN Michael A Nachbaur <mike@nachbaur.com>
2003-06-25 19:21 ` Re: Database design - AGAIN Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 6+ messages in thread
From: Rudi Starcevic @ 2003-06-24 22:15 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Database design - AGAIN
2003-06-24 05:19 Database design - AGAIN Rudi Starcevic <rudi@oasis.net.au>
2003-06-24 15:38 ` Re: Database design - AGAIN Jonathan Gardner <jgardner@jonathangardner.net>
2003-06-24 22:15 ` Re: Database design - AGAIN Rudi Starcevic <rudi@oasis.net.au>
@ 2003-06-24 23:02 ` Michael A Nachbaur <mike@nachbaur.com>
1 sibling, 0 replies; 6+ messages in thread
From: Michael A Nachbaur @ 2003-06-24 23:02 UTC (permalink / raw)
To: Rudi Starcevic <rudi@oasis.net.au>; pgsql-sql
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>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Database design - AGAIN
2003-06-24 05:19 Database design - AGAIN Rudi Starcevic <rudi@oasis.net.au>
2003-06-24 15:38 ` Re: Database design - AGAIN Jonathan Gardner <jgardner@jonathangardner.net>
2003-06-24 22:15 ` Re: Database design - AGAIN Rudi Starcevic <rudi@oasis.net.au>
@ 2003-06-25 19:21 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 6+ messages in thread
From: Josh Berkus @ 2003-06-25 19:21 UTC (permalink / raw)
To: Rudi Starcevic <rudi@oasis.net.au>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-06-25 19:21 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-24 05:19 Database design - AGAIN Rudi Starcevic <rudi@oasis.net.au>
2003-06-24 15:25 ` Josh Berkus <josh@agliodbs.com>
2003-06-24 15:38 ` Jonathan Gardner <jgardner@jonathangardner.net>
2003-06-24 22:15 ` Rudi Starcevic <rudi@oasis.net.au>
2003-06-24 23:02 ` Michael A Nachbaur <mike@nachbaur.com>
2003-06-25 19:21 ` Josh Berkus <josh@agliodbs.com>
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