为Job Listing网站设计数据库模式？

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

对于一个学校项目,我在ASP.NET MVC中创建了一个简单的Job Listing网站(我们必须选择框架)。

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

我已经考虑了一段时间,这是我的初始架构:

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

JobPostings + --- JobPostingID + --- UserID + ---公司+ --- JobTitle + --- JobTypeID + --- JobLocationID + ---说明+ --- HowToApply + --- CompanyURL + --- LogoURL

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

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

JobTypes
+---JobTypeID
+---JobTypeName

JobTypes + --- JobTypeID + --- JobTypeName

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

注意:UserID将链接到MembershipProvider生成的Member表。

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

现在,我对关系数据库和SQL非常陌生,所以对我不屑一顾。

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"?

怎么命名?它应该只是JobPostings表下的“Description”,还是“JobDescription”(与该主表中的其他列相同)。它应该是“JobPostingID”还是“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.

编辑:我们的项目修复了JobTypes,将有15个工作类别。我已经把这个社区wiki鼓励人们发帖。

5 个解决方案

#1

Job Schema http://gfilter.net/junk/JobSchema.png

工作架构http://gfilter.net/junk/JobSchema.png

I split Company out of Job Posting, as this makes maintaining the companies easier.

我将公司从职位发布中分离出来,因为这使公司更容易维护。

I also added a XREF table that can store the relationship between companies and locations. You can setup a row for each company office, and have a very simple way to find "Alternative Job Locations for this Company".

我还添加了一个XREF表,可以存储公司和位置之间的关系。您可以为每个公司办公室设置一行,并有一种非常简单的方法来查找“此公司的替代工作地点”。

This should be a fun project...good luck.

这应该是一个有趣的项目......祝你好运。

EDIT: I would add Created and LastModifiedBy (Referring to a UserID). These are great columns for general housekeeping.

编辑:我会添加Created和LastModifiedBy(参考用户ID)。这些是一般管家的好栏目。

#2

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...);

只需使用“ID”作为每个表的主键(我们已经知道它是一个JobLocationID,因为它位于JobLocations表中......);

I'd drop the 'Job' prefix from the fields in JobPostings, as it's a bit redundant.

我从JobPostings中的字段中删除'Job'前缀,因为它有点多余。

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.

您可以包含一些特定于域的信息,例如工资范围和申请人详细信息,但我不知道您应该在多大程度上采用这种方式。

#3

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

对我来说看起来不错,我建议还将Created,LastModified和Deleted列添加到用户可更新表以及将来的校对。

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

确保在架构中明确定义主键和外键。

#4

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"?

怎么命名?它应该只是JobPostings表下的“描述”,还是应该是JobDescription(与该主表中的其他列相同)。它应该是“JobPostingID”还是“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).

就个人而言,我按照你的建议指定带有前缀的“ID”和“描述”等通用声音字段。它可以避免在稍后编写查询时对id / description适用的内容产生混淆(并省去了别名它们的麻烦)。

#5

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.

我建议将您要存储在JobLocations中的数据折叠回主表中。可以为州和国家提供一张桌子,但我怀疑你想要一张包含每个城市/州/国家/地区的桌子,你真的没有从中获得任何东西。如果有人进入并编辑他们的位置会发生什么?您必须检查以确保没有其他工作列表指向该位置并进行编辑,否则创建一个新位置并指向该位置。

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

我通常的模式是地址和城市作为文本与记录和FK到州表。

#1