Hello, list --

No problem, yet. ;-)

Wondering if anyone would have a suggestion to ensure better performance, or
could point out any likely errors in the database outlined below.

Basically, I have digital pictures, scanned text/forms and emails that all
relate to information indexed in a separate DB with "shipment_id". I don't
have any real experience with DB design, so any suggestions or things to
consider would be appreciated.

My thinking is that I create an overview with an id and store that id in the
other tables so I can get all related documents. (My next question will be
how to query the lot in a single statement...)

All the tables are ENGINE=MyISAM and DEFAULT CHARSET=utf8. I don't know
squat about configuration parameters but an error in putting up the images
led me to change this line in my.cnf:

max_allowed_packet = 4M

.... because I don't know how to put up a binary in chunks, I guess. (I'm
using DBD::mysql and CGI in perl and inserting the uploaded file with a
placeholder in my SQL...)

Thanks in advance for any helpful suggestions, corrections or
clarifications. ;-)

Cheers,

Michael Higgins

######################### db info #######################

+------------------+
| Tables_in_claims |
+------------------+
| carrdocs |
| claimsubs |
| emails |
| overview |
| pictures |
+------------------+


mysql> describe carrdocs;
+---------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------+------+-----+---------+----------------+
| cardoc_id | int(11) | NO | PRI | NULL | auto_increment |
| claim_id | int(11) | NO | | | |
| carr_doc | mediumblob | YES | | NULL | |
| carr_doctype | tinytext | YES | | NULL | |
| carr_mimetype | tinytext | YES | | NULL | |
+---------------+------------+------+-----+---------+----------------+
5 rows in set (0.13 sec)

mysql> describe claimsubs;
+--------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------+------+-----+---------+----------------+
| claimsub_id | int(11) | NO | PRI | NULL | auto_increment |
| claim_id | int(11) | NO | | | |
| claim_doc | mediumblob | YES | | NULL | |
| clm_doctype | tinytext | YES | | NULL | |
| clm_mimetype | tinytext | YES | | NULL | |
| clmdoc_name | tinytext | YES | | NULL | |
+--------------+------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

mysql> describe emails;
+----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+----------------+
| email_id | int(11) | NO | PRI | NULL | auto_increment |
| claim_id | int(11) | NO | | | |
| email | text | YES | | NULL | |
+----------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> describe overview;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| claim_id | int(11) | NO | PRI | NULL | auto_increment |
| shipment_id | int(11) | NO | UNI | | |
| claimant | varchar(60) | YES | | NULL | |
| clmnt_email | varchar(60) | YES | | NULL | |
| claim_rep | varchar(60) | YES | | NULL | |
| rep_email | varchar(60) | YES | | NULL | |
| carr_clm_no | varchar(30) | YES | | NULL | |
| pro_number | varchar(30) | YES | | NULL | |
| carrier | varchar(60) | YES | | NULL | |
| claim_amt | varchar(10) | YES | | NULL | |
| claim_notes | text | YES | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)

mysql> describe pictures;
+--------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------+------+-----+---------+----------------+
| image_id | int(11) | NO | PRI | NULL | auto_increment |
| claim_id | int(11) | NO | | | |
| image_note | text | YES | | NULL | |
| image | mediumblob | YES | | NULL | |
| img_mimetype | tinytext | YES | | NULL | |
| img_name | tinytext | YES | | NULL | |
+--------------+------------+------+-----+---------+----------------+