Re: "advanced" database design (long)

SunWuKung wrote:
> I always thought that having nullable columns in a table is a Bad
> Thing (http://technet.microsoft.com/en-us/library/ms191178.aspx) and

Ridiculous.  The argument provided in that article is specious and likely SQL
Server-specific.  NULLable columns should occur wherever your data model calls
for them, typically when you want to have a marker for "unknown" data.  The
advice in that article to move NULLable columns off to a separate table will
actually cause worse, manual "special handling that increases the complexity
of data operations" than the built-in and optimized handling the engine
provides for NULLs.  You should ignore this terrible advice.

> shows that you try to put different type of entities into the same
> table - having 90 in a column ... brrrrr.

Is that a technical evaluation?  As another respondent stated upthread, 90
NULLable columns is possibly a sign of a bad data model.

> I think its much better to avoid it whenever you have the info but
> when you don't you just have to use the EAV model.

Also ridiculous.  You should never "have to use" the EAV so-called "model".

> E.g. If I knew what info I wanted to store on a person I could create
> columns for that, but since in our application users create the
> questionnaires that is used to store info on persons I see little
> choice - I must have a subjectID, questionID, value table.

That's not EAV.  When you're modeling a questionnaire, "subject", "question"
and "answer" (as I interpret your meaning for "value" here) is natural.  EAV
would have a row with "question" as a value in a column, not the name of a
column as you suggest.

It's very hard to actually think in EAV.  The mind naturally thinks of things
like "question" being a column, but in EAV that wouldn't be; "question" would
be a value of a generic column in some row that represents a fragment of the
question being described.  The difficulty of conceptualizing data structures
as EAV is one of the big strikes against it.  The quoted citation evidences
that difficulty quite well - even trying to come up with an example of an EAV
structure wound up with a non-EAV description.

--
Lew