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?

Re: Should I add fields to an existing table or use a lookup table? 80 80
Member Avatar

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.

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 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.