Member Avatar for zachattack05

Hi everyone!

So we have a web application that some of our clients use to manage their account. Part of that means they can manage activities that their registered employees do. Right now we have a table that logs all of a customer's employees activities, even if they don't use the web application.

We would like to add the ability to have our web application users add the outcome of the activity, notes, and other pieces of data (about 10 in total).

The problem is that not all of our customers use the app so they wouldn't be adding this data to our system, but instead would manage it with pencil and paper (old school style!) and even those that use the web app aren't required to enter this extra data, they can simply monitor the activities and still manage the outcomes with pencil and paper.

Given this, should I just add new fields to the existing table of activities even though a lot of them may end up being NULL values or should I use a lookup table and a 1-1 relationship?

My initial thought was to put the new fields in the existing table, (which has about 120,000 rows now and averages a growth of about 1,500 new records per month) but I don't know how many users will actually be adding the data in the future and I don't want to shoot myself in the foot. I'd rather design with the idea that the database is millions of records now so that things aren't slow and a problem later.

Any thoughts or ideas?

Edited by zachattack05 because: made it easier on the eyes.
  • 2 Contributors
  • 1 Reply
  • 304 Views
  • 7 Minutes Discussion Span
  • Latest Post Latest Post by Member #1111627

Dani AI

Generated

Short answer: put the 10 optional fields in a separate 1:1 table. That keeps the main Activities rows narrow and fast for the common case (most rows will stay “paper-only” and NULL-heavy). This follows the normalization idea raised by , but with a few practical implementation and operational notes for the scenario described by (120k rows, ~1.5k/month growth).

Why a 1:1 table

  • Keeps the hot path (queries that only read activity basics) lean: fewer I/O, smaller clustered index pages, and smaller nonclustered indexes.
  • Makes it easy to add constraints, defaults, and tighter security to the optional data without touching the base table.
  • Avoids lots of NULL columns that bloat schema and complicate analytics.

Implementation sketch
Use a details table whose primary key is also a foreign key to Activities to enforce 1:1. Access with a LEFT JOIN when extra data exists:

CREATE TABLE ActivityDetails (
  ActivityID INT PRIMARY KEY,       -- equals Activities.ActivityID
  Outcome VARCHAR(200),
  Notes NVARCHAR(MAX),
  -- other optional columns
  FOREIGN KEY (ActivityID) REFERENCES Activities(ActivityID)
);

SELECT A.*, D.Outcome, D.Notes
FROM Activities A
LEFT JOIN ActivityDetails D ON A.ActivityID = D.ActivityID;

When to keep columns on Activities
If a substantial fraction of rows (rule of thumb: many tens of percent) will actually populate those fields and queries nearly always need them, adding columns to the main table can be simpler and faster. If usage is sparse or unknown, separate table wins.

Other options and tips
Consider SQL Server features when appropriate: filtered indexes on non-null optional columns, SPARSE columns for extreme-null cases (weigh the tradeoffs), or a JSON column if the attributes are highly dynamic. Avoid an EAV model unless attributes are truly unpredictable. Test the chosen design with realistic data volumes and build a migration/backfill plan before deploying.

Member Avatar for Member #1111627

I would create a new table with a foreign key to your main table's PK. This would help with keeping an easy normal form: https://en.wikipedia.org/wiki/Database_normalization . I hope this helps.

Edited by Member #1111627 because: Procedure name change.
commented: I think that's what I'm going to do. Thanks! +6
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.