CREATEUSERWIZARD or OWN REGISTRATION TABLE

Question

I am creating an Employee Site for employees to insert their CV and employers to contact them.
I have 3 main tables:-

1. Employee_Career_History (career_id, username, experience, area etc.)
2. Employee_Education_Details (edu_id, username, qualification, details etc.)
3. Employee_Hobbies (hobby_id, username, hobby etc.)

Suppose an employer wants to see the details of a PARTICULAR employee, at the button click event of the search button, I run a query like-
"select * from Employee_Career _History where Employee_Career _History.username=@username"
Now, the problem is-

1. if the user CHANGES HIS USERNAME, his record in the tables will never be accessed by the employer when he wants to see that particular person
(because the employer sees his new username and in the record in table has ONLY his OLD username), so
should I go some other key (eg. ProviderKey) or Should I go for myown registration table instead of createuserwizard?
2. Will CreateUserWizard in asp.net allow to CHANGE USERNAME?
3. I am using email as the username (in the text box for username I ask the user to enter email id). Hope it is fine.

Manish Dwivedi · Answer

Hi Both the approach is good. Because we are storing the record for the user then what is the advantage of adding one more table in the fucture and from where u will get the data from the existing user for that table. as u are saying that field contains null value for the existing one in the user registration table. don't worry about that u can use left outer join for getting the data. U can use any one approach. It all depends on the requirement.

r p · Answer

Thanks Manish

Manish Dwivedi · Answer

Yes r p U should have use one table if u want to store all the information related to the user. Happy Coding!!

r p · Answer

Thanks Manish can I go for ONE table? Employee(Reg_id username experience area qualification hobby aboutme otherinfo). By this I don't have to store details in 3 different tables. I can always use 1 table. Thanks

r p · Answer

But the FAULT WITH THE SECOND one is suppose I add one more table called - abc_table in the future (after 1000 records are saved) I have to create one more colum in Registration_Table then for the first 1000 records the new field will be NULL. Regarding the query:- In the first approach I can fetch the data by running- SELECT * FROM Employee_Career_History WHERE Employee_Career_History.Reg_id = Registration_Table.Reg_id and in the second approach:- SELECT * FROM Employee_Career_History INNER JOIN Registration_Table ON Employee_Career_History.Career_id=Registration_Table.Career_id So both of the need only 1 query. So do you find any advantage in choosing the second one? After all should I go for storing data in 3 different tables? Can I store all of them together in ONE Table? Is it a good approach? Thanks

Manish Dwivedi · Answer

Hi Second approch is better because whenever u want to fetch the data. u can easily get the data by writing one query.

r p · Answer

Thanks Manish for your help. This will solve the issue of changing the user name. In case the user changes his user name in the future I can update just the 'USERREGISTRATION' table alone and I don't have to worry about other tables. But now I have two solutions to design the table. I don't know which one is better:- First Method: 1. Registration_Table(Reg_id username) 2. Employee_Career_History (Career_id Reg_id experience area etc.) 3. Employee_Education_Details (Edu_id Reg_id qualification details etc.) 4. Employee_Hobbies (Hobby_id Reg_id hobby etc.) In the first solution Reg_id is the foreign key in all the tables Second Method: 1. Registration_Table(Reg_id username Career_id Edu_id Hobby_id) 2. Employee_Career_History (Career_id experience area etc.) 3. Employee_Education_Details (Edu_id qualification details etc.) 4. Employee_Hobbies (Hobby_id hobby etc.) Here all the other ids beome the foreign in the Registration table. Which one do you recommend? Thanks a lot for your help.

Manish Dwivedi · Answer

Hi I think this is not the right to define the table. u can also create one more table for the user registration. in which u can put the fields UserIdusernamecareer_id edu_id and hobby_id and remove username from all the tables. by this way u can create relations between the tables and your data will be consistent.

r p · Answer

'id' (I changed it now. It is career_id edu_id and hobby_id). They are primary keys of each table. 'username' is the user name of each employee which he uses TO LOGIN to the site. When he saves HIS Employee_Career_History or Employee_Education_Details or Employee_Hobbies from his login the id is automatically generated as it is auto increment in the table. But these ids will not help me to search a candidate. Because they are not the same in all the tables. So I am inserting the username by which the user (EMPLOYEE) is inserting his data. This username will help me to retrieve data easily ( select * from Employee_Career_History where username=@username or select * from Employee_Education_Details where username=@username or select * from Employee_Hobbies where username=@username) Is it the I should do? I am following the quick start tutorial- BeerHouse project in asp.net site

Manish Dwivedi · Answer

Hi I am not getting why r u using id username in all the tables. change the schema like that. Employee_Career_History (id username experience area etc.) 2. Employee_Education_Details (idqualification details etc.) 3. Employee_Hobbies (idhobby etc.) and create the id as the primary key. it will solve your search problem.