Database design is both an art and a science,although the science part is generally the most relevant.There are rules that govern the process of design,including a series of steps called nomalization that ensure you have the optimum structrue for the tables in you database.However,you must also balance this process with the way that you intend to use the data.In some cases,the normalization rules can divide your data across more tables than is strictly necessary when taking into account the data content and the way you will use it.
This is where the rules of normalization come into play.
The first rule states that a row must not contain repeating groups.
The second rule of normalizaiton states that you must remove data repeated in multiple rows and place it in a separate linked table.
The third rule of nomalization states that data in the row should not depend on anything other than the primary key of the table.
There are actually five rules of normalization . The final two help to optimize the data structure and produce the most ideal data format ,but at the expense of extra complexity that you will probably not require in you application.Generally,you will apply only the first three rules as described here.
The rules for normalization are the work of E. F. (Ted) Codd working at IBM in the early 1970s,and there are many articles and resources on the Web that explore these rules in depth. Microsoft publishes two at http://support.microsoft.com/kb/164172/EN-US/ and http://support.microsoft.com/kb/100139/EN-US/. Search the Web for “Codd normalization rules”to see more.