List:General Discussion« Previous MessageNext Message »
From:Gary Josack Date:August 23 2007 3:44pm
Subject:Re: Database architecture and security
View as plain text  
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@stripped
>
>
>

Thread
Database architecture and securityJason Pruim23 Aug
  • Re: Database architecture and securityRolando Edwards23 Aug
    • Re: Database architecture and securityJason Pruim23 Aug
  • Re: Database architecture and securityGary Josack23 Aug
    • Re: Database architecture and securityJason Pruim23 Aug
  • Re: Database architecture and securityDavid T. Ashley23 Aug
    • Re: Database architecture and securityJason Pruim23 Aug
      • Re: Database architecture and securityDavid T. Ashley23 Aug
  • RE: Database architecture and securityJerry Schwartz23 Aug
    • RE: Database architecture and securityWm Mussatto23 Aug