Re: [PHP] Tables vs. databases
Thanks to All who suggested solutions or pointed errors in my existing approach. Most seem to suggest having 1 database and 1-2 tables. So let me confirm: 1 table with million records are ok. But what of the size of the table? 10,000 * 10 MB = 100 GB! If the upload limit is to be notched up 100 times - typical of public mail servers, a table would expand to 10 TB. Someone suggested : The one-database-for all method increases risk that an SQL error will leak information from one client to another. But with 1 table and a million records, what would be the chances of this leak? My idea is, For every 100 users, make a new database. That is 100 tables, each of max. 10MB * 100 = 1GB. For the 101th user, make a new database. So for 1 users - 100 databases. 100 databases and 100 tables don't look bad to me. What say? Thanks Antonio On 6/11/06, Anthony Ettinger [EMAIL PROTECTED] wrote: On 6/9/06, Antonio Bassinger [EMAIL PROTECTED] wrote: Hi gang, Situation: I've a HTTP server. I intend to run a file upload service. There could be up to 1 subscribers. Each saving files up to 10 MB. I made a proof-of-concept service using PHP MySQL, where there is a single database, but many tables - a unique table for each subscriber. But I realize that I may land in trouble with such a huge database. Would it be better to have a separate database for each subscriber? Which approach is better, many tables in 1 database, or many databases with 1 or max 2 tables? Kindly suggest with pros and cons of each. you might want to consider storing the files outside of the database as well, and just a pointer to it's path in the table. with respect to table vs. databases per user, neither. -- Anthony Ettinger Signature: http://chovy.dyndns.org/hcard.html
Re: [PHP] Tables vs. databases
Antonio Bassinger wrote: Thanks to All who suggested solutions or pointed errors in my existing approach. Most seem to suggest having 1 database and 1-2 tables. So let me confirm: 1 table with million records are ok. But what of the size of the table? 10,000 * 10 MB = 100 GB! put your files on the filesystem store data about the files (including where each one is saved) in the DB. If the upload limit is to be notched up 100 times - typical of public mail servers, a table would expand to 10 TB. in which case your in the territory of clusters, distributed systems, really big iron, LOTS of raid, etc, etc - a bit beyond the scope of this list (granted there are a few people here with the skills and experience to tackle such architectures but generally they get paid big bucks to dish out that kind of solution :-) then again you never know. there is a girl called Michele (german?) who posts here now and again who seems to work quite a bit with very large databases and massive storage - maybe she reads this and has some ideas/tips? Someone suggested : The one-database-for all method increases risk that an SQL error will leak information from one client to another. very vague. make your SQL rock solid and never print out any errors returned by mysql (and obviously display_errors must be off in your production system) - just give the user some friendly generic error msgs if something goes wrong. But with 1 table and a million records, what would be the chances of this leak? I don't think the probability of a leak has much, if anything, to with the number of records in the table - it a down to the robustness/quality of the code written to interact with the DB and the user. My idea is, For every 100 users, make a new database. That is 100 tables, each of max. 10MB * 100 = 1GB. For the 101th user, make a new database. So for 1 users - 100 databases. 100 databases and 100 tables don't look bad to me. What say? I'd say it's an arbitrary way of splitting up the data that is heavily unnormalized - and also poses a maintance nightmare when updating the DB schema and or performing DB 'health checks' and/or repairs stick to 1 DB, 2+ tables until/unless it becomes clear that a single data-source is a performance or storage problem. Thanks Antonio On 6/11/06, Anthony Ettinger [EMAIL PROTECTED] wrote: On 6/9/06, Antonio Bassinger [EMAIL PROTECTED] wrote: Hi gang, Situation: I've a HTTP server. I intend to run a file upload service. There could be up to 1 subscribers. Each saving files up to 10 MB. I made a proof-of-concept service using PHP MySQL, where there is a single database, but many tables - a unique table for each subscriber. But I realize that I may land in trouble with such a huge database. Would it be better to have a separate database for each subscriber? Which approach is better, many tables in 1 database, or many databases with 1 or max 2 tables? Kindly suggest with pros and cons of each. you might want to consider storing the files outside of the database as well, and just a pointer to it's path in the table. with respect to table vs. databases per user, neither. -- Anthony Ettinger Signature: http://chovy.dyndns.org/hcard.html -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Tables vs. databases
Antonio Bassinger wrote: I've a HTTP server. I intend to run a file upload service. There could be up to 1 subscribers. Each saving files up to 10 MB. I made a proof-of-concept service using PHP MySQL, where there is a single database, but many tables - a unique table for each subscriber. But I realize that I may land in trouble with such a huge database. Would it be better to have a separate database for each subscriber? Which approach is better, many tables in 1 database, or many databases with 1 or max 2 tables? Kindly suggest with pros and cons of each. Take a look at bitweaver www.bitweaver.org Fully operational user management and file upload with management that allows it to be fully scalable. It will give you some practical examples at least ;) -- Lester Caine - G8HFL - L.S.Caine Electronic Services - http://home.lsces.co.uk Model Engineers Digital Workshop - http://home.lsces.co.uk/ModelEngineersDigitalWorkshop/ Treasurer - Firebird Foundation Inc. - http://www.firebirdsql.org/index.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Tables vs. databases
At 10:40 PM +0530 6/9/06, Antonio Bassinger wrote: Hi gang, Situation: I've a HTTP server. I intend to run a file upload service. There could be up to 1 subscribers. Each saving files up to 10 MB. I made a proof-of-concept service using PHP MySQL, where there is a single database, but many tables - a unique table for each subscriber. But I realize that I may land in trouble with such a huge database. Would it be better to have a separate database for each subscriber? Which approach is better, many tables in 1 database, or many databases with 1 or max 2 tables? Kindly suggest with pros and cons of each. Thanks Regards, Bassinger You might also ask the mysql group. http://lists.nyphp.org/mailman/listinfo/mysql hth's tedd -- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Tables vs. databases
On 6/9/06, Antonio Bassinger [EMAIL PROTECTED] wrote: Hi gang, Situation: I've a HTTP server. I intend to run a file upload service. There could be up to 1 subscribers. Each saving files up to 10 MB. I made a proof-of-concept service using PHP MySQL, where there is a single database, but many tables - a unique table for each subscriber. But I realize that I may land in trouble with such a huge database. Would it be better to have a separate database for each subscriber? Which approach is better, many tables in 1 database, or many databases with 1 or max 2 tables? Kindly suggest with pros and cons of each. you might want to consider storing the files outside of the database as well, and just a pointer to it's path in the table. with respect to table vs. databases per user, neither. -- Anthony Ettinger Signature: http://chovy.dyndns.org/hcard.html -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Tables vs. databases
On 09/06/06, Antonio Bassinger [EMAIL PROTECTED] wrote: Hi gang, Situation: I've a HTTP server. I intend to run a file upload service. There could be up to 1 subscribers. Each saving files up to 10 MB. I made a proof-of-concept service using PHP MySQL, where there is a single database, but many tables - a unique table for each subscriber. But I realize that I may land in trouble with such a huge database. Would it be better to have a separate database for each subscriber? Which approach is better, many tables in 1 database, or many databases with 1 or max 2 tables? First off, why does each subscriber have their own table rather than being one record in a single subscribers table? I think you may need to think about the database design - the relational structure. -- http://www.web-buddha.co.uk dynamic web programming from Reigate, Surrey UK (php, mysql, xhtml, css) look out for project karma, our new venture, coming soon!
Re: [PHP] Tables vs. databases
Antonio Bassinger wrote: Hi gang, Situation: I've a HTTP server. I intend to run a file upload service. There could be up to 1 subscribers. Each saving files up to 10 MB. I made a proof-of-concept service using PHP MySQL, where there is a single database, but many tables - a unique table for each subscriber. But I why do you have a unique table for each user? realize that I may land in trouble with such a huge database. Would it be there is an upper limit on the number of tables dependent on your system; you don't want the kind of trouble that hitting that limit will bring. better to have a separate database for each subscriber? NO - I really don't think that is an option either - 1+ databases doesn't sound any better than 1+ tables. 1 database, 2 tables: USERS: user_id user_name ...etc FILES: file_id user_id file_location ...etc Which approach is better, many tables in 1 database, or many databases with 1 or max 2 tables? Kindly suggest with pros and cons of each. I can't think of a 'pro' for either of your approaches. Thanks Regards, Bassinger -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Tables vs. databases
On Fri, June 9, 2006 12:10 pm, Antonio Bassinger wrote: I've a HTTP server. I intend to run a file upload service. There could be up to 1 subscribers. Each saving files up to 10 MB. I made a proof-of-concept service using PHP MySQL, where there is a single database, but many tables - a unique table for each subscriber. But I realize that I may land in trouble with such a huge database. Would it be better to have a separate database for each subscriber? Which approach is better, many tables in 1 database, or many databases with 1 or max 2 tables? Kindly suggest with pros and cons of each. The one-database-for all method increases risk that an SQL error will leak information from one client to another. The overhead for multiple databases is tiny, except maybe in god-like admin pages that would access 2 databases at once. You do have to be methodical about db changes and applying them to ALL the dbs at once, or you'll have a version-control nightmare quickly... -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Tables vs. databases
On 6/10/06, Antonio Bassinger [EMAIL PROTECTED] wrote: Hi gang, Situation: I've a HTTP server. I intend to run a file upload service. There could be up to 1 subscribers. Each saving files up to 10 MB. I made a proof-of-concept service using PHP MySQL, where there is a single database, but many tables - a unique table for each subscriber. But I realize that I may land in trouble with such a huge database. Would it be better to have a separate database for each subscriber? On top of the replies you already have you should read these pages: http://arjen-lentz.livejournal.com/66547.html http://bobfield.blogspot.com/2006/03/million-tables.html Though you're not talking about those sort of numbers, you need to be aware of those limitations (there were more posts on planetmysql.org about this but I can't find the original page). -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
