Best Practices for Column Names 1

[schafjoe]

Everybody,

Is there a Best Practices for Year, Month and Week Column Names?

What I mean is that is it good to have a Column named Year or is their something better.

 

[JohnDTampaBay]

IMHO, I would avoid using any sort of reserved word; maybe use Yr, Mnth, Wk. Better yet, you can remove all ambiguity by storing a datetime value and deriving the year, month, and week from it using SQL functions.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[SQLSister]

Agreed that I would avoid using Year, month or week as these are reserved words. Usually if I have a field that involves a reserved word I use a modifier as part of the field name to further define it, things like StartMonth, EndMonth.

 

[SQLSister]

Angel, I meant to address the using functions issue in the above post. Yes the functions are there and available to use. However if you have a really large database, it is sometimes better to extract what you need at the time the record is inserted or updated to speed up the select process later. So using the datepart funtion to find the month for a table with 50,000 records might not slow things up too much, but it would for a table with 20,000,000 records, particularly if you are using the amount to filter the records and thus the calculation will have to occur for every record not just the ones you filtered out.

 

[JohnDTampaBay]

True. In this scenario, I might create calculated columns to hold each of the pieces, then index those columns. This would still only require one unambiguous datetime column, but (hopefully) address the performance concerns. Or perhaps horizontally partition the table on the datetime column to mitigate the performance concerns. As always, the real answer lies in how the data will be used most frequently.

Point well made, Sister.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[SQLSister]

Yep that's the reason why there is more than one way to do things.

 

[r937]

aw darn, you guys went straight to the important issue, whether to store a datetime value or separate values for the components of the date

i was hoping that some of the einsteins who like to name their tables as tblThis and tblThat would suggest that the best practice for column names would be colYr and colMth and colDay



rudy

http://r937.com/

SQL Consulting

 

[SemperFiDownUnda]

Personally I'm not a fan of prefixing my table names with TBL and columns with col.

I agree I never use keywords and never use spaces in names. My column names are always ProperCase and I don't us _.

I agree with SQLSister, as usual, and say that your column names should be more descriptive then Year. What type of Year is this? BirthYear, BirthDate, PurchaseYear, etc.

On breaking out the data I'm not big on derived information having its own column unless there is a performance issue to be addressed. At that point I'd break out the data, make a trigger that fills in that data on insert/update and only apply the new logic to the procedures that need to to have their performance increased.

Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[schafjoe]

Thanks Everybody for you suggestions.

The fields are for a summarized table that is broken out by the part number, year, month, week and quantity that was shipped.

For Example,
Part Number Year Month Week Quantity
Part A 2003 9 40 100
Part A 2003 10 40 77
Part B 2003 10 41 22

 

[Lekar]

I agree SemperFiDownUnda. The conventions he suggests are the best for me. In ur case, you would rename your cols:

ShipYear, ShipMonth, ShipWeek.

 

[ks1392]

Ahh, glad I caught this thread.

I have question along the lines of breaking a datetime field down into Day/Month/Year. I do this in a number of views, but I am wondering if I can CAST/CONVERT some date functions to create a datetime field in a view with the time as 00:00:00.000.

I've not had any luck with this in the past and was wondering if anyone else has had success with it.

Thanks,
Kerr

 

[r937]

cast(convert(char(10),datefield,120) as datetime)

rudy

http://r937.com/

SQL Consulting

 

[ks1392]

r937, Thanks -- I've been half-heartedly trying to do this for awhile.

Question -- I understand all but the datefield,120 portion -- specifically the 120.

 

[r937]

see CAST and CONVERT

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

style 120 = ISO Standard format, yyyy-mm-dd

rudy

http://r937.com/

SQL Consulting

 

[ks1392]

Ahh, style -- thanks very much for your help!

 

[SemperFiDownUnda]

Don you want type 14 for the time portion?

Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[r937]

no, no time portion required

when you cast a yyyy-mm-dd string without a time portion into a datetime, it gets midnight by default

that was the requirement: "to create a datetime field ... with the time as 00:00:00.000"

stripping off the time is done by converting the datetime to a char(10) field of style 120

then cast it basck to datetime


rudy

http://r937.com/

SQL Consulting