Re: [PHP-DB] FW: db design - which is better
Use the second design. SQL 101 / 1st normal form. _ - Original Message - From: mayo [EMAIL PROTECTED] To: php-db [EMAIL PROTECTED] Sent: Saturday, February 07, 2004 23:19 Subject: [PHP-DB] FW: db design - which is better -Original Message- From: Gilbert Midonnet [mailto:[EMAIL PROTECTED] 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 10011 10021 10022 10033 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
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:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] To: php-db [EMAIL PROTECTED] Sent: Saturday, February 07, 2004 23:19 Subject: [PHP-DB] FW: db design - which is better -Original Message- From: Gilbert Midonnet [mailto:[EMAIL PROTECTED] 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 10011 10021 10022 10033 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
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:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] To: php-db [EMAIL PROTECTED] 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:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] To: php-db [EMAIL PROTECTED] Sent: Saturday, February 07, 2004 23:19 Subject: [PHP-DB] FW: db design - which is better -Original Message- From: Gilbert Midonnet [mailto:[EMAIL PROTECTED] 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 10011 10021 10022 10033 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 -- 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
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 [EMAIL PROTECTED] To: php-db [EMAIL PROTECTED] 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:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] To: php-db [EMAIL PROTECTED] 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:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] To: php-db [EMAIL PROTECTED] Sent: Saturday, February 07, 2004 23:19 Subject: [PHP-DB] FW: db design - which is better -Original Message- From: Gilbert Midonnet [mailto:[EMAIL PROTECTED] 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 10011 10021 10022 10033 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 -- 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
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:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] To: php-db [EMAIL PROTECTED] 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:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] To: php-db [EMAIL PROTECTED] 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:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] To: php-db [EMAIL PROTECTED] Sent: Saturday, February 07, 2004 23:19 Subject: [PHP-DB] FW: db design - which is better -Original Message- From: Gilbert Midonnet [mailto:[EMAIL PROTECTED] 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
Re: [PHP-DB] FW: db design - which is better
There just seems to be times when non-normalized table structures are easier to work with. There are times when de-normalizing is acceptable. One of the more common uses of denormalized data is in data warehousing. Such as pre-summing sales, and storing pre-calculated aggregate information to relieve the db of doing heavy or complex calculations when summary data is going to be queried on a frequent basis. Often php-based message boards will use columns of pre-sumed data as part of a normal table (for example, a count of total user posts stored in the user table, that is incremented/decremented when a user adds/deletes a post, or the total number posts in a given thread stored in the topic table). This helps to allieviate the need for a count() whenever a message thread is built and you want to show the number of posts each user in the thread has, etc. So there are some acceptable situations :) Cheers, Keith. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
