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.
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.
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.
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.
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
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
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.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.