Tables vs Views Scenario

1

Sorry if this is already covered somewhere but I just wanted some opinions on a scenario.

I am using a doc purely as a database and we have customers and associates, the associates can also be customers but a relatively small percentage (which will likely decrease as time goes on and we populate more of the database).

So originally I set up the associate info and the customer as two separate tables with a plan to link the ones where they were both (I partially didn’t want to have too many columns on the one table as the detail view would get clunky, I am now comfortable I can work many columns suitably for our people that are doing data entry so that concern is gone). I was using the associates as a limited dropdown but ultimately just ended up linking that dropdown list to the whole customers table as we were ending up with duplicates/mismatches so the associates table has sat unused in the background for some time (I am doing this around my normal day job so haven’t had a lot of time to do stuff). I recently sat in on some live sessions and am happy now that I can set up so that the associates table is automatically populated whenever a customer acts as an associate for another customer.

My inclination is now this really should be 1 table with a view showing just the associate info.

So these numbers are by way of illustration but are in the ball park, we would be talking a few thousand customers and a couple hundred associates

A) I have one table with circa 100 columns, I would hide a load of the columns on one so this effectively just shows the customer info, then have a connected view with the customer stuff all hidden and just the associate info showing (probably with a filter to only show associates)

In this big table there would be thousands of rows where half of the columns are empty and always would be.

B) I have two tables of circa 50 columns each and link them up (I can automate this with buttons fine)

In this scenario I’ve got probably an extra few hundred rows and the associated relations that the other doesn’t have but don’t have loads of empty columns on most of the rows.

Which is better in terms of the running speed of the database?

2

It comes down to two main tradeoffs:
Option A (Single Table):

  • Performance impact of empty columns (negligible, AFAIK)

Option B (Two Tables):

  • Complex sync logic to maintain
  • Higher risk of breaking something when expanding the system

Option A is clearly the winner in my opinion. The minimal performance impact of empty columns is far outweighed by avoiding the complexity and risks of maintaining synchronized tables.

1 Like