RE: [PHP-DB] FW: db design - which is better

Yeah, I think it's just the devil sending me strange thoughts. :-) It does sound dumb to have normalized tables AND to duplicate some of the data in another table. thx, just tossing off some ideas. -- gil > -----Original Message----- > From: Ignatius Reilly [mailto:ignatius.reilly@free.fr] > Sent: Saturday, February 07, 2004 6:31 PM > To: mayo; php-db > Subject: Re: [PHP-DB] FW: db design - which is better > > > Hmmm... > > What of maintenance? > One day your pointed-headed boss asks you to add a "civility" field > (Mr|Mrs|Colonel|...), a birthdate, etc. > How do you do that with a 1-table design? You will have to create 1-NF > temporary tables in the process... > > How do you search for an author? You will have to create a > needlessly large > and inefficient index on (LastName + FirstName), instead of a > nice integer > index. > > And when querying the articleID, I do not think that there should be a > significant performance penalty, if your indexes are properly designed. > > cheers > > Ignatius > _________________________ > ----- Original Message ----- > From: "mayo" <mayo@nycinteractive.com> > To: "php-db" <php-db@lists.php.net> > Sent: Sunday, February 08, 2004 00:10 > Subject: RE: [PHP-DB] FW: db design - which is better > > > > Let's say you have a page that is hit fairly frequently and > spits out 100s > > of results. > > > > On it you have articleTitle, authorFName, authorLName. > > > > I ran a few tests and found that if all the above fields are > in one table > it > > gets processed quicker than if there's a join. (authorID -> > authorFName, > > authorLName) > > > > So, I'm beginning to think that processing time would be quicker if I > insert > > authorFName and authorLName into 2 tables. One the author table and the > > other the *VERY* frequently hit article table. > > > > That way when someone wants a list of articles, author FName, > author LName > > all the data comes from one table. > > > > This would be probably very stupid in a financial or medical database > which > > has tens of millions of records and 100s if not 1000s of > tables but SEEMS > > (I'm very open to being corrected) to work better on a fairly small web > > accessed database. > > > > yours, > > > > Gil > > > > > > > > > > > > > > > > > > > > > -----Original Message----- > > > From: Ignatius Reilly [mailto:ignatius.reilly@free.fr] > > > Sent: Saturday, February 07, 2004 6:01 PM > > > To: mayo; php-db > > > Subject: Re: [PHP-DB] FW: db design - which is better > > > > > > > > > There are perhaps such times, but I have yet to meet one. > > > > > > with 1NF: > > > - your table space will be smaller > > > - your indexes will work better > > > - your SQL will be easier to write > > > - ease of maintenance > > > > > > Just my 2 Belgian francs > > > > > > Ignatius > > > _________________________ > > > ----- Original Message ----- > > > From: "mayo" <mayo@nycinteractive.com> > > > To: "php-db" <php-db@lists.php.net> > > > Sent: Saturday, February 07, 2004 23:45 > > > Subject: RE: [PHP-DB] FW: db design - which is better > > > > > > > > > > > > > > Thanks, > > > > > > > > There just seems to be times when non-normalized table > structures are > > > easier > > > > to work with. A few hundred to a few thousand records, maybe 20 > fields, > > > > rarely adding another field. > > > > > > > > I wonder sometimes whether it make sense to put everything in one > table > > > > instead of using joins. > > > > > > > > yours, putting-his-foot-in-his-mouth, > > > > > > > > Gil > > > > > > > > > > > > > -----Original Message----- > > > > > From: Ignatius Reilly [mailto:ignatius.reilly@free.fr] > > > > > Sent: Saturday, February 07, 2004 5:33 PM > > > > > To: mayo; php-db > > > > > Subject: Re: [PHP-DB] FW: db design - which is better > > > > > > > > > > > > > > > Use the second design. SQL 101 / 1st normal form. > > > > > _________________________ > > > > > ----- Original Message ----- > > > > > From: "mayo" <mayo@nycinteractive.com> > > > > > To: "php-db" <php-db@lists.php.net> > > > > > Sent: Saturday, February 07, 2004 23:19 > > > > > Subject: [PHP-DB] FW: db design - which is better > > > > > > > > > > > > > > > > > > > > > > > > > > > > -----Original Message----- > > > > > > From: Gilbert Midonnet [mailto:glm@glmdesigns.com] > > > > > > Sent: Saturday, February 07, 2004 5:09 PM > > > > > > To: php-db > > > > > > Subject: db design - which is better > > > > > > > > > > > > > > > > > > I have a client who has hundreds of articles. Each > article can > > > > > be seen by > > > > > > one or more permission groups. > > > > > > > > > > > > I have questions regarding setting up the an > > > article_display table. > > > > > > > > > > > > The easiest table to read and to conceptualize would list the > > > > > articles and > > > > > > use a boolean for each of the permission groups (let's > > > call the PGs > > > for > > > > > this > > > > > > example). > > > > > > > > > > > > (articleNameID refers back to the article table) > > > > > > > > > > > > articleNameID---PG1---PG2---PG3---PG4---PG5 > > > > > > 1001-------------1-----0-----0-----0-----0 > > > > > > 1002-------------1-----1-----0-----0-----0 > > > > > > 1003-------------0-----0-----1-----0-----0 > > > > > > > > > > > > > > > > > > Or should the table set up be: > > > > > > > > > > > > articleNameID--PG > > > > > > 1001------------1 > > > > > > 1002------------1 > > > > > > 1002------------2 > > > > > > 1003------------3 > > > > > > > > > > > > etc... > > > > > > > > > > > > gil > > > > > > > > > > > > -- > > > > > > PHP Database Mailing List (http://www.php.net/) > > > > > > To unsubscribe, visit: > > > > > > http://www.php.net/unsub.php > > > > > > > > > > > > > > > > > > > > > > -- > > > > > PHP Database Mailing List (http://www.php.net/) > > > > > To unsubscribe, visit: http://www.php.net/unsub.php\ > > > > > '<�Y��^��D^ > > > > > > > > > > > > > -- > > > > PHP Database Mailing List (http://www.php.net/) > > > > To unsubscribe, visit: > > > > http://www.php.net/unsub.php > > > > > > > > > > > > > > -- > > > PHP Database Mailing List (http://www.php.net/) > > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > >