Hi Everyone,

Just had a quick question about a database I'm working on.

I am planning on having the database open to customers of mine to
store their mailing addresses on-line, and be able to manage the
records.

Is it safe, to have 1 database with lots of tables? Or am I safer
setting up separate databases for everyone?

I should mention, no one will be accessing the database directly,
it'll be through a web interface and php to display it.

Any info would be greatly appreciated!


--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
japruim@raoset.com

Think about how your going to make backups.

1) Would you backup one database with all the mailing lists together ?
2) Would you keep the backups of each user separate ?
3) Could users ask you to restore mailing lists from the past ?

You could make one mysqldump for everybody from one database if
no one ever asks for restoration of past mailing lists.

You could create a database for each user.
Then, backup (mysqldump) each database for each user.
Should they request a restore, it's becomes an easy thing.

How you answer the three questions I posed should you decide.

----- Original Message -----
From: "Jason Pruim" <japruim@raoset.com>
To: "MySQL List" <mysql@lists.mysql.com>
Sent: Thursday, August 23, 2007 10:58:52 AM (GMT-0500) America/New_York
Subject: Database architecture and security

Hi Everyone,

Just had a quick question about a database I'm working on.

I am planning on having the database open to customers of mine to
store their mailing addresses on-line, and be able to manage the
records.

Is it safe, to have 1 database with lots of tables? Or am I safer
setting up separate databases for everyone?

I should mention, no one will be accessing the database directly,
it'll be through a web interface and php to display it.

Any info would be greatly appreciated!


--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
japruim@raoset.com




--
Rolando A. Edwards
MySQL DBA

SWMX, Inc.
1 Bridge Street
Irvington, NY 10533
(914) 406-8406 (Main)
(201) 660-3221 (Mobile)

I'd never have a separate database for everyone or even a separate table
for everyone. Here's a rough idea of how I'd do it

mysql> CREATE TABLE customer (
-> `custid` INT NOT NULL AUTO_INCREMENT,
-> `lastname` VARCHAR(25) not null,
-> `firstname` VARCHAR(25) NOT NULL,
-> PRIMARY KEY(custid)
-> );
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE address (
-> `addressid` INT NOT NULL AUTO_INCREMENT,
-> `custid` INT NOT NULL,
-> `address` VARCHAR(100) NOT NULL,
-> `city` VARCHAR(50),
-> `state` CHAR(2) NOT NULL,
-> `zip` MEDIUMINT(5) NOT NULL,
-> PRIMARY KEY(addressid)
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO customer (lastname, firstname) VALUES ('Bolton',
'Mike'), ('Vader', 'Darth');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM customer;
+--------+----------+-----------+
| custid | lastname | firstname |
+--------+----------+-----------+
| 1 | Bolton | Mike |
| 2 | Vader | Darth |
+--------+----------+-----------+
2 rows in set (0.00 sec)

mysql> INSERT INTO address (custid, address, city, state, zip) VALUES
-> (1, '123 house drive.', 'Davie', 'FL', 33314),
-> (1, '54325 awesome way', 'Sunrise', 'FL', 33521),
-> (2, 'The Death Star', 'SPACE', 'NA', 66666);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM address;
+-----------+--------+-------------------+---------+-------+-------+
| addressid | custid | address | city | state | zip |
+-----------+--------+-------------------+---------+-------+-------+
| 1 | 1 | 123 house drive. | Davie | FL | 33314 |
| 2 | 1 | 54325 awesome way | Sunrise | FL | 33521 |
| 3 | 2 | The Death Star | SPACE | NA | 66666 |
+-----------+--------+-------------------+---------+-------+-------+
3 rows in set (0.00 sec)

mysql> SELECT lastname, firstname, address, city, state, zip FROM
customer JOIN address USING (custid);
+----------+-----------+-------------------+---------+-------+-------+
| lastname | firstname | address | city | state | zip |
+----------+-----------+-------------------+---------+-------+-------+
| Bolton | Mike | 123 house drive. | Davie | FL | 33314 |
| Bolton | Mike | 54325 awesome way | Sunrise | FL | 33521 |
| Vader | Darth | The Death Star | SPACE | NA | 66666 |
+----------+-----------+-------------------+---------+-------+-------+
3 rows in set (0.01 sec)

mysql> SELECT address, city, state, zip FROM customer JOIN address USING
(custid) WHERE (lastname, firstname) = ('Bolton', 'Mike');
+-------------------+---------+-------+-------+
| address | city | state | zip |
+-------------------+---------+-------+-------+
| 123 house drive. | Davie | FL | 33314 |
| 54325 awesome way | Sunrise | FL | 33521 |
+-------------------+---------+-------+-------+

Now each customer/person can have multiple addresses listed.

Jason Pruim wrote:
> Hi Everyone,
>
> Just had a quick question about a database I'm working on.
>
> I am planning on having the database open to customers of mine to
> store their mailing addresses on-line, and be able to manage the records.
>
> Is it safe, to have 1 database with lots of tables? Or am I safer
> setting up separate databases for everyone?
>
> I should mention, no one will be accessing the database directly,
> it'll be through a web interface and php to display it.
>
> Any info would be greatly appreciated!
>
>
> --
>
> Jason Pruim
> Raoset Inc.
> Technology Manager
> MQC Specialist
> 3251 132nd ave
> Holland, MI, 49424
> www.raoset.com
> japruim@raoset.com
>
>
>

On 8/23/07, Jason Pruim <japruim@raoset.com> wrote:
>
> I am planning on having the database open to customers of mine to
> store their mailing addresses on-line, and be able to manage the
> records.
>
> Is it safe, to have 1 database with lots of tables? Or am I safer
> setting up separate databases for everyone?
>
> I should mention, no one will be accessing the database directly,
> it'll be through a web interface and php to display it.


Assuming that the web server runs on the same box as the MySQL daemon ...
you want to firewall the server so that nobody can connect to the MySQL
daemon directly from outside the box. It is also a bad idea to allow the
users to have shell accounts on that box unless you have taken additional
security precautions (specifically, being sure the MySQL userid/password
you're using are secure from all but the web server UID/GID, and that no
other userid/passwords have access to the database you're using).

Once that is done, all access to the database is controlled by the PHP
scripts, and there is no security advantage to having multiple databases.

I'm assuming that users have to log in individually (jsmith, bjones, etc.)
and that the PHP scripts then carefully control what each user is allowed to
modify.

I'm also going to assume that you've handled all the obvious technology
issues, such as:

a)Database transactions/atomic actions.

b)Terminating TCP connections and ensuring that each PHP script runs to
completion, anyway, and that the database isn't left in an indeterminate
state due to this.

Dave.

On Aug 23, 2007, at 11:44 AM, Gary Josack wrote:

> I'd never have a separate database for everyone or even a separate
> table for everyone. Here's a rough idea of how I'd do it
>
> mysql> CREATE TABLE customer (
> -> `custid` INT NOT NULL AUTO_INCREMENT,
> -> `lastname` VARCHAR(25) not null,
> -> `firstname` VARCHAR(25) NOT NULL,
> -> PRIMARY KEY(custid)
> -> );
> Query OK, 0 rows affected (0.03 sec)
>
> mysql> CREATE TABLE address (
> -> `addressid` INT NOT NULL AUTO_INCREMENT,
> -> `custid` INT NOT NULL,
> -> `address` VARCHAR(100) NOT NULL,
> -> `city` VARCHAR(50),
> -> `state` CHAR(2) NOT NULL,
> -> `zip` MEDIUMINT(5) NOT NULL,
> -> PRIMARY KEY(addressid)
> -> );
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> INSERT INTO customer (lastname, firstname) VALUES ('Bolton',
> 'Mike'), ('Vader', 'Darth');
> Query OK, 2 rows affected (0.00 sec)
> Records: 2 Duplicates: 0 Warnings: 0
>
> mysql> SELECT * FROM customer;
> +--------+----------+-----------+
> | custid | lastname | firstname |
> +--------+----------+-----------+
> | 1 | Bolton | Mike |
> | 2 | Vader | Darth |
> +--------+----------+-----------+
> 2 rows in set (0.00 sec)
>
> mysql> INSERT INTO address (custid, address, city, state, zip) VALUES
> -> (1, '123 house drive.', 'Davie', 'FL', 33314),
> -> (1, '54325 awesome way', 'Sunrise', 'FL', 33521),
> -> (2, 'The Death Star', 'SPACE', 'NA', 66666);
> Query OK, 3 rows affected (0.00 sec)
> Records: 3 Duplicates: 0 Warnings: 0
>
> mysql> SELECT * FROM address;
> +-----------+--------+-------------------+---------+-------+-------+
> | addressid | custid | address | city | state | zip |
> +-----------+--------+-------------------+---------+-------+-------+
> | 1 | 1 | 123 house drive. | Davie | FL | 33314 |
> | 2 | 1 | 54325 awesome way | Sunrise | FL | 33521 |
> | 3 | 2 | The Death Star | SPACE | NA | 66666 |
> +-----------+--------+-------------------+---------+-------+-------+
> 3 rows in set (0.00 sec)
>
> mysql> SELECT lastname, firstname, address, city, state, zip FROM
> customer JOIN address USING (custid);
> +----------+-----------+-------------------+---------+-------+-------+
> | lastname | firstname | address | city | state | zip |
> +----------+-----------+-------------------+---------+-------+-------+
> | Bolton | Mike | 123 house drive. | Davie | FL | 33314 |
> | Bolton | Mike | 54325 awesome way | Sunrise | FL | 33521 |
> | Vader | Darth | The Death Star | SPACE | NA | 66666 |
> +----------+-----------+-------------------+---------+-------+-------+
> 3 rows in set (0.01 sec)
>
> mysql> SELECT address, city, state, zip FROM customer JOIN address
> USING (custid) WHERE (lastname, firstname) = ('Bolton', 'Mike');
> +-------------------+---------+-------+-------+
> | address | city | state | zip |
> +-------------------+---------+-------+-------+
> | 123 house drive. | Davie | FL | 33314 |
> | 54325 awesome way | Sunrise | FL | 33521 |
> +-------------------+---------+-------+-------+
>
> Now each customer/person can have multiple addresses listed.

I really like the idea of being able to have multiple addresses, some
of our customers right now have lots of seasonal addresses... But
that's a little bit out of my comfort zone right now... I'll add it
to the feature list though and keep your e-mail to reference

Thanks!

--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
japruim@raoset.com

On Aug 23, 2007, at 11:28 AM, Rolando Edwards wrote:

> Think about how your going to make backups.
>
> 1) Would you backup one database with all the mailing lists together ?

If I went the route of 1 database, Many tables, I would just backup
the entire database and all the tables in one shot. Unless I"m
misunderstanding how MySQL handles the backups, I would think that it
would preserve the individual tables?


> 2) Would you keep the backups of each user separate ?

I probably should, but hadn't thought that far ahead yet. Still
working on getting the PHP Scripts and everything else set.

> 3) Could users ask you to restore mailing lists from the past ?

The only reason I could see them asking for that is if they went
through and deleted the entire database, which has to be done
manually, meaning you have to hit "delete" on each record to delete
it. I would hope they would realize what they were doing before going
through the entire database...

>
> You could make one mysqldump for everybody from one database if
> no one ever asks for restoration of past mailing lists.
>
> You could create a database for each user.
> Then, backup (mysqldump) each database for each user.
> Should they request a restore, it's becomes an easy thing.
>
> How you answer the three questions I posed should you decide.

