ansaurus

Question

MSSQL2008: Bit arrays and bit-wise operations + database Brainstorm

Answer 1

+2 A:

Yes, you are over-engineering.

Simply create a single datetime column, and index it. If you want to split up a datetime, use the built in functions like datepart().

"How can the client application RETRIEVE say, all the notes for a given set of dates?"

By executing a query with a WHERE clause BETWEEN the start and end dates.

Declare the 'Note' field as varchar(n) [n up to 8000 approx.). It only uses the space taken up by the note not the maximum defined size.

Mitch Wheat 2009-09-21 05:13:35

I understand, I also considered the fact that the note system is going to get really big, with sometimes notes applying to over 60 days. What you saying is have 60 records with the same 255-byte note.

lb 2009-09-21 05:15:56

@Leo, no you won't want to repeat the same note for each date. You will have another table that stores a datetime stamp along with a foreign key to the note table. That way you can find all dates that relate to a note, and all notes that relate to a date. There will be an entry this table for each note for each date.

Chris Porter 2009-09-21 05:28:07

Will a date time stamp be able to specify a multiple set of dates, say 4th, 6th .. etc. (not a range)My sincere apologies for being so critical.

lb 2009-09-21 05:30:31

@Leo: A DateTime column only stores **one** Date/Time value per row. See my comment about using effective/expiry datetime columns to manage notes covering more than one date.

OMG Ponies 2009-09-21 05:32:28

Answer 2

+1 A:

If the application will need to pick a date, and retrieve all the notes related to that date (exclusively or partially), then you need to store the date using the DateTime datatype.

If the business note for a note is such that it can span days, your NOTES table needs EFFECTIVE_DATE and EXPIRY_DATE DateTime columns. The dates could be the same day, or a couple apart.

If you want the note text/body to apply to periodic days (IE Jan 1st, 13th, 21st), then you'd need two tables: NOTES, and NOTE_ACTIVITY. NOTE_ACTIVITY would contain the note_id, along with effective and expiry dates. I just don't see how you could support sporadic dates associated to a single body of text any other way in a database.

OMG Ponies 2009-09-21 05:18:02

I basically need some notes saying "In June blah blah." that note will apply to each day of June. If in the client application they do a look up for the 4th of June, that note will come up, as well as another note that applies to say, the 4th and 6th, or another one for the 4th only.

lb 2009-09-21 05:23:18

Rexem, my apologies for being over critical. I understand that your method will work in case of a range? What about a date here and there?Thanks - I'm probably causing anger, but this has caused me many headaches because I'm too overcritical too!

lb 2009-09-21 05:32:52

Answer 3

+1 A:

I would personally split this to two tables

NOTE
int Id
varchar NoteText

NOTEACTIVITY
int Id
DateTime ActivityDate
int NoteId

This keeps your queries simple and allows for plenty of flexibility in the future. It will mean that if a note applies to three dates you have one entry in the Note table, and three entries in the NoteActivity table. In some ways you would be likely to run into this issue with your existing design. If a client wanted a note to apply to the first of every month you would be duplicating the row (including note) 12 times?

Alternatively, if you've already decided that your note bitmap idea is the correct way, then consider storing the day numbers instead of a bitmap, so you could store '[1][5][30]'. This may make your queries easier to run, by searching for rows that contain '[5]' in that column for example

PaulG 2009-09-21 07:14:05

ansaurus

tags:

views:

answers:

MSSQL2008: Bit arrays and bit-wise operations + database Brainstorm

related questions