one big table or many smaller tables
Date: Tue, 16 Jul 2002 12:15:26 -0700
Message-ID: <3d3470d0$0$253$4c4eb88e_at_reader.news.uu.net>
Database theorists:
I have a fairly complex data structure with about 110 tables, 11 of the tables have 'notes' related to them and each row can have multiple notes associated with them.
We're considering two ways to model this, the simplest
structure is to have a notes table associated with each table that will have
notes, so you have
table1_notes, table2_notes etc, each with the primary key of the
related table in each row in notes.
In this simple model to query the notes table it would be something like:
"SELECT note_text FROM table1_notes with id=currentID"
The other way is to have one large notes table (it would have about..
I think it was 60,000 records in it, not enough that performance would
be an issue), and have the primary key of each related table + the
table name in the notes table, so you would specify the table name
when you query the notes table, like:
"SELECT note_text FROM notes with table_name='table1'
AND table_id=currentID"
I'm advocating the second method because in the interface design there
will be several places where we need to pull together notes from
multiple related tables, for instance if table1 is students and table2
is classes and table3 is instructors and I'm at a student record and
want to see the notes for the student and the classes and the
instructor it seems to me it would be reasonable to modify the query
pulling in the student notes:
"SELECT note_text FROM notes, classes_students
and add notes for the classes, let's see: AND classes_students.student_id=current_student_id AND with notes.table_id=classes_students.classes_id AND with notes.table_name='classes'
I would have to pick an n:n for the example, but there will be some. Anyway,
assuming
this pulls in the notes for the classes that the student is taking,
modifying the query like this should pull in the notes for the student
and the classes that that student is taking, and you could pull in the
instructor notes as well, displaying all the notes for the three
entities in a single grid. I believe so anyway - we're using SQL
Server 2K, vb.net., Win2k Server with a dual processor compaq Proliant
server.
So my question is: do any of you see any problems with the one big
notes table? If anyone has used a model like this (I've seen things
modeled this way before but never used it on something this large and
complex)
I would also be interested in knowing that it works, any pitfalls.
Thanks in advance for any answers, and for reading all this even if you
don't
have an answer (or opinion).
David C.
Database Developer
ABC-CLIO Received on Tue Jul 16 2002 - 21:15:26 CEST