Strategies to uploading files to the server

[NotionCommotion]

I have an application where users can create one of 10 types of entity records, and 8 of these record types are identified by a single primary key, and 2 of them are identified by a duel composite key.

 

Users are then able to upload files to the server provided the file validates physically (i.e. file size, extension, etc), and whether the file can be "linked" to one of the previously mentioned entity records based on various "authorization" rules which will be promoted by the JavaScript client and enforced by the server either using PHP code and/or the database.  Upon successfully uploading a file and linking it to an entity record, several other transactions occur such as audit trails, etc.

 

My plan is to create the 10 described entity records table, a single "documents" table, and 10 junction tables to save the link state to the 10 entity tables.  For this discussion, please assume this schema is correct.

 

I would like recommendations on the steps to implement.

 

 

One option is when a file is submitted to the server...

1. Verify that all necessary POST/FILE data is provided
2. Verify that the file passes physical validation.
3. Make sure the authorization rules are met.
4. Store the file statistics in the "documents" table and retrieve its primary key.
5. Create the junction link record.
6. Move the file to the correct location.
7. Save audit trails, etc.

 

Another option is when a file is submitted to the server...

1. Verify that all necessary POST/FILE data is provided
2. Verify that the file meets passes physical validation.
3. Store the file statistics in the "documents" table and retrieve its primary key.
4. Attempt to create the junction link record using a query than must pass authorization rules.
5. If inserted...
   1. Move the file to the correct location.
   2. Save audit trails, etc.
6. If not inserted...
   1. Delete the file statistics in the database

 

Recommendations on which approach, or whether it should be a different approach all together?

[Jacques1]

The first option seems to be oversimplified. If anything goes wrong while inserting the junction record (which can always happen), you have to go through the exact same steps as in the second option. So really the only difference is whether the authorization rules should be checked in the application or by the database system. I think we need to see them to answer that question.

 

Have you considered using a transaction so that you don't have to clean up your database in case of an error?

[NotionCommotion]

Hi Jacques,

 

I am concerned that showing all the authorization rules will divert focus.  Guess if I better organized them, might not be the case, but unfortunately it is the case.

 

Yes, I have considered using transactions and will do so if they provide value.  Don't think it is necessary on the option 2.

 

I didn't say so, but the authorization rules will require a database query.  Seems that this is also a potential reason to go with option 2.

 

While I framed the question as I did, part (or most) of my dilemma is how to scope functions of a class to perform most of this scope.  I want a class which is responsible to upload and validate the file, store the file in the documents table, move the file to its correct directory, and even add these link junction records.  At least, I think this is what I want, but I have been going back and forth.  I believe the audit trails which I previously mentioned are outside of what this class should do, and plan on doing them after the action occurs.  Then comes these authorization rules that I mentioned.  They seem like they should be defined outside of this class which makes me question my option 2, but maybe they can still be defined outside and implemented within the class?

 

Hope I am not overly confusing the issue.

 

 

 

[Jacques1]

The design of some particular class is irrelevant at this point. You should worry about that later.

 

Like I said, we can't really give concrete advice without concrete information. Generally speaking:

• If the authorization check is complex, do it in the application, even if that requires an extra query. Don't stuff business logic into queries.
• The benefit of a transaction is that you don't have to delete any orphaned records at all. Either the entire procedure is successful, or you simply roll back everything.