CODEWITHSUNDEEP
sql-serverdatabase-designdata-modelingbitwise-operators
lb.
lb.

Reputation: 5776

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

I have a coding problem where I have to store a 'note' and the dates the note applies to. Think of:

Note 1 Hi, today Mr Client rang arranging these appointments. 30/8/2009, 31/8/2009, 5/9/2009

Note 2 Business as usual. 30/8/2009

Note 3 Restaurant is shut. 6/9/2009

I need to store the following data in a database, while maintaining indexes for efficient retrieval, that is, my client application will need to pick a date, and retrieve all the notes related to that date, exclusively or partially.

I've discussed with a few colleagues and friends, and have come down to this design. Please note I want to be able to store all the information in one table, or more if the design is elegant.

> Date Bitmap | Month | Year | Note
> 101..         9       2009   Blah Blah     // applies to 1st and 3rd
> 0001...       10      2009   Blah2         // applies to the 4th
> 100           9       2009   Blah23

When the user selects from a multi date picker the following date: 1st of September He will get Blah Blah and Blah23. A single date time object will repeat the note, or force the creation of another table with a foreign key.

In the sense that I could store in the first column what days of the month the note applies to. In a bitmap, things could be super-efficient. Any other ways (linked tables with Note-ID, or a table with all the days with IDs) have all resulted in ugly duplication of either the note field, or repeated dates. I also don't want to think about having a Text field with sets of dates with separators, and ugly code that will parse it upon search.

I have the luxury of time on this project as you can tell.

But how can the client application RETRIEVE say, all the notes for a given set of dates? Is there any bit operations in MSSQL? So I can retrieve all the rows, with say, a '1' in the 5th and 7th bit in that binary field?

I can't use BETWEEN as I might have sporadic dates that are not in a range or anything or the sort. Think of having a datatype where it could represent any number from 1 to 31. That's how I thought of a bitmap.

Let me also say that we could add an extra text field in which the real dates (in real text) will be populated by a routine. But for the rest, this application will be the main interface and rarely users will have to look at the ugly table.

Am I over-engineering, or can you help me?

Thanks for all your replies.

Leo

Upvotes: 0

Views: 541

Answers (3)

PaulG
PaulG

Reputation: 14021

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

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332591

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.

Upvotes: 1

Mitch Wheat
Mitch Wheat

Reputation: 300559

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.

Upvotes: 2

Related Questions