How to build a good database design? Is there any guidelines? I need to build a biological database for my course project... I am going to do a phylogenetic database... I need some good advice on how to prepare a good database design...
How to build a good database design...
There are thousands of criteria for an optimal database. There are text books dedicated to the subject, so a forum post is going to be severely limited in the information it can impart.
However, the key idea of a database is to minimise redundancy - ie store data in the least possible amount of space. Normalisation is how this is don from a design viewpoint.
Have a look here or google around for something similar:
http://www.troubleshooters.com/littstip/ltnorm.html
However, the key idea of a database is to minimise redundancy - ie store data in the least possible amount of space. Normalisation is how this is don from a design viewpoint.
Have a look here or google around for something similar:
http://www.troubleshooters.com/littstip/ltnorm.html
It is a fight between redundancy and query length. It is commonly usable to separate everything to separate columns and tables, but not all. You should draw ER-diagram before. It is good start.
You should imagine almost all "SELECT" operations before you create the database. "All" means imagine all views, you want to do with your data. Many columns, which are in WHERE or JOINs should have indexes, but many indexes slow down INSERT operations and cause the table to be larger in disk size. Without indexes should be JOIN and WHERE operations very slow. It depends on number of rows, 20 rows - you really don't want an index, 10000 rows - you must have index.
Every table must have primary key. I prefer to create primary keys without meaneing (some auto increment integer field with name id_blabla). It is easier to do JOINS.
You could try to use stored procedures and triggers. It is more work, but it helps you to have data consistent. When you delete some item, you should forget delete all rows from other tables, depending on deleted item. After delete, this rows "hangs" in database and should produce unpredictable results (when you also forget some WHERE condition). This is only one example what should happen.
You should imagine almost all "SELECT" operations before you create the database. "All" means imagine all views, you want to do with your data. Many columns, which are in WHERE or JOINs should have indexes, but many indexes slow down INSERT operations and cause the table to be larger in disk size. Without indexes should be JOIN and WHERE operations very slow. It depends on number of rows, 20 rows - you really don't want an index, 10000 rows - you must have index.
Every table must have primary key. I prefer to create primary keys without meaneing (some auto increment integer field with name id_blabla). It is easier to do JOINS.
You could try to use stored procedures and triggers. It is more work, but it helps you to have data consistent. When you delete some item, you should forget delete all rows from other tables, depending on deleted item. After delete, this rows "hangs" in database and should produce unpredictable results (when you also forget some WHERE condition). This is only one example what should happen.
 |