I am starting to lean towards the multiple databases with 1 table
even though it makes more files on my server, I think it gives me the
most flexibility/security. That way I can also edit 1 database and
not screw it up for the entire user base, just the one user



>
> ----- Original Message -----
> From: "Jason Pruim" <japruim@raoset.com>
> To: "MySQL List" <mysql@lists.mysql.com>
> Sent: Thursday, August 23, 2007 10:58:52 AM (GMT-0500) America/
> New_York
> Subject: Database architecture and security
>
> Hi Everyone,
>
> Just had a quick question about a database I'm working on.
>
> I am planning on having the database open to customers of mine to
> store their mailing addresses on-line, and be able to manage the
> records.
>
> Is it safe, to have 1 database with lots of tables? Or am I safer
> setting up separate databases for everyone?
>
> I should mention, no one will be accessing the database directly,
> it'll be through a web interface and php to display it.
>
> Any info would be greatly appreciated!
>
>
> --
>
> Jason Pruim
> Raoset Inc.
> Technology Manager
> MQC Specialist
> 3251 132nd ave
> Holland, MI, 49424
> www.raoset.com
> japruim@raoset.com
>
>
>
>
> --
> Rolando A. Edwards
> MySQL DBA
>
> SWMX, Inc.
> 1 Bridge Street
> Irvington, NY 10533
> (914) 406-8406 (Main)
> (201) 660-3221 (Mobile)
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?
> unsub=japruim@raoset.com
>
>

--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
japruim@raoset.com

On Aug 23, 2007, at 11:50 AM, David T. Ashley wrote:

> On 8/23/07, Jason Pruim <japruim@raoset.com> wrote:
>>
>> I am planning on having the database open to customers of mine to
>> store their mailing addresses on-line, and be able to manage the
>> records.
>>
>> Is it safe, to have 1 database with lots of tables? Or am I safer
>> setting up separate databases for everyone?
>>
>> I should mention, no one will be accessing the database directly,
>> it'll be through a web interface and php to display it.
>
>
> Assuming that the web server runs on the same box as the MySQL
> daemon ...
> you want to firewall the server so that nobody can connect to the
> MySQL
> daemon directly from outside the box. It is also a bad idea to
> allow the
> users to have shell accounts on that box unless you have taken
> additional
> security precautions (specifically, being sure the MySQL userid/
> password
> you're using are secure from all but the web server UID/GID, and
> that no
> other userid/passwords have access to the database you're using).
>
> Once that is done, all access to the database is controlled by the PHP
> scripts, and there is no security advantage to having multiple
> databases.
>
> I'm assuming that users have to log in individually (jsmith,
> bjones, etc.)
> and that the PHP scripts then carefully control what each user is
> allowed to
> modify.
>
> I'm also going to assume that you've handled all the obvious
> technology
> issues, such as:
>
> a)Database transactions/atomic actions.
>
> b)Terminating TCP connections and ensuring that each PHP script
> runs to
> completion, anyway, and that the database isn't left in an
> indeterminate
> state due to this.
>
> Dave.

The server is currently firewalled to block all but the necessary
ports from outside the local network.

No user, other then myself, and a few admins on the server will have
shell access...

The MySQL userid/password will be changed once I go live with it, or
get into the final testing.

What do you mean by "b"? If all the connections come from the local
box how could I configure that to make sure it's all set up so it
won't leave the database all messed up?



--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
japruim@raoset.com

On 8/23/07, Jason Pruim <japruim@raoset.com> wrote:
>
>
> > b)Terminating TCP connections and ensuring that each PHP script
> > runs to
> > completion, anyway, and that the database isn't left in an
> > indeterminate
> > state due to this.
> >
> > Dave.
>
> What do you mean by "b"? If all the connections come from the local
> box how could I configure that to make sure it's all set up so it
> won't leave the database all messed up?


