Notes

Question

Post reply

Notes

JKSQL

SSCrazy Eights

Points: 8481
More actions
November 5, 2008 at 9:39 am

#193243

I have a table that needs a notes field. This is in sql 2005. It will be nullable The primary key is a guid. The notes field is going to be a nvarchar(max). Should I make this a separate table or keep it within the current table. Are there storage costs either way?

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Joel Ewald SSCertifiable Points: 6120 More actions · Answer 1

I would make it a seperate table for when they decide they want multiple notes.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Here it comes....

It Depends.

There, said it. It depends on how often the field is going to referenced in queries for one. Second, how big is the table and how many rows will have Notes entered. You can have the column data stored in a separate file group and still be part of the record.

If the majority of queries will never access this column, then a separate table that needs to be joined (left outer join) only on those relatively few queries that need the data may make sense. If the column is accessed on a regular basis, then you'll probably want it as a part of the row so you don't have to join the separate table every time.

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 3

Hmmm...

I think I am going to do a separate table. I do not feel this field is going to be selected often and I just got further in the design and there are two other tables that need a notes field. So I will do a separate table with an ownerid and un-enforce the FK. Do you think that would be a good solution?

Joel Ewald SSCertifiable Points: 6120 More actions · Answer 4

Without knowing the design I am just giving my rule of thumb, which would to be create tables such as the ones below.

SourceA --> SourceANotesLink --

SourceB --> SourceBNotesLink --> Notes

SourceC --> SourceCNotesLink --/

Lowell SSC Guru Points: 323518 More actions · Answer 5

if you decinde you need separate tables for notes, then

if you have three tables that need notes, I'd really recommend three notes tables, all with enforced FK's instead; it saves a lot of headaches, avoids that whole special coding to figure out which table the note belongs to.

there's no real storage cost for three tables vs one table, but lots of advantages.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

I have to agree with Lowell. Each table should have its own "Notes" table with approprate FK relationships defined.

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 7

So if I define a table with a guid and a nvarchar(max) there is no space difference between one table and 3? That does not make sense to me. Does the table container not cost the DB anything? Is it just the records that cost disk space? If so is there an article discussing that?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Let us approach it from a different direction. Three tables Customer, Vendor, Employee. Each table has a related Notes Table, CustomerNotes, VendorNotes, EmployeeNotes. Do you REALLY want to intermingle these different NOTES tables in a single NOTES table? If you do, you have unrelated data in the same table. Not a good design.

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 9

It is a notes table so I am not sure unrelated is valid. So for your example If you called the table Customer notes and had contacts, customers, and email I would think that would be grouped and effective. There would be less space used, The owner id would be unique, and on the code side it would be easier to handle with only one SP to have in the DAL. I understand the point of a db design and clarity. In this case I feel this is clear with showing the un-enforced FK. I appreciate the help on this so don't take this the wrong way. This has been a question I have had for a long time. There are several different ways to approach this. The program I am writing the db design for has the possibility to go on an express machine so space is a heavy consideration.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Using my example tables, do you really want notes for records from on Employees, Vendors, And Customers in the same table? From a design point of view, it isn't right. Each should have its own, separate Notes table.

No offense taken, it is your database, and your design. You have to make the decisions you feel is approriate.

Lowell SSC Guru Points: 323518 More actions · Answer 11

less space? even the smallest servers today have at least half a terabyte or so of space if not more, and you are worrying about a miniscule amount of space, at the risk of being unable to keep your data in an enforced relationship.

one table costs 1 row in sys.objects.

each column costs 1 row per field in sys.columns.

for a 3 column notes table, with a PK,FK, and varchar max field, it will cost you around 12 KBYTES of data, plus a single page in memory.so 20K of dist space for a simple table.

proof is here: this is the aboslute largest amount of memory a new row can take, and is typically less than half of the defined size of the data iteself:

Select 'sysobjects' As TableName, SUM(C.Length) as Length from dbo.SysColumns C where C.id = object_id('sysobjects')

--my server reeturns 342 bytes

Select 'syscolumns' As TableName, SUM(C.Length) as Length from dbo.SysColumns C where C.id = object_id('syscolumns')

--myserver returns 8836 bytes

so for 3 tables, you are talking a whopping 36 extra kbytes of space, plus 8K*3 for the pages in memory., and as you add rows, they will take up the same amount of space of the data itself whether in one table or distributed into 3 tables...no problem.

best practice is ALL data is ALWAYS in an enforced relationship...otherwise you will have trouble in the future.

See this thread, where some vendor decided not to use foreign keys in order to somehow keep their "business methods" hidden.... and now the new guy is trying to reverse engineer the relationships because the data is doinked:

http://www.sqlservercentral.com/Forums/FindPost597272.aspx

trying to conserve memory should never be an reason to avoid best practices.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 12

Now I am tossed up. I agree on the biggest point of the enforced FK. I hate un-enforcing FK's. I will do it the right way.....Thanks

Jeff Moden SSC Guru Points: 1004299 More actions · Answer 13

I just wouldn't bother with all of that... we're not talking about in row TEXT columns. We're talking about NVARCHAR(MAX). Just add the Notes column to each table... the table will actually store a pointer and put the data in a pseudo table, anyway. Then, no worry about table matching, FK's, etc, etc...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004299 More actions · Answer 14

AND, if you do happen to need or want to plan on more than 2 billion characters of notes for each row, then I agree with some of the others... one "sister" table per table... NOT one common note table for all tables.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)