I have different kind of documents which have common fields (not all of them, but most) and I thought to put them in the same table and use ENUM to differentiate them.
Then I realized that if have thousands of docs of every kind I will have a (very?) slow SELECT/UPDATE's right?
So my question is: should I split the tables and have one table for each document? or is the initial approach the way to go?
Thank you
Best Answer
Certainly not one table for each document. Perhaps one for each document type.
In general you are correct - reading or writing to larger tables typically takes longer than the same operation on smaller tables. There are techniques to help with this - indexing for example. Modern DBMSs can easily handle tables of several million rows, however, so unless you are pushing these limits it is better for your design to match your requirements than to work around the limitations of the technology.
If you commonly
updateseveral different documents of different types simultaneously then a single table would be beneficial. If each document type is processed independently then separate tables would be better.