Hi again,
I am REALLY having a hard time so REALLY need your input yet again!
I think I understand the normalisation process BUT in my instance I am struggling to see how 4NF would benefit me, at the moment the only light at the end of the tunnel seems to be a freight train heading straight for me.
The database application:
Lets say a db contains 10K freelance IT people who have listed themselves in the hope of finding work. In the process of listing themselves in the db they have answered 50 'skill' related questions. (eg. html, php, perl, asp, win98, etc.) and have specified the competency they have in each of the skills listed (eg. html - excellent, php - guru, asp - hate it, etc.)
Understanding however that quite a lot of the questions allow the person to select multiple answers and further understanding that each 'skill' has at least 3 parts (eg. installation, maintaining, using, developing etc.)
The database:
I have a horde of tables containing the answers for each of the skills etc. which are used to populate the drop down lists, checkboxes etc. in the form that the person uses to list themselves in the db. Each of the elements in these tables have an IDnum which is written into the respective field in the member table.
Disregarding the design rules I could have 1 member table with 150+ columns populated as above.
The sql query which will be used to SEARCH for candidates for a specific task would be lengthy purely because of the number of fields that need to be included.
~~~~~~~
If however I follow the normalisation process then I am going to end up with a small member table, and hundreds of lookup tables which will be populated with member id's and their chosen skills and/or competencies.
This query would be ENORMOUS because it is going to have to JOIN most if not all of these tables ......
~~~~~~~
Thus my confusion ...... is a single member table with 150+ columns each containing a 1 or 2 digit reference for the respective answer/s REALLY worse than the specified 4NF option which will require so many joins?
OR ..... have I missed the point entirely ???
Many thanks
Grant
I am REALLY having a hard time so REALLY need your input yet again!
I think I understand the normalisation process BUT in my instance I am struggling to see how 4NF would benefit me, at the moment the only light at the end of the tunnel seems to be a freight train heading straight for me.
The database application:
Lets say a db contains 10K freelance IT people who have listed themselves in the hope of finding work. In the process of listing themselves in the db they have answered 50 'skill' related questions. (eg. html, php, perl, asp, win98, etc.) and have specified the competency they have in each of the skills listed (eg. html - excellent, php - guru, asp - hate it, etc.)
Understanding however that quite a lot of the questions allow the person to select multiple answers and further understanding that each 'skill' has at least 3 parts (eg. installation, maintaining, using, developing etc.)
The database:
I have a horde of tables containing the answers for each of the skills etc. which are used to populate the drop down lists, checkboxes etc. in the form that the person uses to list themselves in the db. Each of the elements in these tables have an IDnum which is written into the respective field in the member table.
Disregarding the design rules I could have 1 member table with 150+ columns populated as above.
The sql query which will be used to SEARCH for candidates for a specific task would be lengthy purely because of the number of fields that need to be included.
~~~~~~~
If however I follow the normalisation process then I am going to end up with a small member table, and hundreds of lookup tables which will be populated with member id's and their chosen skills and/or competencies.
This query would be ENORMOUS because it is going to have to JOIN most if not all of these tables ......
~~~~~~~
Thus my confusion ...... is a single member table with 150+ columns each containing a 1 or 2 digit reference for the respective answer/s REALLY worse than the specified 4NF option which will require so many joins?
OR ..... have I missed the point entirely ???
Many thanks
Grant