Database structure question: one or multiple tables?

Hi there!

I am currently working on a research project management tool. This tool shall support the process of offering topics, users making a choice of topics and then developing proposals, proposals being accepted by supervisors and turned into projects, and once a project gets done, it gets archived.

Would you, for such a type of demand, work with

  1. one big table that might be called “Work” that would contain all fields for all different types of work (entries describing topics would be emptier than fully completed, archived projects), or

  2. multiple more specialized tables that might be called “Topics”, “Proposals”, “Projects”, “Archived”, each only containing their relevant set of fields. For this solution, I’d probably have links back to the previous work thing the current thing would have emerged from (e.g., having a “fromTopic” field in “Proposals”).

What would be the advantages and disadvantages? Which path would you take?

If Topics, Proposals, and Projects are all separate things within your app, then they should definitely be separate datatypes.

If they share all (or most) of the same fields, then they could be a single datatype, and you could differentiate between them some other way.

But from what you’ve described, using separate datatypes is the most logical approach.

(I’m not sure what your ‘Archived’ datatype would be though… I don’t think you’d need that)

1 Like

Thank you very much, @adamhholmes ! That makes a lot of sense. I did read on after posting and also found your thoughts in another discussion and suggestion that keeping the tables smaller provides a performance improvement since you only load what you need - makes plenty of sense.

Thank you for your help, again! :slight_smile:

PS: Archived is to be able to access past projects which should not clutter up the current listing of projects anymore.

Yeah, I figured that’s what archived was… so you don’t need to duplicate things into separate tables for that… just mark them as archived in their respective databases.

1 Like