Michael, this looks pretty decent overall. I'm a big fan of fully descriptive table and column names, avoiding abbreviations except where truly needed, so I personally would spell out "claim" and "claimant" for example. I also like to separate words in table and column names with underscores, which you're already doing in most cases. And finally, I like to have the table name be plural and the primary key be singular_id. So, "users" table will have "user_id", for example (how I wish Ruby on Rails did that). I'd either rename your overview table to claims, or change the id column to overview_id
In the carrdocs table (two r's) you have a column named cardoc_id (one r). No biggie but you'll scratch your head more than once as you write SQL that doesn't work the first time. One performance suggestion: add an index on each table for the claim_id column. This will greatly speed retrieval of material related to a given claim/overview. ALTER TABLE x ADD INDEX claim_id_idx (claim_id) When you say "query the lot", what do you mean? Get all related "stuff" in a single SQL statement? Possible, but maybe a bit messy, and not as easy to maintain as a handful of routines that each get documents, emails, pictures. As you add more tables holding related material the SQL would become unwieldy and you'd likely break it down later anyway. HTH, Dan On 5/2/07, Michael Higgins <[EMAIL PROTECTED]> wrote:
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 | | +--------------+------------+------+-----+---------+----------------+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
