On Sat, 2 Jul 2011, Chris Travers wrote:
On Sat, Jul 2, 2011 at 5:26 AM, Erik Huelsmann <..hidden..> wrote:It's not clear to me what we need the different attachment classes for. Could you explain what you want to use them for? Isn't a file just a file?The file_base table is essentially a partitioned table of file attachments. The partitioning here occurs primarily for referential integrity enforcement reasons. Another possibility would be: CREATE TABLE file_attachment{ content bytea NOT NULL, mime_type_id int not null references mime_type(id), file_name text not null, description text, id serial primary key, ref_key int not null, ); CREATE TABLE file_to_transaction( file_id int references file_attachment(id), transaction_id int references transactions(id) ); CREATE TABLE file_to_order ( file_id int references file_attachment(id), order_id int references oe(id) ); In terms of theory, this second possibility comes out cleaner, but it also has two things going against it, in that traditional database theory doesn't typically assume the possibility of inherited tables in this way. These are: 1) A unified API for file retrieval and attachment operations, and 2) More complex data integrity rules can be enforced in this second option.
You would have to replicate the handling for other documents, right? I.E. quotes, payments, etc.
Where it loses is in the ability to essentially attach a file to both an order and an invoice, so that's the tradeoff.
We have not talked about what happens when you convert an order to an invoice. Did you envision the reference to the attachment going with it in your first model? Obviously that wouldn't happen here--the file would have to be uploaded twice.
I would rather see it go more generic.If you had an attached_to table, containing an ID for the document, and an ID for the file, unique keyed to both, it would not matter at the database level, which file was associated to which document(s)--a file could be associated with a quote, an order, and an invoice, if necessary.
You would have to manage security at some other level, I think, although I'm not sure you wouldn't already have to do that.
In a filesystem context, I am talking about hard links.Probably though, as I think about it, this would require globally unique filenames, and a name comparison with new uploads, possibly followed by a content comparison if names match.
I'm not sure globally unique filenames are such a bad idea anyway.
Ok, would you want to have all file attachments enabled/disabled on a per user level. or just per transaction type (financial, order entry, etc)?
If you have the latter, as the more complex case, you get the former by inclusion anyway.
I can see a reason for separating financial/transactional, from orders/quotes/etc.
It is probably more likely that orders will be posted from external systems, than that invoices will.
Luke