Design Question - What's in a Column Name

Question

Post reply

Design Question - What's in a Column Name

souLTower

SSCarpal Tunnel

Points: 4026
More actions
March 6, 2008 at 10:35 am

#69981

An application has a table which tracks the number of months a vehicle is expected to be in service under a given contract:
TABLE vehicleServiceContract
(
numMonths INT NOT NULL,
vehID varchar(12) NOT NULL, -- FK to vehicle table
contractNum varchar(20) NOT NULL -- FK to contract table
)
The system has been in place for a number of years. The client wants to add greater granularity to the table, storing the number of days a vehicle is expected to be in service.
The question is which is the better design idea:
1) Re-use the column
2) Add a new column
3) Other
How would the pros do it?
Thanks
ST

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 1

Steve Jones - SSC Editor

SSC Guru

Points: 736160

March 6, 2008 at 11:05 am

#786221

I'd add a new column.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

I'd also consider making on a derived column of the other. meaning - days as a factor of months in service, or vice versa.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

TraderSam SSCrazy Points: 2931 More actions · Answer 3

Going back to the title of your post, 'What's in a column name'...

Since the column name is numMonths, DO NOT reuse the column to store the data as days. This will only lead to confusion for anyone else that ever has to query the table in the future.

If it was easy, everybody would be doing it!;)

souLTower SSCarpal Tunnel Points: 4026 More actions · Answer 4

This was a "round the water cooler" discussion. I appreciate your posts and agree.

Thanks

dedybear-668797 SSC Enthusiast Points: 155 More actions · Answer 5

I agree with the above, I would add a new column for this request. But everytime I get a request like this, I am plagued with "foresight". Seeing as how they are asking me to track the number of days for this task, are they going to ask me to start tracking the individual sections of this task and how long each of these take? Should I build an new table to relate back to this one identifying that this task will take 3 days but the other 3 required tasks could push this up to eight?

So a questions about your work vs your sql skills, is it properly to look this far into a possible future and just add it in?

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

Probably the most important thing to do would be to ask exactly what they are trying to do, what information they need, and why they think adding this column will help.

You may find that what they are asking for is a completely inappropriate solution to the problem they are trying to solve. For example, maybe all they need is a simple way to convert months in service to number of days in service, and you could supply that in a view.