Database structure for a "Document Management System"

[Michelangelo Rezzonico]

I all,

I am programming a "Document Management System" whit Perl && MySQL.
In my DMS I have 20 different document-type.
Each document-type have approximatly 30 fields (20 generic fields and 10 specific fields).
The total of all different fields is 50.

The problem is the design of the database.

I see the following 3 solutions:

-----------------------------------------------------------------------------
1) For each document-type I have a single table

-----------------------------------------------------------------------------
2) I have only one big table with 50 columns

-----------------------------------------------------------------------------
3) I have a "proper design" with:
1 table for the fields definition
1 table where the different document-type are listed
1 table where are defined the fields for each table
2 tables for storing data

You can see an example of this database structure for storing the document-type email at the following link.

http://195.144.40.170/file.png

Note: I have "copied" this structure from the
KnowledgeTree - Open Source Document Management System
see http://www.ktdms.com/

-----------------------------------------------------------------------------

I am convinced that the third solution is the correct solution.
Therefore I have made some tests.
I have added 1000000 documents.
The problem is that the SELECT statement is very slow.

Consider the following select command.

SELECT D.id,
DFL1.value,
DFL2.value
FROM documents AS D
LEFT JOIN document_fields_link AS DFL1 ON DFL1.document_id = D.id
LEFT JOIN document_fields_link AS DFL2 ON DFL2.document_id = D.id
WHERE DFL1.document_field_id = 1
AND DFL2.document_field_id = 2
AND D.document_type_id = 2
LIMIT 10;

This command select the id (D.id) and two column (DFL1.value, DFL2.value) of the type "email" (document_type_id = 2). The result is limited to 10 records.
This select is executed in 3.3 seconds.
But if I select 11 fields (DFL1.value ... DFL11.value) the join is on 11 tables and the execution time is 337 seconds !!
Note that I have created the index on column "document_id" of the table document_fields_link.

With a single big table (solution 2) a select on a table with 1000000 records is done in
2.3 seconds.

Is this "normal" or I make an error ?
Can someone help me ?

Thanks in advance. Miche



Edited 8 time(s). Last edit at 11/17/2006 10:51AM by Michelangelo Rezzonico.