Re: Job listings DB

On Mon, 21 May 2001 13:55:12 +0100, py (pylem@sympatico.ca) wrote: >> Hi, >> >> I am designing a system for my company to get employee resumes via >>the >Web, >> rather than paper. All resumes now go into a mySQL DB, which can be >> searched by HR (at least that's the theory :) > >[I would do it like that also] > >> >> My questions: >> >> For job listings - how do I design the tables? Can I dump all job >>listings >> into a single table, and all applications/resumes into another >>one? Or >> should they be normalized? > >[They should be normalized] only if it makes life easier. I hardly ever do it that way because it takes longer and doesn't usually speed things up any. >> If normalized - how much? For example, in a listing, I have job >>code, >> department, location, salary range, etc. - should I have separate >>tables >> for each of these? Like > >[Yes, I would say so] sounds like a headache to me. >> jcode dept loc >> 1 213 2 >> 2 77 3 >> >> where dept and loc are FKs to other tables? Or is this taking it >>too far? > >[It is not a necessity, but it would be easier to modify or upgrade >later, wich we always do] not necessarily easier, it just means you're making changes to one table instead of another. >> Same for application form. All this information that I am getting - >> should >> I put into a single table, or split across separate tables? I do >>plan to >> search by differenet criteria later. >> >> What about items in drop-downs? For example, if I have a drop-down >>for >> salary ranges or countries - should this information come from a >>table, or >> should I hard-code into the form itself? How do I make the >>decision as to >> which is appropriate? > >[I would never hard code anything that can change, salary ranges can >changes, country can change too :) ] I agree there. you can even take laziness to the next level and use 1 table with fields for just the items you will use in your sql, serialize the rest of the data and put it in a text field -> that way you can add fields later without having to alter the table. Plus you could put a fulltext index on that and do an easy keyword search on all the data.