Bugzilla database schema review

 
Picture of Brito e Abreu Fernando
Bugzilla database schema review
by Brito e Abreu Fernando - mercredi, 19 décembre 2007, 9:25
 
Post here the defects found and proposed workarounds for the Bugzilla DDL schema that is posted in this topic.
 
Picture of MERINO Leonel
Re: Bugzilla database schema review
by MERINO Leonel - mercredi, 19 décembre 2007, 10:47
 
I found the following defects:
  • Tables bugcclist, bugdependson, bugdetails don't have primary key
  • I didn't find a column to store a classification. There is one only for classification_id, so maybe should be better create another table for classification
  • Tags reporter and assigned_to have an attribute called name, but I didn't find a column to store them
  • In the XML, I see that a bug have a list of long_desc tags and these tags have the attribute isPrivate, I didn't find a place to store that too. Maybe this can be fixed adding a column to bugdetails table.
Picture of CHARLOT J
Re: Bugzilla database schema review
by CHARLOT J - mercredi, 19 décembre 2007, 11:41
 
"I didn't find a column to store a classification. There is one only for classification_id, so maybe should be better create another table for classification"

This information is stored in the Classification Table, the field is name. This is a kind of optimization.(we only have 5 items : Components, Server Software, Client Software, Client Support and Unclassified)

First of All, in order to avoid 'ERROR:  type "error" does not exist', I'll add the condition "IF EXISTS" for all "DROP" for instance now we have this SQL DDL :

DROP DATABASE IF EXISTS BugBase;
...
DROP DOMAIN IF EXISTS Error CASCADE;
...
DROP TABLE IF EXISTS Person CASCADE;


Instead of using domain, to define ENUM Type we can use ENUM directly in the table declaration. for instance with the Error domain :
We have:

DROP DOMAIN Error CASCADE;
    CREATE DOMAIN Error
      as text
      check (value in ('NotFound', 'NotPermitted', 'InvalidBugId'));
     
In the Bug table we can use ENUM instead :
           error Text (check error in ('NotFound', 'NotPermitted', 'InvalidBugId'))