Just now I am working on a project to develop a web application for multiple users.
The plan is that every user saves his data about a cutomer survey in his own database table (all customer-tables are of the same structure).
I thought doining the scripts with cgi/perl and the databases with mySQL.
My Question: Is it principially ok doing it this way?
Of course security questions about logging in and how to direct every user to his own table is what i've not thought about jet, it's more a principle question if it's tecnically feasible this way.
if they're the same structure, they're the same tables, no? just tie a particular row to a customer (maybe foreign key relations) and only allow customer a to see customer a's data.
that's what i thought i'll do with the customers (users) personal data (e-mail, name, poasswd...)
but i need a second database in which i store the following:
Every user should have it's own table within the dadabase. The user tables are of the same structure, they have the same columns, respectively. The User should then fill in their data from a quality survey (all the users do the same survey). Sorry if this wasn't posted clear enough by me.
yes, and that's exactly what i'm saying should be one table, not one per customer. its typically a poor design decision to say that you need to create a new table for each customer that gets added. let's say you start w/ 3 customers. next week you pick up 3 more, and each week you double.
in a few weeks you need to create 20 then 40 then 80 tables...
it's a management nightmare.
also, since you're hosting the system, you're going to want to collect statistics and tell customers how they rank against one another at some level. it's much more difficult to automate this over numerous tables than it would be if it's all in one table.
what is your concern, or really design choice, that makes multiple tables the most viable option?
[QUOTE=chazzy]yes, and that's exactly what i'm saying should be one table, not one per customer. its typically a poor design decision to say that you need to create a new table for each customer that gets added. let's say you start w/ 3 customers. next week you pick up 3 more, and each week you double.
Thank's chazzy, now i think i got your point.
Sorry, but I'm not very used to database design.
Although, this application is for a limited number of users (max. 150), it's the best way like you wrote, to store the data into one table (with a row that identifies the user)... then it should be no problem to do statistic with the data (user specific and general stat. including all users)
? but for the user data itself (passwrd, name...) i think i'll have to create an extra table?
One more question: is their a major difference using cgi or php for the communication between user and database?
1 table = users
1 table = the other data, including a reference back to the user.
doesn't matter how many users are on the system, you should never make a system that says "this will support 150 users." what if it booms? all of a sudden you have requests for 1000 accounts? you'd love that i'm sure, but your app wouldn't be able to handle it.
by CGI, i'm assuming you mean Perl based. either would work. PHP's more popular, but Perl's more complete. If you do use PHP, follow an object oriented approach. Perl is less resource intensive, but is commonly more difficult to develop in. also depends on what you know already.