Posted by MattMika on 09/07/05 19:33

I have been given the task of creating a webapp to take orders for team
uniforms. I've setup a preliminary DB, but feel the structure could be
better. Anyone have any suggestions or comments? I've included preliminary
tables and some data.

TBL_users
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| user_id | int(8) | | PRI | NULL | auto_increment |
| username | varchar(8) | | | | |
| password | varchar(13) | | | | |
| admin | int(1) | | | 0 | |
| fname | varchar(30) | | | | |
| lname | varchar(25) | | | | |
| address | varchar(35) | | | | |
| address2 | varchar(35) | | | | |
| city | varchar(20) | | | | |
| state | char(2) | | | | |
| zip | varchar(5) | | | | |
| country | varchar(25) | | | | |
| phone | varchar(12) | | | | |
| fax | varchar(12) | | | | |
| email | varchar(30) | | | | |
| hint | varchar(40) | | | | |
| answer | varchar(20) | | | | |
| insertdate | varchar(12) | | | | |
+------------+-------------+------+-----+---------+----------------+

TBL_teams
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| team_id | int(8) | | PRI | NULL | auto_increment |
| user_id | int(8) | | | 0 | |
| team_name | varchar(30) | | | | |
| jersey_color | varchar(20) | | | | |
| logo_style | varchar(20) | | | | |
| jersey_style | varchar(20) | | | | |
| pant_style | varchar(20) | | | | |
| pant_color | varchar(20) | | | | |
| cap_style | varchar(20) | | | | |
| cap_color | varchar(20) | | | | |
| league | varchar(20) | | | | |
+--------------+-------------+------+-----+---------+----------------+

TBL_players
+-------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------+------+-----+---------+----------------+
| player_id | int(8) | | PRI | NULL | auto_increment |
| team_id | int(8) | | | 0 | |
| player_num | char(2) | YES | | NULL | |
| jersey_size | varchar(5) | | | | |
| pant_size | varchar(5) | | | | |
| cap_size | varchar(5) | | | | |
+-------------+------------+------+-----+---------+----------------+

TBL_options
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| option_id | int(4) | | PRI | NULL | auto_increment |
| option_type | varchar(20) | | | | |
| option_value | varchar(20) | | | | |
+--------------+-------------+------+-----+---------+----------------+
1 | jersey_style | Mesh
2 | jersey_style | T-shirt
3 | jersey_style | Vest
4 | jersey_style | V-neck
5 | logo_style | Tailsweep
6 | logo_style | Collegiate
7 | logo_style | Olde English
8 | cap_style | Flexfit
9 | cap_style | Adjustable
10 | pant_style | Regular
11 | league | Softball

TBL_color_to_options
+-----------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+----------+------+-----+---------+----------------+
| color_option_id | int(4) | | PRI | NULL | auto_increment |
| option_id | int(4) | | | 0 | |
| color | char(10) | | | | |
+-----------------+----------+------+-----+---------+----------------+
1 | 1 | Black
2 | 1 | Gold
3 | 1 | Green
4 | 1 | Grey
5 | 1 | Navy
6 | 1 | Orange
7 | 1 | Purple
8 | 1 | Red
9 | 1 | Royal
10 | 2 | Black
11 | 2 | Gold
12 | 2 | Green
13 | 2 | Grey


TBL_price_to_options
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| price_option_id | int(4) | | | 0 | |
| option_id | int(4) | | | 0 | |
| price | decimal(3,2) | | | 0.00 | |
+-----------------+--------------+------+-----+---------+-------+
1 | 1 | 16.99
2 | 2 | 14.99
3 | 3 | 24.99
4 | 4 | 22.99


TBL_size_to_options
+----------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------+------+-----+---------+----------------+
| size_option_id | int(4) | | PRI | NULL | auto_increment |
| option_id | int(4) | | | 0 | |
| size | char(5) | | | | |
+----------------+---------+------+-----+---------+----------------+
6 | 1 | M
7 | 1 | L
8 | 1 | XL
9 | 1 | 2XL
10 | 2 | YS
11 | 2 | YM
12 | 2 | YL
13 | 2 | XS
14 | 2 | S
15 | 2 | M

I'm wondering if I should break the various uniform components out to their
own tables? Should I create tables for sizes and colors rather than what I
have here? Any pointers would be appreciated.

Thanks,
Matt

[Back to original message]