db design

Last Post 02 Aug 2010 05:41 AM by dbandee. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
dbandee
New Member
New Member

--
30 Jul 2010 10:11 AM
we are going to redesign one of our systems
one of the issues in old system it does not allow comments on major business objects like client, studio, order, contract

what is the best way to design it in new system, sql2K5?

add column to a table with max or ntext? this somehow does not sound right to me

what is your opinion?

Thank
russellb
New Member
New Member

--
01 Aug 2010 04:53 PM

I'd have to say that we don't have enough information, but here's what I'll consider as for adding comments to records.

Is it possible, or likely, that you'll eventually want more than one comment per record? If so, you want a new table with a foreign key to the "master" table.  That way multiple comments can be attached to the same record.

As an example, we have a CustomerNotes table. And there are different note types. Different types identify if the note should show up in the Point of Sale system, the CRM, etc. Also the note type can determine urgency.

So we have three tables (in this example).

Customers (CustomerID int, ....)

NoteType (NoteTypeID int,  ...)

CustomerNotes (CustomerID FK, NoteTypeID FK, Note Varchar(255)...)

All that said, the length of the note depends on the business requirement. Don't try to decide for yourself. Talk to the business users who will be entering the notes. Figure out what they require. If you can get by with less than Varchar(MAX) do it. If you can't make it MAX.

Also, If it is guaranteed that it will be no more than one note per record, go ahead and add a column and make it nullable.

Hope this helps a bit.

dbandee
New Member
New Member

--
02 Aug 2010 05:41 AM
very helpful
thank you
You are not authorized to post a reply.

Acceptable Use Policy