Large Table Design Best Practices

Question

Large Table Design Best Practices

Viewing 2 posts - 46 through 46 (of 46 total)

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 1

tnpich (3/6/2016)
To Hugo’s comment “I agree that having an explicit column to show the step (either as a number or as a code) is probably a much better idea.” I’m concerned that this would require additional logic to create a (somewhat automated) workflow. Under ideal situations this would work, but are some time/cost constraints.

Just to clarify - IF you need/want to enforce that attributes that are required after stage 3 are not left out, or that attributes that should not be entered before stage 5 are entered too early, then having a column that explicitly holds the state makes all those checks a lot easier to code. Alternatively, using sub-tables for each stage can have the same benefit without having to add this column.

If you do not want to (or cannot for lack of time to analyze them) enfore all such constraints, then such a column would probably be less useful.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 2

E.g. run a query to get the average length of comments provided. LEN('') is 0 which reduces the average length; LEN(NULL) is NULL and is not included in the average. My interpretation of '' is that someone deliberately provided a zero-length comment, so this should reduce the average length; NULL values are not comments and should not be included when computing the average length of provided comments. Your interpretation of '' as "there is no comment" and NULL as "there is a comment but I don't know what it is" will give incorrect results, since all the rows where the comment is not applicable will be counted as zero and the average will be way too low.

This is an interesting comment. I remember on another thread debating whether NULLs should be allowed and used this AVG calculation example as one reason they should. In the case of my comment field, there is no need to ever make such a calculation as it would have no business value. But it's something of which to be conscious if such a calculation would have a business value.