Re: one big table or many smaller tables

On Fri, 19 Jul 2002 11:48:13 +0200, GoranG <no_at_spam.net> wrote:
>On Tue, 16 Jul 2002 12:15:26 -0700, "news0.alterdial.uu.net"
><DCraig_at_abc-clio.com> wrote:
>
>Which can be done on the 220 tables with UNION as well.
>
>SELECT 'students notes' AS notes_origin, note_text FROM students_notes
>WHERE notes.students = current_student_id
>UNION
>SELECT 'classes notes' AS notes_origin, note_text FROM classes_notes,
>classes_students
>WITH classes_students.student_id = current_student_id
>AND classes_notes.classes_id = classes_students.classes_id
>

That's exactly what I was thinking yesterday after going over it again, this reinforces that it's the right track.

>Please note that with some naming conventions the complete SQL string
>can be built, given only the 2 tables' names 'students' and 'classes'
>and of course, the current_student_id.
>
>I think it is easier to keep the ref control this way... and the data
>model is slightly more obvious, at the expense of the size of it :)
>
>This way model also allows greater flexibility in design... some notes
>might have additional properties.
>
A good point, we have one group of notes that's separated because of differing attributes.

>Still I think that decission should be based on the question: are
>notes related in meaning or not, do they refer to the same area of
>interest and comment on same entities.

That's how I got here in the first place, it really all started with having notes for many entities who were all tied together such that a note for any one in the group is likely to be relevant information for the other entities, like student/class/teacher. Because we have a dozen or so notes which will typically be pulled together by groups of 3 or so entities and they all have the same structure it seemed to make sense to just put them in one table.   But now I'm convinced Union and multiple notes tables is a better (more flexible and less complex in the long run) approach.

I suppose we could identify the groups of entities and assign notes to them but the group boundaries are fuzzy, in that the notes we would be interested in could be for any single entity, one of the groups - or a subset or a superset.

>If they do then you might expect specification request for searches
>through all notes for some text... (student name, etc..) and that
>would (i think) make one table approach more interesting (or would
>lead to some sort of denormalization to approach acceptable
>performance).
>If only pairs or small groups of notes entities are related then
>divided approach might make more sense.
>
Typically small groups but possibly more, again, the divided approach is looking better all the time.

Thanks for your help,

David C
ABC-CLIO Received on Mon Jul 22 2002 - 18:32:26 CEST