SQL Schema Change Suggestion?

[zachattack05]

Good morning strangers! I haven't posted in a while, but you always seem to be helpful...

I have an application that uses SQL as a back-end to store data so that multiple users can access it at once. The question I have is, the requirements have changed and the SQL schema needs to change, and I'm not sure what the best way to do this is.

I have a table that contains a "log" of sorts. Basically it allows us to keep track of tasks and indicate (via a boolean value) if the task was completed. So when the task is done, the user checks a checkbox. Now we need to have the user indicate the date that the task was completed, not just check a box.

Since we have nearly 75,000 records in this table already, and we need to maintain the old information, would it be best to:

• Add new columns to the existing table, and just not use the boolean fields for new records?
• Create a brand new table that mimics the old one and just use date fields instead of the boolean ones and keep the old table for reference?

I've never actually had to change something like this before, normally it was "we don't need this column anymore and it's safe to just drop it and all the data in it."

Any suggestions?

[Stuugie]

My opinion would be to add a new column to the existing table. If people are already used to working with that table, it might be easier to have them change how they work with that table rather than teach them to work with an all new table.

My 2 little pennies.

[ss125]

Well said @Stuugie..

Just to add, you can also use a new table. The only rule to follow, while designing the structure of the table, is to assure that the problem which you are currently facing, will not occur on the future. If you are a well planner, you can go for new table. else stick with the first option.

Thanks...

[Ketsuekiame]

Personally I would create a new table and link them.

So if you have a worker and a task you could create a table that references the workers table and the tasks table and contains a completion flag.

Something like

create table MySchema.TaskState
(
    TaskStateId smallint not null,
    StateDescription nvarchar(50) not null,

    constraint PrimaryKey_MySchema_TaskState_TaskStateId primary key clustered ([TaskStateId])
);

create table MySchema.WorkerTaskState
(
    WorkerTaskStateId bigint identity(1, 1) not null,
    WorkerId int not null,
    TaskId int not null,
    TaskStateId smallint not null,

    constraint PrimaryKey_MySchema_WorkerTaskState_WorkerTaskStateId primary key clustered ([WorkerTaskStateId]),
    constraint ForeignKey_MySchema_WorkerId foreign key ([WorkerId]) references MySchema.Worker ([WorkerId]),
    constraint ForeignKey_MySchema_TaskId foreign key ([TaskId]) references MySchema.Task ([TaskId]),
    constraint ForeignKey_MySchema_TaskStateId foreign key ([TaskStateId])
);

You could in this case now define a TaskStatus, so: New, Active, Delayed, On Hold, Completed etc.
You could then create a View which linked the tables together, or, simply link them in your Stored Procedure/Select statement using an outer join.

This gives you the ability to support both new and old records without really changing the design of your existing tables.

[zachattack05]

Thanks for the replies!

It's been busy and I haven't had a chance to reply. I think I'm going to just add columns to the table and after many years the old data in the current columns will no longer be kept and I can drop those columns.

At least that's the plan for now. Hopefully it works!