Dear MySQL User,
I am sending the table schema of 5 tables.Here pid (partner ID) is in the top layer,
aid (Affiliate ID) is in the middle layer and uid (User Id OR Customer ID) is in the
bottem layer.User may promoted to Affiliate and affiliate may promoted to partner.We
have to store there billing and contact info...Could you pls check that design is
perfect?Pls send ur comments?
Regards,
mysql> desc tbl_user;
+-------------+-------------------+------+-----+----------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+----------+----------------+
| uid | smallint(8) | | PRI | NULL | auto_increment |
| aid | smallint(8) | | PRI | 0 | |
| pid | smallint(8) | | PRI | 0 | |
| username | varchar(16) | | | | |
| password | varchar(16) | | | | |
| user_status | enum('A','I','S') | YES | | NULL | |
| secret_word | varchar(20) | YES | | NULL | |
| language | varchar(8) | | | ENG | |
| flag_group | double(8,0) | | | 0 | |
| group_name | varchar(16) | YES | | Customer | |
| db_update | timestamp(14) | YES | | NULL | |
| update_user | smallint(8) | | | 0 | |
+-------------+-------------------+------+-----+----------+----------------+
12 rows in set (0.00 sec)
mysql> desc tbl_affiliate;
+------------------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------------+------+-----+---------+-------+
| aid | smallint(8) | | PRI | 0 | |
| pid | smallint(8) | | PRI | 0 | |
| affil_type | enum('1','2','3') | | | 1 | |
| affil_order_date | timestamp(14) | YES | | NULL | |
| affil_status | enum('A','I','S') | YES | | NULL | |
| affil_aux_data | varchar(64) | YES | | NULL | |
| db_update | timestamp(14) | YES | | NULL | |
| update_user | smallint(8) | | | 0 | |
+------------------+-------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
mysql> desc tbl_partner;
+--------------------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+-------------------+------+-----+---------+-------+
| pid | smallint(8) | | PRI | 0 | |
| partner_type | enum('1','2','3') | | | 1 | |
| partner_order_date | timestamp(14) | YES | | NULL | |
| partner_status | enum('A','I','S') | YES | | NULL | |
| partner_aux_data | varchar(64) | YES | | NULL | |
| db_update | timestamp(14) | YES | | NULL | |
| update_user | smallint(8) | | | 0 | |
+--------------------+-------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql> desc tbl_user_contact_info;
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| uid | smallint(8) | | PRI | 0 | |
| email | varchar(64) | | | | |
| email_alt | varchar(64) | | | | |
| first_name | varchar(12) | | | | |
| last_name | varchar(12) | | | | |
| company | varchar(32) | YES | | NULL | |
| address_1 | varchar(32) | YES | | NULL | |
| address_2 | varchar(32) | YES | | NULL | |
| city | varchar(20) | YES | | NULL | |
| state | varchar(20) | YES | | NULL | |
| zip | varchar(9) | YES | | NULL | |
| country | char(2) | | | | |
| phone | varchar(24) | YES | | NULL | |
| fax | varchar(24) | YES | | NULL | |
| cell_phone | varchar(24) | YES | | NULL | |
| db_update | timestamp(14) | YES | | NULL | |
| update_user | smallint(8) | | | 0 | |
+-------------+---------------+------+-----+---------+-------+
17 rows in set (0.00 sec)
mysql> desc tbl_user_billing_info;
+--------------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+-----------------------+------+-----+---------+-------+
| uid | smallint(8) | | PRI | 0 | |
| cc_number | int(16) | YES | | NULL | |
| cc_auth_code | int(11) | YES | | NULL | |
| cc_type | enum('V','m','d','o') | YES | | NULL | |
| cc_exp_date | date | YES | | NULL | |
| payment_account | int(11) | YES | | NULL | |
| payment_routing | int(11) | YES | | NULL | |
| payment_aux | varchar(32) | YES | | NULL | |
| payment_id | decimal(11,2) | | | 0.00 | |
| payment_gateway | decimal(11,2) | | | 0.00 | |
| export_billing | decimal(11,2) | | | 0.00 | |
| outsourced_billing | decimal(11,2) | | | 0.00 | |
| invoicing_type | varchar(16) | | | | |
| db_update | timestamp(14) | YES | | NULL | |
| update_user | smallint(8) | | | 0 | |
+--------------------+-----------------------+------+-----+---------+-------+
15 rows in set (0.00 sec)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
