June 21, 2010 at 9:16 am
I have two tables, one that stores detail data and the other that stores notes associated to the data. The syntax below is a very simplified example:
CREATE TABLE Notes
(
idUNIQUEIDENTIFIER,
note_orderINT,
note_textVARCHAR(255)
)
CREATE TABLE Data
(
idUNIQUEIDENTIFIER,
note_idUNIQUEIDENTIFIER
)
A note can exceed the 255 characters specified by the column in the Notes table so to cater for this I intend to split a note up so that it can be stored across more than one note entry. For example if the character count is 355 I will have one entry in the Notes table holding the first 255 characters and the remaining characters will be held in a second entry in the table. The id field will group together entries that combined form one note with the note_order field determining the order of the notes.
What I want to do is make the id and note_order fields in the Notes table unique but to reset the note_order for each new note. In other words if a single note spans 3 entries then we should have something like:
UID Note Order
A 1
A 2
A 3
The combination of the id and the order will be unique, so when a new note is added that only spans one entry we will have the following:
UID Note Order
A 1
A 2
A 3
B 1
The Note Order is reset. How can I do this or can you suggest an alternaive method of achieving what I need?
Thanks
June 21, 2010 at 9:49 am
Why do you need to limit the note to 255? SQL can obvioulsy handle much more than that.
Is that some type of output restriction?
If so, I would still store it as one column in SQL and split it for output rather than splitting it in SQL itself.
Scott Pletcher, SQL Server MVP 2008-2010
June 21, 2010 at 10:59 am
I tend to agree with Scott in terms of designing this differently. If you are trying to keep the table size small, you can put the entire note in the second table and vertically partition this table. Not sure what the point is in keeping 255 characters only.
A unique index can be set on multiple fields to enforce uniqueness, but please revisit your design.
June 21, 2010 at 1:15 pm
Ok, thanks for the advice, I'm going to try and store the note in a varchar(max) field and see how I get on with that.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply