List:General Discussion« Previous MessageNext Message »
From:Jason Pruim Date:August 23 2007 4:37pm
Subject:Re: Database architecture and security
View as plain text  
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


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