SEO

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 | |
+--------------+------------+------+-----+---------+----------------+

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 <mhiggins@banfieldgroup.com> 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/mysql?unsub=drbuettner@gmail.com
>
>

> -----Original Message-----
> From: Dan Buettner [mailto:drbuettner@gmail.com]
>
> Michael, this looks pretty decent overall.
>
> I'm a big fan of fully descriptive table and column names,

[8<] (All good suggestions, thanks.)

> 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)

Okay, this was exactly the kind of question I had. So, if I do that, then
mysql will just access that INDEX information internally? IOW, I don't
actually query on that field, or ever have to think about it again, right?

>
> 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.
>

Yeah, I get that... but what I'm looking for is to select (all non-blob
fields) from (all the tables) where claim_id = xxxxx ..... [what do I do
here? some kind of a 'join'?]

This way, I'd be able to get access to each record associated with that
claim_id from one, say, webpage.

Like, having retrieved an image_id from the monolithic query, I could then
retrieve the associated image blob with another query.

Anyway, I'm sure this will all become clearer to me eventually... ;-)

Thanks a bunch,

Michael Higgins

You might benefit from drawing your layout as a picture, if you haven't
already. Use arrows to connect the fields in each table with the fields in
other tables that they will "hook" to. That will give you an idea of which
fields to index and JOIN on. You JOIN on those fields, and you index the
ones that have many different values.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341


> -----Original Message-----
> From: Michael Higgins [mailto:mhiggins@banfieldgroup.com]
> Sent: Wednesday, May 02, 2007 3:20 PM
> To: 'Dan Buettner'
> Cc: mysql@lists.mysql.com
> Subject: RE: basic architecture review?
>
> > -----Original Message-----
> > From: Dan Buettner [mailto:drbuettner@gmail.com]
> >
> > Michael, this looks pretty decent overall.
> >
> > I'm a big fan of fully descriptive table and column names,
>
> [8<] (All good suggestions, thanks.)
>
> > 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)
>
> Okay, this was exactly the kind of question I had. So, if I
> do that, then
> mysql will just access that INDEX information internally? IOW, I don't
> actually query on that field, or ever have to think about it
> again, right?
>
> >
> > 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.
> >
>
> Yeah, I get that... but what I'm looking for is to select
> (all non-blob
> fields) from (all the tables) where claim_id = xxxxx .....
> [what do I do
> here? some kind of a 'join'?]
>
> This way, I'd be able to get access to each record associated
> with that
> claim_id from one, say, webpage.
>
> Like, having retrieved an image_id from the monolithic query,
> I could then
> retrieve the associated image blob with another query.
>
> Anyway, I'm sure this will all become clearer to me eventually... ;-)
>
> Thanks a bunch,
>
> Michael Higgins
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=j...e-infoshop.com
>
>