BLOB field for Email attachments

[cyberant]

Hey!

I'm creating an email browser program and am trying to store my attachments into a BLOB field in an interbase database. I'm not having trouble storing a single attachment into the field, but multiple attachments have become quite tricky. I was thinking of creating 5 BLOB fields and making the attachment limit 5, but this seems too clumsy. I've heard that you can store more than a single file into a BLOB field via streaming, the trick is getting the starting and ending point of the file.

Any ideas?

 

[MartijnTonies]

Ahum - a totally "radical" idea:

TABLE EMAIL
-----------
EmailID
Subject
Text

TABLE ATTACHMENT
----------------
Attachment (BLOB)
EmailID - points to EMAIL

This way, you can have as many attachments as you like.


Martijn Tonies
Database Workbench - the developer tool for InterBase and Firebird

http://www.upscene.com

 

[cyberant]

Thanks Martijn

I wanted to use that method all along, but my boss has a problem with too many tables, he wanted all email information to be contained in a single email table.

I'm going to twist his arm though.

Thanks again.

 

[MartijnTonies]

"but my boss has a problem with too many tables"

*g* ... well, isn't that a good argument for table design... hehehe...

Didn't your boss read anything on normalization and keeping a design flexible?

Martijn Tonies
Database Workbench - the developer tool for InterBase and Firebird

http://www.upscene.com

 

[cyberant]

His theory is that its better to have fewer tables with many fields, rather than many tables and fewer fields. Is this true in your opinion or does it not affect database spped to such an extent that should warrant an implementation such as this?

 

[MartijnTonies]

I would certainly say that narrower tables (with less fields) is better when it comes to physical performance. A narrower table means less data-pages (on harddisk) to go through when getting results.

When it comes to design - you shouldn't care about wide or narrow, but care about normalization. Usually, if your table has many many fields, you need to redesign.

In your particular case, you already found out that doing things in a single table means you've lost complete flexibility with regard to the emails you'll be able to process.


Martijn Tonies
Database Workbench - the developer tool for InterBase and Firebird

http://www.upscene.com