Re: Must we also create separate tables?

From: paul c <toledobythesea_at_oohay.ac>
Date: Mon, 27 Oct 2008 16:05:14 GMT
Message-ID: <_ElNk.4298$%%2.1321_at_edtnps82>


Srubys_at_gmail.com wrote:
...
>
> Say we have non-normalized table STUDENT:
>
> STUDENT ( STUDENT#, STUDENT_NAME, SUBJECT1#, SUBJECT2#, SUBJECT1_NAME,
> SUBJECT2_NAME, TEACHER1_NAME, TEACHER2_NAME )
>
>
> I realize best thing to do would be to create two tables:
>
> STUDENT-SUBJECT ( STUDENT#, SUBJECT#, SUBJECT_NAME, TEACHER_NAME )
>
> STUDENT ( STUDENT#, STUDENT_NAME)
>
...

Heh, just to muddy the waters a little or even a lot, the conventional normalization discipline is really just a way to help determine a simple structure that avoids a lot of constraint verbiage. From what I gather of the typical SQL product it is effectively a sop that allows them to cop-out and not give very full constraint support.

Suppose that SUBJECT# determines SUBJECT_NAME. Then if you wanted to store the version of STUDENT-SUBJECT above, you'd want to enforce that constraint. If I had a dbms that let me state that constraint, there might be performance reasons for doing it. (I mean logical performance, trading off more logical "update" work for less logical query work.) But I gather that most dbms'es don't make that easy to do. Further, I might even want that stored "table" to be a view, which I gather they don't allow at all. What's more, I might want one of the base tables of that view to be SUBJECT (SUBJECT#, SUBJECT_NAME) but not to store it!

As CJ Date says, normalization doesn't tell you what's right, it only gives hints as to what might be wrong. A lot depends on purpose as well as the "business rules". Received on Mon Oct 27 2008 - 17:05:14 CET

Original text of this message