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