Designing a database schema for a Job Listing website?

Question

For a school project I'm making a simple Job Listing website in ASP.NET MVC (we got to choose the framework).

I've thought about it awhile and this is my initial schema:

JobPostings
+---JobPostingID
+---UserID
+---Company
+---JobTitle
+---JobTypeID
+---JobLocationID
+---Description
+---HowToApply
+---CompanyURL
+---LogoURL

JobLocations
+---JobLocationID
+---City
+---State
+---Zip

JobTypes
+---JobTypeID
+---JobTypeName

Note: the UserID will be linked to a Member table generated by a MembershipProvider.

Now, I am extremely new to relational databases and SQL so go lightly on me.

What about naming? Should it be just "Description" under the JobPostings table, or should it be "JobDescription" (same with other columns in that main table). Should it be "JobPostingID" or just "ID"?

General tips are appreciated as well.

Edit: The JobTypes are fixed for our project, there will be 15 job categories. I've made this a community wiki to encourage people to post.

Answer 1

A few thoughts:

• Unless you know a priori that there is a limited list of job types, don't split that into a separate table;
• Just use "ID" as the primary key on each table (we already know it's a JobLocationID, because it's in the JobLocations table...);
• I'd drop the 'Job' prefix from the fields in JobPostings, as it's a bit redundant.

There's a load of domain-specific info that you could include, like salary ranges, and applicant details, but I don't know how far you're supposed to be going with this.

Answer 2

Looks good to me, I would recommend also adding Created, LastModified and Deleted columns to the user updateable tables as well for future proofing.

Make sure you explicitly define your primary and foreign keys as well in your schema.

Answer 3

What about naming? Should it be just "Description" under the JobPostings table, or should it be JobDescription (same with other columns in that main table). Should it be "JobPostingID" or just "ID"?

Personally, I specify generic-sounding fields like "ID" and "Description" with prefixes as you suggest. It avoids confusion about what the id/description applies to when you write queries later on (and saves you the trouble of aliasing them).

Answer 4

I'd recommend folding the data you're going to be storing in JobLocations back into the main table. It's ok to have a table for states and another for countries, but I doubt you want a table that contains every city/state/country pair, you really don't gain anything from it. What happens if someone goes in and edits their location? You'd have to check to make sure no other joblisting points to the location and edit it, else create a new location and point to that instead.

My usual pattern is address and city as text with the record and FK to a state table.

Answer 5

How would you include say a list of skills to your jobs table?