Re: 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: ��ci8 > > > > > &�x���q�>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 > > > > > > > > > > -- > > 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 > >