Seeking advice on many versus fewer tables?

Discussion created by user17152 on Jun 12, 2016
Latest reply on Jun 19, 2016 by PeterWindle

I'm in the process of redesigning the solution I've built.  Without getting into too many details, the project has been one of those "make it up as you go along" projects and, as a result, is predictably bloated.  Now that we've more or less figured out what my client really wants and needs, I'd like to simplify the overall design and make everything web friendly.  When we started this project, they weren't interested in the web.  Now they want to access everything via web direct (go figure...), so some optimization is definitely required.  I will say, I'm quite impressed by web direct.  It handles my existing solution quite well.  One click indeed.


Imagine a task manager with a variety of task types.  Each type has a few type-specific bits of information.  For example, one task is to issue a Purchase Order on a certain date.  When viewing that task, the users needs to see the PO number, dollar value, and whether the items on the PO are being delivered to the job site or the office.  When viewing a Punch Item, the user needs to see the Punch topic and summary and delivery information for items on the Punch, if applicable.


At the moment these different task types each have their own table.  Each one links to a single Events table that stores the due date, task status, and completion date (we generate calendars from this table using SeedCode Subscribe).  When this project started, it made sense to have a different table for each task type as I thought we might build out additional type-specific features.  But I don't really see that happening.


If I created a single Tasks table with five text fields and five number fields, I could replace all of the smaller type-specific tables.  Each task type would retain it's unique layout, but the underlying table could be the same.  If task type = "Purchase Order", textfield1 stores the PO number and numberfield1 stores the dollar amount.  If task type = "Punch", textfield1 stores the Topic and textfield2 stores the Summary.  Etc.  Some task types would use most of the fields.  Others might only use one or two.


There are other common features that could be simplified as well.  For example, Purchase Orders, Punch Items, and Change Orders may all have related Deliverables records displayed in a portal on the respective layout.  Deliverables function the same for each of these task types.  Another common feature is the ability to attach documents and images to Punch Items and Purchase Orders (and, in the future, Submittals, RFIs, etc).  Although there is a single Deliverables table and single Documents table, there are multiple instances on the graph to accommodate for the fact that Purchase Orders are one table and Punch Items are another.  And all scripts related to managing Deliverables and Documents have to take that into account as well.


The upside to creating a single Tasks table (with one relationship for Deliverables, one for Documents, etc) I see is simplicity.  It would greatly simplify my relationship graph and scripts.  The downside I see is creating one big monster Tasks table and losing the ability to further build out specific task types into their own modules.  That said, whereas I used to think Purchase Orders might one day grow into a full-fledged quoting system, I no longer believe that.  So is it overkill to have all these smaller tables?


I also don't see combining Events and Tasks.  I still feel that date information should reside in a different table, but instead of linking individual task type tables (ie: Punch, Purchase Order) to Events, there would now be a single Tasks table.  The reason I don't want to further consolidate things by merging Tasks and Events is that we have a lot of non-task dates that we want to see on our Project calendars.  When such a date is added to the project, an Event record is created and linked to the project, but no task record is created.  If Events and Tasks merged into one table, at least 50% of the records would contain absolutely no task information, so I felt that keeping task information in another table made sense.


Any and all thoughts appreciated.  Thanks!