|
Hi all,
I've designed a database for a recruitment site, this is the my first time to design such a complex model, I feel that I have gone so far over-normalizing this database and made it too complicated. I need your comments/suggestions on its design. Please bear with me and I'll provide you with any details you might need.
An image of the database design model is here http://wunex.blogspot.com
|
|
|
|
|
Eliminate lookup tables that have only two or fewer choices (e.g. Gender). Military service - this is either yes/no or number of years - not a lookup
A lookup for Grades doesn't make sense - wouldn't this be a continuous range of values either a number (3.0) or a letter (A, B, C)?
Avoid "mapping tables" like Resumes_fields and Resumes_languages. In both cases, what is probaly wanted is
another tabel: Language_skills:(ResumeID,LanuageID,Language_proficiencyID), unless you really intend an n-to-m cross-reference (these can be difficult to maintain)
NoticePeriod does not seem like an attribute of a resume (rather a job-history) and should probably just be a range-limited value(0-n weeks) rather than a lookup.
|
|
|
|
|
Hey Rob, I just don't know what to say man....thank you
About your comments:
Eliminating lookup tables that have only 2 choices is reasonable, the reason why I created them because I don't have a clear idea as how much is too much normalization, I thought that even a table with two values should be separated!....but ok I'll eliminate them.
Grades here are (Excellent, Very Good, Good, Satisfactory), so would you still recommend eliminating it?
I don't understand this
Rob Graham wrote: Avoid "mapping tables" like Resumes_fields and Resumes_languages.
These two tables are junction tables between Resumes and Fields(job/company field) and Resumes and Languages(Spoken languages) respectively.
I don't understand this:
Rob Graham wrote: In both cases, what is probaly wanted is another tabel: Language_skillsResumeID,LanuageID,Language_proficiencyID), unless you really intend an n-to-m cross-reference (these can be difficult to maintain)
what do you mean by in both cases I need another table(Language_Skills)? and what is n-to-m cross-reference?
NoticePeriod contains values(1 week, 2 weeks,....,1 month) so is this ok to keep it or you still recommend to eliminate it and just get the value from user and put it in a varchar field in resumes/job_history?
Rob, thank you again and excuse me for being such a nag, but my resources are limited to forums, e-books, articles, and tutorials.
|
|
|
|
|
iskaza wrote: I thought that even a table with two values should be separated!
If it's always goint to be just a boolean answer (yes/no, true/false, etc.) then a boolean (bit) field will do, you can format the meaning of 1/0 in the UI as appropriate.
iskaza wrote: These two tables are junction tables between Resumes and Fields(job/company field) and Resumes and Languages(Spoken languages) respectively.
Do you really intend an m-to-n relation where both m & n can be > 1? I think you really want a
table related to a resume that contains the lanuage skills of the applicant: rows with resumeid, Language Id, and Language proficiency. The lanuage proficiency is an attribute of the applicant/resume, not of the language itself as your present structure implies.
Is a field an attribute of a resume, or of a job (in which case maybe its just a column in History_jobs)
If it is an attribute of the resume, then perhaps resume_fields isn't meant as a cross-reference, but just n fields per resume. Last modified: 10mins after originally posted -- Oops, hit post before I was done...
|
|
|
|
|
Yes there's a many-to-many relationship between (Resumes and Fields), (Resumes and Languages).
Rob Graham wrote: I think you really want a
table related to a resume that contains the lanuage skills of the applicant: rows with resumeid, Language Id, and Language proficiency. The lanuage proficiency is an attribute of the applicant/resume, not of the language itself as your present structure implies.
The languages table has predefined languages(English, French,....), so how would I map LanguageProficiency to every language skill per applicant. Yes LanguageProficiency is an attribute of the applicant's language skill but I have a FK named LanguageProficiency in the Languages table which further specifies the LanguageProficiency for every language skill per applicant...!
About Field...Well, Field is a table which contains the various business activities (Accounting, Construction, Import & Export, Computer Hardware,......). So, I make use of this table as the Employer's company field and the Applicant's required job field(s), just like the countries table, I use it to specify the Employer's country, the applicant's country and the location of a posted job.
|
|
|
|
|
iskaza wrote: Yes there's a many-to-many relationship between (Resumes and Fields), (Resumes and Languages).
I would argue that there is really a 1-n relationship between a resume and the applicable fields.
There is a different 1-m relation betwen a field and applicable jobs. although that implies a possible n-m relation between resumes and jobs, it will be easiier in the long run to maintain two 1-n relationsips than 1 m-n relation. Write some sample qureies that would use your junction tables (in particular some insert and update queries). See if they really make sense. If they do, then fine. I'm just generally suspect of tables that contain only fk's from non-lookup tables. iskaza wrote: Yes LanguageProficiency is an attribute of the applicant's language skill but I have a FK named LanguageProficiency in the Languages table which further specifies the LanguageProficiency for every language skill per applicant...!
I find it cumbersome to have to think about having to select the combination of lanugage and proficiency together as a single attribute of a resume. I would seem to make more sense to select the languages and proficiencies separately as values in a record related to a resume. But, whatever makes sense to you...again I would walk through some usage scenarios before settling on a design.
Usually it is good to start with a highly normalized design and then de-normalize as needed for ease of (programmer) use and efficiency.
|
|
|
|
|
Rob Graham wrote: I would argue that there is really a 1-n relationship between a resume and the applicable fields.
I'd like to give the applicant the chance to have more than 1 desired job in his resume, that makes a m-m relationship between resumes and fields.
Rob Graham wrote: I find it cumbersome to have to think about having to select the combination of lanugage and proficiency together as a single attribute of a resume
It's not a single attribute, language is an attribute of a given resume, proficiency is an attribute of that language attribute...it's attribute of an attribute, what I want to do is:
specifying proficiency of every language selected by the applicant to layout this part of the whole resume like:
Language Skills:
English Fluent
French Fair
So, do you have any alternative way to do that?
One last silly question: what exactly is meant by a lookup table and what distinguishes it from aother tables?
|
|
|
|
|
iskaza wrote: do you have any alternative way to do that?
Language skills:
<--------pK----------->
ResumeID | LanguageID | Skill Spoken | Skill Written
-----------------------------------------------------
1 | 1 | 2 | 1
1 | 2 | 3 | 4
Languages:
LanuageID | Lanuage Name
------------------------
1 | English
2 | French
Lanuage Skill Level:
LevelID | Level Description
----------------------------
1 | Fluent
2 | Good
3 | Fair
4 | Poor
5 | none
I can change my mind about the skill level(s) without having to delete and re-add the row.
I can extend the information (as I did in the example) more easily.
iskaza wrote: hat exactly is meant by a lookup table and what distinguishes it from aother tables?
A lookup table is basically an enumeration - it provides an integer substitute for one of an enumerated set of values. Its main purpose is to prevent unintended problems caused by user mispelling, etc. and to limit values to a pre-determined set of possibilities. This makes searching and sorting on values of the column that useds the lookup much more deterministic, and improves performance, since integer fields are easier to index.
|
|
|
|
|
1) Your approach suggests that I can do the same to (Resume_Fields) hence eliminating all of the junction tables approach....is that right? if it's so, why I should try to avoid junction tables?
2) About lookup tables; if it's just a matter of avoiding misspelling errors, can't I just populate a comboBox or whatever with the hardcoded values and let the user select from them then extract this selected value and insert it right in a field of the table without the need to reference any lookup tables that would contain these predetermined values? I mean is there a clear-cut rule as when I should to use lookup tables and when I should not?
|
|
|
|
|
iskaza wrote:
1)
Yes.
2) It's better to do this with a database table - you can change the wording of the string values (for instance translate to another language) or extend the enumeration without recoding/recompiling - and it makes the database more self-documenting. No clear cut rule, but I prefer to keep everything thats about the data in the database. It's also a storage space saving device - the integer id takes less space than the equivalent string in the tables where it is used.
|
|
|
|
|
iskaza wrote: 2) About lookup tables; if it's just a matter of avoiding misspelling errors ...
The golden rule for all databases: Keep all instances of data in one place, and one place only. Refer to it if necessesary. The guy who ends up with the maintenance will thank you. 
--
-= Proudly Made on Earth =-
|
|
|
|
|
Hi i am using access in my webapplication.I am facing a lot of problems like connection already open.My question is why MS access is not suitable for web application ?
Thanks and regards
|
|
|
|
|
sishya wrote: My question is why MS access is not suitable for web application ?
Because it is fundamentally a single user file based database, not a multiuser server.
|
|
|
|
|
Do you have the 'Default Open Mode' set to 'shared' in the Database Options?
Steve
|
|
|
|
|
Hi Rob and all,
I have a recruitment site which is part of a large portal which has other services, so the whole portal has its own database apart from the recruitment's. and there's a couple of other databases. Now I want to authenticate the user once I mean if this user(JobSeeker) had already been logged in from somewhere on the portal modules, I don't want to ask him again to sign in to search jobs or modify his resume. Now, with that said I was wondering can I create a FK say UserID in the resumes table in the (Recruitment) database whose value is the PK of the Users table of the (portal) database. So, is that possible?? I mean referencing a PK from a table in another database?? If not, what is the best approach to integrate multiple databases within that portal and to implement the above functionality.
Thanks
|
|
|
|
|
JobSeeker is in the MyTestDB database and Resumes is in the MyTestDB2 database.
select * from MyTestDB..JobSeeker js inner join MyTestDB2..Resumes re on js.JobSeekerID = re.JobSeekerID
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Greetings:
I'm learning how to access SQL databases under C#. SQL is a little new to me but I'm really kind of enjoying it. I have a general sort of question about strategies and accepted approaches though:
Let's say that I have to search a single table for records that match a certain criteria. What would be the best approach and why:
1. Implement an SqlCommand and use a SELECT statement to get SQL to do all the work and search for the records.
2. Load the table into either a DataReader or DataAdaptor and search the table myself(programmatically).
What are the key considerations? Speed? Memory usage? Which approach is faster? Safer? Is there a universally prefered approach or is it case by case?
I'm not seeing anything in the literature that I have that address these questions.
Thanks in advance to anyone that responds,
Mark
|
|
|
|
|
Using stored procedures is the preferred and recommended way access your data. If the tables have been properly constructed and have good indexes then in general it would be more advantageous to run the query in SQL Server and return the results. I say in general because there are other factors; is it a very complex search, size the dataset involved, etc.
only two letters away from being an asset
|
|
|
|
|
Hi Mark:
I haven't got to stored procedures yet. I'm not sure what they are - just a script of consecutive SQL statements?
By "properly constructing" my tables, are you refering to the various levels of normalization? I think I have a pretty good understanding of that. I'm a little sketchy on indexing though.
Thanks for your input.
Mark
|
|
|
|
|
|
|
If you're using SQL2005Express and you're completely new to SQL and SQL Server then I would recommend this book[^]for your learning pleasure.
"We've all heard that a million monkeys banging on a million typewriters will eventually reproduce the entire works of Shakespeare. Now, thanks to the internet, we know this is not true." -- Professor Robert Silensky
|
|
|
|
|
You almost always want SQL Server to do the work. If you index the table appropriately and supply a suitable query, SQL Server can find a given row by touching only a small number of pages making up the index (loading them from the disk), and one page of the main table, if the columns you're requesting aren't part of the index. It only returns one row across the wire, which will reduce the time spent getting the network to transfer the row.
If you make SQL Server give you all the rows, it will touch - and briefly lock - all the rows in the table, requiring all pages of the table to be read from the disk (slow), and the network time will be much higher. It'll take much longer to get your data. Then, you'll be using more memory on the client side to store the results while you search for the row you're after.
|
|
|
|
|
Thanks for the responses everybody.
Mark
|
|
|
|
|
I have 2 tables : Client(ClientID and other informations) and ProjectClient(ProjectID,ClientID).
I must select clients and all project id's they are working at, the project id's being in one field, separated by comma.Could you help me, pls?
Thank you.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
