August 26, 2010 at 7:26 am
Hi,
I was thinking about placing several VarChar fileds in a single table called tblContent. It would something like this...
ContentTitle varchar(128)
ContentTags varchar(128)
ContentShort1 varchar(196)
ContentShort2 varchar(196)
ContentShort3 varchar(196)
ContentShort4 varchar(196)
ContentBrief1 varchar(512)
ContentBrief2 varchar(512)
ContentBrief3 varchar(512)
ContentBrief4 varchar(512)
ContentLong1 varchar(8000)
ContentLong2 varchar(8000)
ContentLong3 varchar(8000)
ContentLong4 varchar(8000)
ContentMax varchar(MAX)
It will basically be a content block where the content is arbitrary. It could be that ContentShort1 might hold the HTML for a link, or it might hold the text to place in the footer of the block depending on how the block is presented. So far the expanation is a bit vague but bare with me.
All the fields could all be populated on any record, however only a couple of fields might be populated on other records. Is this generally a bad or ok practice? Is it a waste of paging space? Is this design too inefficient? I feel like it could be an issue but I would rather ask about this rather than rely on the hairs on the back of my neck standing up.
Your discussion is greatly appreciated with this.
August 26, 2010 at 7:37 am
I think you should first go through the NORMALIZATION concepts.
http://support.microsoft.com/kb/283878
Abhijit - http://abhijitmore.wordpress.com
August 27, 2010 at 12:09 pm
mike-939611 (8/26/2010)
ContentTitle varchar(128)ContentTags varchar(128)
ContentShort1 varchar(196)
ContentShort2 varchar(196)
ContentShort3 varchar(196)
ContentShort4 varchar(196)
ContentBrief1 varchar(512)
ContentBrief2 varchar(512)
ContentBrief3 varchar(512)
ContentBrief4 varchar(512)
ContentLong1 varchar(8000)
ContentLong2 varchar(8000)
ContentLong3 varchar(8000)
ContentLong4 varchar(8000)
ContentMax varchar(MAX)
Not having a detailed explanation of the rationale behind such a design, it is real difficult to find a good reason for it.
mike-939611 (8/26/2010)
Is this generally a bad or ok practice?
In general... very bad practice.
mike-939611 (8/26/2010)
Is it a waste of paging space?
Assuming all columns are nullable... No.
mike-939611 (8/26/2010)
Is this design too inefficient?
In general... yes.
Having said that, you should explain why such level of de-normalization, in general this should be in the 3NF - provided there is not a good reason to do it different.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 30, 2010 at 3:36 pm
Thanks for your reply.
To clarify further, in a cms we have wysiwyg editors which is fantastic stuff. However, it is unfortunate that clients expect to be able to magically add text via the cms wysiwyg editor without knowing anything (for example, some clients can't even float an image to the right so text wraps around it).
So, I thought if I had some arbitrary content placeholders (i.e. fields in a table as described), they could just edit those fileds and depending on which layout they choose, the cms would automatically create that layout for them.
Of course there are an infinite amount of layouts and creating a table structure for each layout would be a bit over the top. As content is basically text of different lengths then arbitrary varchar fields could be one answer (maybe). However, all the fields might get used on any one record, and only one field might get used on another. So, it 'feels' kind of wasteful to do it this way.
I hope this clarifies this a little more and any further thoughts would be greatly appreciated.
August 31, 2010 at 4:57 am
One problem you might have is that the total possible row length you've got there is well in excess of 32k--way higher than the little-bit-less-than-8k SQL server provides! If you try to populate all those fields at maximum length your INSERT will fail.
August 31, 2010 at 5:12 am
paul.knibbs (8/31/2010)
One problem you might have is that the total possible row length you've got there is well in excess of 32k--way higher than the little-bit-less-than-8k SQL server provides! If you try to populate all those fields at maximum length your INSERT will fail.
Since they are variable-length columns, inserts won't fail, the excess data will be pushed off page. Row Overflow. Will do terrible things for performance.
Still doesn't make this a good idea.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 31, 2010 at 8:50 am
I get the feeling I am trying to get something to work in a certain way and that way isn't the way the the database is mean't to work; much like trying to put a square peg in a round hole.
What I am really trying to do is put a square client into using a round wysiwyg editor.
Unless anyone has anything else to add, I shall concede that what I am trying to achieve cannot be done effectively.
Please free to continue the discussion if you have any other thoughts.
Thanks for your time guys.
August 31, 2010 at 8:53 am
GilaMonster (8/31/2010)
Since they are variable-length columns, inserts won't fail, the excess data will be pushed off page. Row Overflow. Will do terrible things for performance.
Doesn't that depend on the SQL server version? Pretty sure it didn't do that in SQL 2000, not sure about 2005...
August 31, 2010 at 8:53 am
mike-939611 (8/31/2010)
I get the feeling I am trying to get something to work in a certain way and that way isn't the way the the database is mean't to work; much like trying to put a square peg in a round hole.
You got it right, the poor square pig will be really uncomfortable in the round hole.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 31, 2010 at 8:58 am
paul.knibbs (8/31/2010)
GilaMonster (8/31/2010)
Since they are variable-length columns, inserts won't fail, the excess data will be pushed off page. Row Overflow. Will do terrible things for performance.
Doesn't that depend on the SQL server version? Pretty sure it didn't do that in SQL 2000, not sure about 2005...
Yes. It's available from 2005 onwards. Since this is a 2005 forum and the OP is using SQL 2005 or higher he'll hit that and not 'row too large' errors.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply