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
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
