What is the best database design when storing documents of different structures for users?

Question

The requirement is: Users should be able to define the structure of the document they want to store.

For example, a user can decide to store a bank statement with the following structure:

• post_date (date)
• particulars (text)
• debit (number)
• credit (number)
• value_date (date)
• reference (text)

then select a file to load into the database.

Another user or the same user can also load a different document with a different structure.

If we knew all the different structures of documents available, we can create all the tables in the database, but the system must be such a way that the user can load any type of document, and define its own structures.

I am looking for a way to save all the rows from the documents into a single table if possible. Or should I create dynamically a new table when the user define a new structure of document to load? Is this the best way?

Answer 1

What is the best database design when storing documents of different structures for users?

...the system must be such a way that the user can load any type of document, and define its own structures.

None. There is no database design for what you describe. Saying that it needs to be able to store anything and everything means there is no way to structure it.

Options

1. What you can do though is dynamically create the table based on the document definition provided by the user. You would request the names and types of each column in the document. Then you can generate a CREATE TABLE statement (in whichever programming language of your choice) that creates that table as a part of the process. It would probably be best to have an internal UserTables mapping table to store the key for the User and the name of the tables that they've created, so you can easily reference this list in the application. Depending on the database system, you could utilize schemas to segregate Users to their own schema. This would make organization and security control better.

2. Alternatively you can try using the EAV anti-pattern. In such a design, a single table typically has 3 columns: The key of the entity (model / what would've been table), the name of the attribute (column name), the value (of that attribute / column). While enticing on first glance, and meets your idea of storing everything in a single table, there are a lot of drawbacks of using this anti-pattern. Some of the drawbacks include losing the data types, losing data integrity enforcement, no relationship enforcement, poor scalability and poor query performance, etc. There's very few scenarios where EAV makes sense, and even in your case I'd say option 1 is the better choice.

Answer 2

I am looking for a way to save all the rows from the documents into a single table if possible.

Store them as XML or JSON. (Whether that means using XML/JSON/NVARCHAR column in SQL database, or outright using NoSQL solution.)

Of course, this will make the querying by the contents of the document somewhat cumbersome. But if it is simply way to dump your data and then retrieve it, this would be valid solution.

If you need to extensively work with the contents of the documents, this might not be such a good idea.