Re: help with table structure

[Justin Smith]

What you have so far looks good, but what I learned from doing my ecomm 
project was that it is beneficial to make a separate table for anything 
and everything that you might have more than one of...  Addresses, phone 
numbers, and email addresses are all great candidates for breaking out 
into another table.  That way, if you want to have a billing address and 
a shipping address for each customer, for example, all you have to do is 
have an address table with all of the address information, and a 
relation table that describes the relationship between an address and a 
user:

address_id int not null,  (foreign key corresponding to an address ID in 
your address table)
user_id int not null,  (foreign key corresponding to a user ID in your 
user table)
address_type int  (1=billing address, 2=shipping address, 3=secondary 
shipping, etc.)

I hope this helps.
Chris W. Parker wrote:
hello,
i'm just looking for some examples of a customer table that some of you
are using for your ecomm sites (or any site that would need a "customer"
table).
here is mine so far:
(horrible wrapping to follow...)
mysql> describe customers;
+----------------+---------------------+------+-----+-------------------
--+----------------+
| Field          | Type                | Null | Key | Default
| Extra          |
+----------------+---------------------+------+-----+-------------------
--+----------------+
| id             | int(10) unsigned    |      | PRI | NULL
| auto_increment |
| fname          | varchar(20)         |      | PRI |
|                |
| lname          | varchar(20)         |      | PRI |
|                |
| address1       | varchar(40)         |      |     |
|                |
| address2       | varchar(40)         | YES  |     |
|                |
| city           | varchar(20)         |      |     |
|                |
| state          | char(2)             |      |     |
|                |
| zip            | varchar(10)         |      |     |
|                |
| phone          | varchar(20)         | YES  |     |
|                |
| fax            | varchar(20)         | YES  |     |
|                |
| email          | varchar(64)         |      | PRI |
|                |
| newsletter     | tinyint(1)          |      |     | 0
|                |
| password       | varchar(32)         |      |     |
|                |
| signupdate     | datetime            |      |     | 0000-00-00
00:00:00 |                |
| lastvisit      | datetime            |      |     | 0000-00-00
00:00:00 |                |
| type           | tinyint(3) unsigned |      |     | 0
|                |
| company        | varchar(64)         | YES  |     |
|                |
| is_active      | tinyint(4)          |      |     | 0
|                |
| activationdate | datetime            |      |     | 0000-00-00
00:00:00 |                |
| activationtype | tinyint(3) unsigned |      |     | 0
|                |
+----------------+---------------------+------+-----+-------------------
--+----------------+
i would appreciate not only table descriptions (like mine above)(if
you're willing) but comments on what i have so far as well.
thank you,
chris.
 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]