I am not too experienced in database best practices so I am wondering if anyone has some advice on whether to make a new database table or just differentiate between columns on an existing table.

For example, if I had a table for uploaded documents...

documents id | type | description | url

I can either enforce an enumeration requirement at the application level that requires type to be either application, information, or contract.

or I can make a different document table for each of these. Having different tables would make me feel a bit safer, but then I would need to add more endpoints to the consuming application to take care of creation, destruction, etc so it seems a bit cumbersome. Any advice so I don't shoot myself in the foot?


You have to take decision based on below two questions.

a). Do you expect to get more records in each type (Bank,Applicaition..)?

Ans). Go for separate tables.

Lets say Bank might get 200000 records, application might get 50000 records. And if their rate of growth is expected to grow higher its better to keep them in seperate table names like bank_documents, application_documents etc. By this you might avoid issues when you alter the table or add index you can go table by table. Ofcourse it is back ache to DBA but no stress.

b). If there is not much of records? Or not much bigger rate of growth?

Ans). Then go for single table for all types and you may figure later as and when data grows you may prepare a road-map for application team to split types to different tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.