I need to manage documents in my aplication and I have some doubts about how to implement it.
I was thinking on creating only one “document table” for my aplication.
All documents will be related to a record in the main table where they will be uploaded from (but a given document could be related to other records in the main table as well). I will relate a given document with the main table with a join table between them.
Some of those documents will ocasionally be related to "clients" table too. I was also thinking on relating them with a join table.
The aim of relating the documents with the join table is to store each document only once and then, relate each document to diferent tables by creating a new record on the join table.
Documents will be external secure.
1- Is it a good aproach to do this?
2- Would it be a good practice to make a different table for each join table (for example, “JT_main_table_DOCUMENTS” for relating the “main table” and “document table” on one hand and a different table “JT_clients_DOCUMENTS” for relating “client table” and “document table”on the other hand. In the event of needing to relate a 3th table, I would also create a new join table for it and so on). Otherwise, I could only create just one join table “JT_documents” to be used in all cases.
3- If a document is deleted in “document table”, It will also be deleted from all other related records where it might not be deleted. If I just delete the join table record, I could leave a given document record unrelated to other records (occupaying space).
4- This aplication will be document intensive. There will be lots of documents and therefore, lots of records in “document table”. Could performance be affected?