hi all i am trying to find the best answer for a problem i ve faced. in my db i have a table called AcademicYear which has a name, start and end dates and a iscurrent flag as fields.

i am trying to find a way to best set the iscurrent flag. users can create academic years and i dont want them to set this flag at entity creation time. i have two solutions so far: 1) check the start/end dates with the current day in every login and set the matching entity's iscurrent flag to true. 2) create a ms sql job that will check and set this for me (maybe once a year or everyday, i dont know yet).

they both doesnt sound great to me. any more ideas?

thanks -shane

Should isCurrent = true if the current date falls in between the start and end date values?

If so, I see no reason why you need the flag at all (at least from the database perspective)--you can already discern which year is the current year via the start and end values.

Phil

that is correct but making this check every time i need it (which is a lot) seemed costly compared to checking a the value of a bit.

dont you think?

-shane

I can't answer with 100% certainty without knowing very much about your system, but in general I would say no, it's not costly.

It's easy to check--run the query you would be using to find the current year using the date columns, then run it again using the isCurrent column and see how long each one takes to run. It might be a good idea to apply a proper index to the table for each query before you do such a test.

Your original question/problem is a perfect illustration of one of the difficulties you will see with denormalizing your data without really needing to.

HTH,

Phil

the table has an index actually, i never mentioned it. there is academicyearid as my primary key.

just out of curiosity: what do you mean here "Your original question/problem is a perfect illustration of one of the difficulties you will see with denormalizing your data without really needing to."

thanks for the help. i think i kind of know what to do now.

-shane

psandler wrote:

I can't answer with 100% certainty without knowing very much about your system, but in general I would say no, it's not costly.

It's easy to check--run the query you would be using to find the current year using the date columns, then run it again using the isCurrent column and see how long each one takes to run. It might be a good idea to apply a proper index to the table for each query before you do such a test.

Your original question/problem is a perfect illustration of one of the difficulties you will see with denormalizing your data without really needing to.

HTH,

Phil

e106199 wrote:

the table has an index actually, i never mentioned it. there is academicyearid as my primary key.

If you are going to be hitting this table a lot, and you are worried about performance, it should also be indexed by the date columns or the isCurrent column (depending on which way you decide to go). Pragamatically speaking, if the table is small you may not need such indexes, but as a best practice you should have indexes that reflect the way you access the data (in addition to the primary key).

e106199 wrote:

just out of curiosity: what do you mean here "Your original question/problem is a perfect illustration of one of the difficulties you will see with denormalizing your data without really needing to."

I mean, this is one of the main problems with denormalized data--ensuring that it is always up to date, and that the updates are managed in such a way that the data can never be wrong when it's needed.

Phil

thank you, it will be a pretty small table.

i think i ll find the current academic year during login event and keep it in users session. that seems to be the best solution and i ll get rid of the isCurrent field.

thanks for all the answers. -shane

psandler wrote:

e106199 wrote:

the table has an index actually, i never mentioned it. there is academicyearid as my primary key.

If you are going to be hitting this table a lot, and you are worried about performance, it should also be indexed by the date columns or the isCurrent column (depending on which way you decide to go). Pragamatically speaking, if the table is small you may not need such indexes, but as a best practice you should have indexes that reflect the way you access the data (in addition to the primary key).

e106199 wrote:

just out of curiosity: what do you mean here "Your original question/problem is a perfect illustration of one of the difficulties you will see with denormalizing your data without really needing to."

I mean, this is one of the main problems with denormalized data--ensuring that it is always up to date, and that the updates are managed in such a way that the data can never be wrong when it's needed.

Phil