数据库设计-结构 [英] Database Design - structure
问题描述
我正在设计一个包含课程和工作的网站。
I'm designing a website with courses and jobs.
我有一个工作表和课程表，每个工作或课程都是由机构提供的，它可以是机构（提供课程）或公司（提供工作）。我要在这两个选项之间做出决定：
I have a jobs table and courses table, and each job or course is offered by a 'body', which is either an institution(offering courses) or a company(offering jobs). I am deciding between these two options:
选项1：使用机构表，并在机构和公司中使用body_type列。
option1: use a 'Bodies' table, with a body_type column for both insitutions and companies.
选项2：使用单独的机构和公司表。
option2: use separate 'institution' and 'company' tables.
我的主要问题是也是发布表格，其中显示了课程和工作的所有广告。因此，如果我选择第一个选项，则只需要将body_id记录为每个帖子的记录，而如果选择第二个选项，则在显示帖子时，我需要在某个地方添加一个额外的联接。
My main problem is that there is also a post table where all adverts for courses and jobs are displayed from. Therefore if I go with the first option, I would just need to put a body_id as a record for each post, whereas if I choose the second option, I would need to have an extra join somewhere when displaying posts.
哪个选项最好？还是有替代设计？
Which option is best? or is there an alternative design?
推荐答案
不要对SQL语法和额外联接这么多思考在模型，实体，属性和关系方面。
Don't think so much in terms of SQL syntax and "extra joins", think more in terms of models, entities, attributes, and relations.
在最高级别上，您的模型的中心实体是帖子。帖子的属性是什么？
At the highest level, your model's central entity is a Post. What are the attributes of a post?
- 谁发布了帖子
- 发布时
- 其内容
- 一些用于搜索目的的其他元数据
- （其他？）
- Who posted it
- When it was posted
- Its contents
- Some additional metadata for search purposes
- (Others?)
每个属性对于该帖子都是唯一的，因此应直接在帖子表中，或者不应该在帖子表中与相关相关的表格；一个明显的例子是谁发布了-这应该只是一个具有与发布者/实体实体的另一张表格相关的ID的发布者字段。 （注意：您的发布者实体不必一定是您的身体实体...）
Each of these attributes is either unique to that post and therefore should be in the post table directly, or is not and should be in a table which is related; one obvious example is "who posted it" - this should simply be a PostedBy field with an ID which relates another table for poster/body entities. (NB: Your poster entity does not necessarily have to be your body entity ...)
您的发布者/实体实体具有各自的属性，这些属性对于每个发布者都是唯一的/ body，或者再次，应该在自己的某个规范化实体中。
Your poster/body entity has its own attributes that are either unique to each poster/body, or again, should be in some normalized entity of their own.
工作职位和课程职位是否存在实质性差异？也许您应该考虑包含作业和课程特定数据的CoursePosts和JobPosts子表，然后将它们联接到Posts表中。
Are job posts and course posts substantially different? Perhaps you should consider CoursePosts and JobPosts subset tables with job- and course-specific data, and then join these to your Posts table.
关键是要获得模型在这种状态下，所有实体属性和关系都可以理解它们的位置。正确地对实际实体建模可以防止性能和逻辑问题。
The key thing is to get your model in such a state that all of the entity attributes and relationships make sense where they are. Correctly modeling your actual entities will prevent both performance and logic issues down the line.
对于您的特定问题，如果您的实体在属性（名称，联系方式）方面通常相同信息等），那么您想将它们放在同一张表中。如果它们实质上不同，则它们可能应该在不同的表中。而且，如果它们本质上不同，并且您的工作和课程也显着不同，那么绝对可以考虑为JobPosts和CoursePosts创建两个完全不同的数据模型，然后将它们链接到Posts的一些超集表中。但是，正如您可以看到的那样，从面向对象的角度来看，如果您的帖子没有什么共同点，而是唯一的密钥标识符和一些管理元数据，您甚至可能会问为什么在应用程序中混合这两个实体。
For your specific question, if your bodies are generally identical in terms of attributes (name, contact info, etc) then you want to put them in the same table. If they are substantially different, then they should probably be in different tables. And if they are substantially different, and your jobs and courses are substantially different, then definitely consider creating two entirely different data models for JobPosts versus CoursePosts and then simply linking them in some superset table of Posts. But as you can tell, from an object-oriented perspective, if your Posts have nothing in common but perhaps a unique key identifier and some administrative metadata, you might even ask why you're mixing these two entities in your application.
这篇关于数据库设计-结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
