A query on Database design
-
Hi all, I have created a database for my application. One of the tables manages the employees data. It has got 2 columns listing primary skillset and secondary skill set (free text to give a summary of skillsets). Both these columns are optional. It is basically aimed at capturing skillsets of employees in a department but could be extended to all. Now, what comments I receive from some of team members is that these skillsets columns being optional should be placed in a different table with a link to employee_master. Should I do this really? If so, why should I? How does this improve the existing design? What is wrong with having it all in a single table? Thanks in advance,
-
Hi all, I have created a database for my application. One of the tables manages the employees data. It has got 2 columns listing primary skillset and secondary skill set (free text to give a summary of skillsets). Both these columns are optional. It is basically aimed at capturing skillsets of employees in a department but could be extended to all. Now, what comments I receive from some of team members is that these skillsets columns being optional should be placed in a different table with a link to employee_master. Should I do this really? If so, why should I? How does this improve the existing design? What is wrong with having it all in a single table? Thanks in advance,
Since they are optional, they could be 'missing' if in a linked table. That is, you would simply not add the linked row until there was some data to supply. This would conserve space in the database (but would complicate your application design a bit, since you would need to deal with returning no rows for the join). It would also simplify tasks like locating which employees had no skillsets, etc. If you keep this in the main table, every row will have the colums, which take up some space even if NULL.
-
Hi all, I have created a database for my application. One of the tables manages the employees data. It has got 2 columns listing primary skillset and secondary skill set (free text to give a summary of skillsets). Both these columns are optional. It is basically aimed at capturing skillsets of employees in a department but could be extended to all. Now, what comments I receive from some of team members is that these skillsets columns being optional should be placed in a different table with a link to employee_master. Should I do this really? If so, why should I? How does this improve the existing design? What is wrong with having it all in a single table? Thanks in advance,
The key advantage that separating the skillset information out to an Employee_Skillset table is that you could store much more information, make it easier to retrieve and have greater control over the data entry. You could have columns to cover not just the skill but the level and any qualifications held. It is perfectly possible to store and retrieve this data from free-text columns but you have a greater chance of simple things like spelling mistakes causing incorrect query results. Ian