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@stripped
