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.
Loading
Blocked AccountPosted Nov 27, 2008, 4:36 AM
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 pPosted Nov 27, 2008, 5:50 AM
Blocked AccountPosted Nov 27, 2008, 5:37 AM
U should have use one table, if u want to store all the information related to the user.
Happy Coding!!
r pPosted Nov 27, 2008, 5:14 AM
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 pPosted Nov 27, 2008, 3:45 AM
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,
Blocked AccountPosted Nov 26, 2008, 10:46 PM
Hi,
Second approch is better because whenever u want to fetch the data.
u can easily get the data by writing one query.
r pPosted Nov 26, 2008, 5:14 PM
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.
Blocked AccountPosted Nov 25, 2008, 10:47 PM
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 UserId,username,career_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 pPosted Nov 25, 2008, 6:08 AM
'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
Blocked AccountPosted Nov 25, 2008, 4:58 AM
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 (id,qualification, details etc.)
3. Employee_Hobbies (id,hobby etc.)
and create the id as the primary key.
it will solve your search problem.