Обсуждение: Database design - AGAIN
<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>
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
-----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-----
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
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>
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