I have a staff database where we are storing information in Normalized form. There are 3 entity tables and 2 mapping tables connecting these with m-n relationships
However, for ease of collecting this information, we are gathering this in a flatfile in excel.(Employeeid, Fname, LName, ITskills(in multiple rows), projectName)
Our plan is to import this flatfile into MS Access into a Staging Table and then write a query to read the staging table and insert the equivalent records in the 5 tables- and creating the identifier keys systemically. (Employee, Skills, Project, employeeSkillsMapping, skillsProjectMapping)
We tried converting from flat-file to 5 tables in excel manually and importing- getting very challenging to implement the relationships. Any suggestions/ links on how to get this done- how will the keys get generated systemically in all the tables.