I don't know all of the technical details, but if a user uses the STOP
button on a browser or the TCP connection is otherwise terminated, there is
a feedback mechanism where the PHP script producing the HTML can be
terminated (it has no further utility, as it would be feeding a non-existent
connection at that point).

A worst case is where the PHP script is aborted in a critical section
(depending on how transactions are handled in the database) so that the
database is left in an inconsistent state. Whether this is possible depends
on how you choose to do locking and transactions.

The safest approaches I'm aware of are:

#1)Form the database results and close the database connection before
generating output.

#2)Use the PHP function intended for that purpose.

Here is the URL for the PHP function:

http://us.php.net/manual/en/function...user-abort.php

http://us.php.net/manual/en/features...n-handling.php

Best regards, Dave

Personally, I think I'd go with one DATABASE per customer. That way the your
code would be the same, and easier to handle. It would be easier to manage
the security at the database level, I suspect. I'd set up a ../inc directory
outside the web server root that would have one file per customer, and would
have the customer-specific variables such as database name, password, and so
forth. Each file would be named after a customer. You'd prompt for a user
name and password, include the appropriate customer-specific .inc file,
check the password against what the user supplied, and if it passed then
create a session with the .inc file variables stored as session variables.

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com


> -----Original Message-----
> From: Jason Pruim [mailto:japruim@raoset.com]
> Sent: Thursday, August 23, 2007 10:59 AM
> To: MySQL List
> Subject: Database architecture and security
>
> Hi Everyone,
>
> Just had a quick question about a database I'm working on.
>
> I am planning on having the database open to customers of mine to
> store their mailing addresses on-line, and be able to manage the
> records.
>
> Is it safe, to have 1 database with lots of tables? Or am I safer
> setting up separate databases for everyone?
>
> I should mention, no one will be accessing the database directly,
> it'll be through a web interface and php to display it.
>
> Any info would be greatly appreciated!
>
>
> --
>
> Jason Pruim
> Raoset Inc.
> Technology Manager
> MQC Specialist
> 3251 132nd ave
> Holland, MI, 49424
> www.raoset.com
> japruim@raoset.com
>
>
>

I concur. Also it makes it easier to remove a customer if they leave.
Finally your backups will only lock up one customer's database at time and
for a much shorter period.

On Thu, August 23, 2007 10:50, Jerry Schwartz said:
> Personally, I think I'd go with one DATABASE per customer. That way the
> your
> code would be the same, and easier to handle. It would be easier to manage
> the security at the database level, I suspect. I'd set up a ../inc
> directory
> outside the web server root that would have one file per customer, and
> would
> have the customer-specific variables such as database name, password, and
> so
> forth. Each file would be named after a customer. You'd prompt for a user
> name and password, include the appropriate customer-specific .inc file,
> check the password against what the user supplied, and if it passed then
> create a session with the .inc file variables stored as session variables.
>
> Regards,
>
> Jerry Schwartz
> The Infoshop by Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
>
> www.the-infoshop.com
> www.giiexpress.com
> www.etudes-marche.com
>
>
>> -----Original Message-----
>> From: Jason Pruim [mailto:japruim@raoset.com]
>> Sent: Thursday, August 23, 2007 10:59 AM
>> To: MySQL List
>> Subject: Database architecture and security
>>
>> Hi Everyone,
>>
>> Just had a quick question about a database I'm working on.
>>
>> I am planning on having the database open to customers of mine to
>> store their mailing addresses on-line, and be able to manage the
>> records.
>>
>> Is it safe, to have 1 database with lots of tables? Or am I safer
>> setting up separate databases for everyone?
>>
>> I should mention, no one will be accessing the database directly,
>> it'll be through a web interface and php to display it.
>>
>> Any info would be greatly appreciated!
>>
>>
>> --
>>
>> Jason Pruim
>> Raoset Inc.
>> Technology Manager
>> MQC Specialist
>> 3251 132nd ave
>> Holland, MI, 49424
>> www.raoset.com
>> japruim@raoset.com