database 逻辑数据模型和概念数据模型有什么区别？

What is the difference between logical data model and conceptual data model?

提问by Amer

What is the difference between logical data model and conceptual data model?

逻辑数据模型和概念数据模型有什么区别？

回答by veljkoz

In the conceptual data model you worry only about the high level design - what tables should exist and the connections between them. In this phase you recognize entities in your model and the relationships between them.

在概念数据模型中，您只关心高级设计——应该存在哪些表以及它们之间的连接。在此阶段，您将识别模型中的实体以及它们之间的关系。

The logical model comes after the conceptual modeling when you explicitly define what the columns in each table are. While writing the logical model, you might also take into consideration the actual database system you're designing for, but only if it affects the design (i.e., if there are no triggers you might want to remove some redundancy column etc.)

当您明确定义每个表中的列是什么时，逻辑模型出现在概念建模之后。在编写逻辑模型时，您可能还会考虑您正在设计的实际数据库系统，但前提是它会影响设计（即，如果没有触发器，您可能想要删除一些冗余列等）

There is also physical model which elaborates on the logical model and assigns each column with it's type/length etc.

还有一个物理模型，它详细说明了逻辑模型并为每列分配了它的类型/长度等。

Hereis a good table and picture that describes each of the three levels.

这是一个很好的表格和图片，描述了三个级别中的每一个。

|----------------------|------------|---------|----------|
| Feature              | Conceptual | Logical | Physical | 
|----------------------|------------|---------|----------|
| Entity Names         | X          | X       |          |
| Entity Relationships | X          | X       |          |
| Attributes           |            | X       |          |
| Primary Keys         |            | X       | X        |
| Foreign Keys         |            | X       | X        |
| Table Names          |            |         | X        |
| Column Names         |            |         | X        |
| Column Data Types    |            |         | X        |
|----------------------|------------|---------|----------|

回答by Emiliano Sangoi

In this table you can see the difference between each model:

在此表中，您可以看到每个型号之间的差异：

See http://www.1keydata.com/datawarehousing/data-modeling-levels.htmlfor more information and some data model examples.

有关更多信息和一些数据模型示例，请参阅http://www.1keydata.com/datawarehousing/data-modeling-levels.html。

回答by nvogel

These terms are unfortunately overloaded with several possible definitions. According to the ANSI-SPARC "three schema" model for instance, the Conceptual Schema or Conceptual Model consists of the set of objects in a database (tables, views, etc) in contrast to the External Schema which are the objects that users see.

不幸的是，这些术语被多种可能的定义所覆盖。例如，根据 ANSI-SPARC“三模式”模型，概念模式或概念模型由数据库中的一组对象（表、视图等）组成，而外部模式则是用户看到的对象。

In the data management professions and especially among data modellers / architects, the term Conceptual Model is frequently used to mean a semanticmodel whereas the term Logical Model is used to mean a preliminary or virtual database design. This is probably the usage you are most likely to come across in the workplace.

在数据管理专业中，尤其是在数据建模师/架构师中，术语概念模型经常用于表示语义模型，而术语逻辑模型用于表示初步或虚拟数据库设计。这可能是您在工作场所最有可能遇到的用法。

In academic usage and when describing DBMS architectures however, the Logical level means the database objects (tables, views, tables, keys, constraints, etc), as distinct from the Physical level (files, indexes, storage). To confuse things further, in the workplace the term Physical model is often used to mean the design as implemented or planned for implementation in an actual database. That may include both "physical" and "logical" level constructs (both tables and indexes for example).

然而，在学术用途和描述 DBMS 架构时，逻辑级别是指数据库对象（表、视图、表、键、约束等），与物理级别（文件、索引、存储）不同。更令人困惑的是，在工作场所，术语物理模型通常用于表示在实际数据库中实施或计划实施的设计。这可能包括“物理”和“逻辑”级别的构造（例如表和索引）。

When you come across any of these terms you really need to seek clarification on what is being described unless the context makes it obvious.

当您遇到这些术语中的任何一个时，您确实需要就所描述的内容寻求澄清，除非上下文使其显而易见。

For a discussion of these differences, check out Data Modelling Essentials by Simsion and Witt for example.

有关这些差异的讨论，请查看 Simsion 和 Witt 的 Data Modeling Essentials 例如。

回答by jose

Logical Database Model

逻辑数据库模型

Logical database modeling is required for compiling business requirements and representing the requirements as a model. It is mainly associated with the gathering of business needs rather than the database design. The information that needs to be gathered is about organizational units, business entities, and business processes.

需要逻辑数据库建模来编译业务需求并将需求表示为模型。它主要与业务需求的收集相关，而不是与数据库设计相关。需要收集的信息是关于组织单位、业务实体和业务流程的。

Once the information is compiled, reports and diagrams are made, including these:

信息汇总后，将制作报告和图表，其中包括：

ERD–Entity relationship diagram shows the relationship between different categories of data and shows the different categories of data required for the development of a database. Business process diagram–It shows the activities of individuals within the company. It shows how the data moves within the organization based on which application interface can be designed. Feedback documentation by users.

ERD-Entity关系图展示了不同类别数据之间的关系，展示了开发数据库所需的不同类别数据。业务流程图——它显示了公司内个人的活动。它显示了数据如何根据可以设计的应用程序界面在组织内移动。用户反馈文档。

Logical database models basically determine if all the requirements of the business have been gathered. It is reviewed by developers, management, and finally the end users to see if more information needs to be gathered before physical modeling starts.

逻辑数据库模型基本上确定是否已经收集了业务的所有需求。它由开发人员、管理人员和最终用户进行，以查看是否需要在物理建模开始之前收集更多信息。

Physical Database Model Physical database modeling deals with designing the actual database based on the requirements gathered during logical database modeling. All the information gathered is converted into relational models and business models. During physical modeling, objects are defined at a level called a schema level. A schema is considered a group of objects which are related to each other in a database. Tables and columns are made according to the information provided during logical modeling. Primary keys, unique keys, and foreign keys are defined in order to provide constraints. Indexes and snapshots are defined. Data can be summarized, and users are provided with an alternative perspective once the tables have been created.

物理数据库模型 物理数据库建模处理基于逻辑数据库建模期间收集的需求设计实际数据库。收集到的所有信息都转换为关系模型和业务模型。在物理建模期间，对象在称为模式级别的级别上定义。模式被认为是数据库中彼此相关的一组对象。表和列是根据逻辑建模期间提供的信息制作的。定义主键、唯一键和外键以提供约束。定义了索引和快照。可以对数据进行汇总，一旦创建了表格，用户就会获得另一种视角。

Physical database modeling depends upon the software already being used in the organization. It is software specific. Physical modeling includes:

物理数据库建模取决于组织中已经使用的软件。它是特定于软件的。物理建模包括：

Server model diagram–It includes tables and columns and different relationships that exist within a database. Database design documentation. Feedback documentation of users.

服务器模型图 - 它包括表和列以及数据库中存在的不同关系。数据库设计文档。用户反馈文档。

Summary:

概括：

1.Logical database modeling is mainly for gathering information about business needs and does not involve designing a database; whereas physical database modeling is mainly required for actual designing of the database. 2.Logical database modeling does not include indexes and constraints; the logical database model for an application can be used across various database software and implementations; whereas physical database modeling is software and hardware specific and has indexes and constraints. 3.Logical database modeling includes; ERD, business process diagrams, and user feedback documentation; whereas physical database modeling includes; server model diagram, database design documentation, and user feedback documentation.

1.逻辑数据库建模主要是收集业务需求信息，不涉及数据库设计；而物理数据库建模主要用于数据库的实际设计。2.逻辑数据库建模不包含索引和约束；应用程序的逻辑数据库模型可用于各种数据库软件和实现；而物理数据库建模是特定于软件和硬件的，并且具有索引和约束。3.逻辑数据库建模包括；ERD、业务流程图和用户反馈文档；而物理数据库建模包括；服务器模型图、数据库设计文档和用户反馈文档。

Read more: Difference Between Logical and Physical Database Model | Difference Between | Logical vs Physical Database Model http://www.differencebetween.net/technology/software-technology/difference-between-logical-and-physical-database-model/#ixzz3AxPVhTlg

阅读更多：逻辑和物理数据库模型之间的区别 | 之间的区别 | 逻辑与物理数据库模型http://www.differencebetween.net/technology/software-technology/difference-between-logical-and-physical-database-model/#ixzz3AxPVhTlg

回答by Bob

I need to produce both a logical model and a conceptual model. All the explanations here are really vague. The link posted above just shows the difference being that a conceptual model is a logical model without fields. Ok fine, I don't mention the name of the database. It appears to be totally redundant.

我需要生成一个逻辑模型和一个概念模型。这里的所有解释都非常含糊。上面发布的链接只是显示了不同之处在于概念模型是没有字段的逻辑模型。好吧，我没有提到数据库的名称。这似乎是完全多余的。

I really don't know what 'semantic' means. can someone explain what I would do differently using 'english' and possibly post a link to better examples than a picture that shows one picture that has fields and one that does not. The buzzwords are all well and good, but its so vague its not useful to practically implement.

我真的不知道“语义”是什么意思。有人可以使用“英语”来解释我会做什么不同的事情，并且可能会发布一个链接到更好的示例，而不是显示一张有字段的图片和一张没有字段的图片。流行语都很好，但它太模糊了，实际上没有用。

do I do anything other than take my logical model (which is basically my physical model reversed engineered out of the DB, click a button in said tools and the images look a little different and then take off the data types).

除了采用我的逻辑模型（这基本上是我的物理模型从数据库中逆向工程，单击所述工具中的一个按钮，图像看起来有点不同，然后去掉数据类型）之外，我还能做什么吗？

From what i can practically see (and without buzzwords)

从我几乎可以看到的（并且没有流行语）

physical model: actually tables. The little pictures have data types in them and named pk/fk constraints Logical Model: click the little button my tool (using Oracles SQL Developer Data Modeller, I dont have an erwin license and 2010 visio no longer reverse engineers out of the DB), and then the images on the screen change slightly. The data types are gone and the names of the constraints are gone, then the colors of the table representations changes to purple (so now I call them entities).

物理模型：实际上是表。小图里面有数据类型，命名为pk/fk约束逻辑模型：点击小按钮我的工具（使用Oracles SQL Developer Data Modeller，我没有erwin license，2010 visio不再逆向工程师出DB），然后屏幕上的图像略有变化。数据类型消失了，约束的名称消失了，然后表表示的颜色变为紫色（所以现在我称它们为实体）。

ok. so what would my Conceptual model look like other then: exact same thing as my logical model minus the fields. I would think there is more to it than this. Reciting that its a 'semantic' representation of data sounds real nice and fancy, but doesn't make sense to someone who has not made one of these before.

好的。那么我的概念模型会是什么样子：与我的逻辑模型完全相同，减去字段。我认为还有比这更多的东西。背诵它对数据的“语义”表示听起来非常好和花哨，但对于以前没有做过其中之一的人来说没有意义。

回答by Chris

Conceptual Schema- covers entities and relationships. Should be created first. Contrary to some of the other answers; tables are not defined here. For example a 'many to many' table is not included in a conceptual data model but is defined as a 'many to many' relationship between entities.

概念架构- 涵盖实体和关系。应该先创建。与其他一些答案相反；此处未定义表。例如，“多对多”表不包含在概念数据模型中，但被定义为实体之间的“多对多”关系。

Logical Schema- Covers tables, attributes, keys, mandatory role constraints, and referential integrity with no regards to the physical implementation. Things like indexes are not defined, attribute types should be kept logical, e.g. text instead of varchar2. Should be created based on the conceptual schema.

逻辑架构- 涵盖表、属性、键、强制性角色约束和参照完整性，与物理实现无关。索引之类的东西没有定义，属性类型应该保持逻辑，例如文本而不是 varchar2。应该基于概念模式创建。

回答by alann9

This is an old question and maybe this comes way too late, but I don't see one very important aspect necessary to answering the question. That is, the TARGET audience for the data model. The Conceptual Data Model is the model generated from business analysis, from interviews with the BUSINESS about their data. It is not so much "high level" as it is the business's understanding of their data, business rules captured in the relationships between "candidate" entities. At this point, you are capturing the things of importance to the business (Employee, Customer, Contract, Account, etc.) and the relationships between them. The final Conceptual Data Model may be somewhat abstract -- for instance, treating Individuals and Organizations entering into a contract as subtypes of a "Party", Contractors and Permanent Employees as subtypes of an Employee, even Employees and Customers subtypes of "Person" -- but it is a document that a data modeler develops from discussions with the business SMEs and presents to the business for validation.

这是一个老问题，也许这来得太晚了，但我认为回答这个问题没有一个非常重要的方面。即，数据模型的 TARGET 受众。概念数据模型是从业务分析中生成的模型，来自与业务部门就其数据进行的访谈。与其说是“高级”，不如说是业务对其数据的理解，以及在“候选”实体之间的关系中捕获的业务规则。此时，您正在捕获对业务重要的事物（员工、客户、合同、帐户等）以及它们之间的关系。最终的概念数据模型可能有些抽象——例如，将签订合同的个人和组织视为“一方”的子类型，

The Logical Data Model is not just "more detail" -- where useful and important, a Conceptual Data Model may well have attributes included -- it is the ARCHITECTURE document, the model that is presented to the software analysts/engineers to explain and specify the data requirements. It will resolve many-to-many relationships to association tables and will define all attributes, with examples and constraints, so that code can be written against the architecture.

逻辑数据模型不仅仅是“更多细节”——在有用和重要的地方，概念数据模型很可能包含属性——它是架构文档，该模型呈现给软件分析师/工程师以解释和指定数据要求。它将解决关联表的多对多关系，并定义所有属性，包括示例和约束，以便可以针对架构编写代码。

The Physical model is that Logical Model generated specifically for a particular environment, such as SQL Server or Teradata or Oracle or whatever. It will have keys, indexes, partitions, or whatever is needed to implement, based on sizing, access frequency, security constraints, etc.

物理模型是专门为特定环境生成的逻辑模型，例如 SQL Server 或 Teradata 或 Oracle 或其他任何东西。它将具有键、索引、分区或任何需要实现的东西，基于大小、访问频率、安全约束等。

So, if you are being asked to develop a Conceptual Data Model, you are being asked to design the solution (or part of it) from scratch, getting your information from the business. There's more to it, but I hope that answers the question.

因此，如果您被要求开发概念数据模型，您将被要求从头开始设计解决方案（或其中的一部分），从业务中获取您的信息。还有更多，但我希望能回答这个问题。

回答by serge

First of all, a datamodel is an abstraction tool and a databasemodel (or scheme/diagramm) is a modeling result.

首先，数据模型是抽象工具，数据库模型（或方案/图）是建模结果。

Conceptual data model is DBMS-independent and covers functional/domain design area. The most known conceptual data model is "Entity-Relationship". Normally, you can reuse the conceptual scheme to produce different logical schemes not only relational.

概念数据模型独立于 DBMS，涵盖功能/领域设计领域。最著名的概念数据模型是“实体-关系”。通常，您可以重用概念方案来生成不同的逻辑方案，而不仅仅是关系方案。

Logical data model is intended to be implemented by some DBMS and corresponds mostly to the conceptual level of ANSI/SPARC architecture(proposed in 1975); this point gives some collisions of terminology. Zachman Frameworktried to resolve this kind of collision ten years later introducing conceptual, logical and physical models.

逻辑数据模型旨在由某些 DBMS 实现，主要对应于ANSI/SPARC 体系结构的概念级别（1975 年提出）；这一点造成了一些术语上的冲突。十年后，Zachman 框架试图通过引入概念、逻辑和物理模型来解决这种冲突。

There are many logical data models, and the most known is relational one.

逻辑数据模型有很多，最著名的是关系模型。

So main differences of conceptual data model are the focusing on the domain and DBMS-independence whereas logical data model is the most abstract level of concrete DBMS you plan to use. Note that contemporary DBMS support several logical models at the same time.

因此，概念数据模型的主要区别在于关注领域和 DBMS 独立性，而逻辑数据模型是您计划使用的具体 DBMS 的最抽象级别。请注意，当代 DBMS 同时支持多个逻辑模型。

You can also have a look to my bookand to the articlefor more details.

您还可以查看我的书和文章以了解更多详细信息。

回答by Jose Araujo

Most answers here are strictly related to notations and syntax of the data models at different levels of abstraction. The key difference has not been mentioned by anyone. Conceptual models surface concepts. Concepts relate to other concepts in a different way that an Entity relates to another Entity at the Logical level of abstraction. Concepts are closer to Types. Usually at Conceptual level you display Types of things (this does not mean you must use the term "type" in your naming convention) and relationships between such types. Therefore, the existence of many-to-many relationships is not the rule but rather the consequence of the relationships between type-wise elements. In Logical Models Entities represent one instance of that thing in the real world. In Conceptual models it is not expected the description of an instance of an Entity and their relationships but rather the description of the "type" or "class" of that particular Entity. Examples: - Vehicles have Wheels and Wheels are used in Vehicles. At Conceptual level this is a many-to-many relationship - A particular Vehicle (a car by instance), with one specific registration number have 5 wheels and each particular wheel, each one with a serial number is related to only that particular car. At Logical level this is a one-to-many relationship.

这里的大多数答案都与不同抽象级别的数据模型的符号和语法严格相关。任何人都没有提到关键的区别。概念模型表面概念。概念以不同的方式与其他概念相关，即一个实体在逻辑抽象级别与另一个实体相关。概念更接近于类型。通常在概念级别，您会显示事物的类型（这并不意味着您必须在命名约定中使用术语“类型”）以及此类类型之间的关系。因此，多对多关系的存在不是规则，而是类型元素之间关系的结果。在逻辑模型中，实体代表现实世界中该事物的一个实例。在概念模型中，不期望实体实例及其关系的描述，而是该特定实体的“类型”或“类”的描述。示例： - 车辆有轮子，轮子用于车辆。在概念层面，这是一种多对多关系 - 具有一个特定注册号的特定车辆（例如一辆汽车）有 5 个车轮，每个特定车轮，每个具有序列号的车轮仅与该特定汽车相关。在逻辑级别，这是一对多的关系。在概念层面，这是一种多对多关系 - 具有一个特定注册号的特定车辆（例如一辆汽车）有 5 个车轮，每个特定车轮，每个具有序列号的车轮仅与该特定汽车相关。在逻辑级别，这是一对多的关系。在概念层面，这是一种多对多关系 - 具有一个特定注册号的特定车辆（例如一辆汽车）有 5 个车轮，每个特定车轮，每个具有序列号的车轮仅与该特定汽车相关。在逻辑级别，这是一对多的关系。

Conceptual covers "types/classes". Logical covers "instances".

概念涵盖“类型/类”。逻辑涵盖“实例”。

I would add another comment about databases. I agree with one of the colleagues who commented above that Conceptual and Logical models have absolutely nothing about databases. Conceptual and Logical models describe the real world from a data perspective using notations such as ER or UML. Database vendors, smartly, designed their products to follow the same philosophy used to logically model the World and them created Relational Databases, making everyone's lifes easier. You can describe your organisation's data landscape at all the levels using Conceptual and Logical model and never use a relational database.

我会添加关于数据库的另一条评论。我同意上面评论的一位同事的观点，即概念和逻辑模型与数据库完全无关。概念模型和逻辑模型使用 ER 或 UML 等符号从数据角度描述现实世界。数据库供应商巧妙地将他们的产品设计为遵循用于对世界进行逻辑建模的相同理念，并且他们创建了关系数据库，使每个人的生活更轻松。您可以使用概念和逻辑模型在所有级别描述组织的数据格局，而不要使用关系数据库。

Well I guess this is my 2 cents...

好吧，我想这是我的 2 美分...

回答by user3216748

logical data model

逻辑数据模型

A logical data model describes the data in as much detail as possible, without regard to how they will be physical implemented in the database. Features of a logical data model include: · Includes all entities and relationships among them. · All attributes for each entity are specified. · The primary key for each entity is specified. · Foreign keys (keys identifying the relationship between different entities) are specified. · Normalization occurs at this level. conceptual data model

逻辑数据模型尽可能详细地描述数据，而不考虑它们在数据库中的物理实现方式。逻辑数据模型的特征包括： · 包括所有实体和它们之间的关系。· 指定每个实体的所有属性。· 指定了每个实体的主键。· 指定外键（标识不同实体之间关系的键）。· 标准化发生在这个级别。概念数据模型

A conceptual data model identifies the highest-level relationships between the different entities. Features of conceptual data model include: · Includes the important entities and the relationships among them. · No attribute is specified. · No primary key is specified.

概念数据模型标识不同实体之间的最高级别关系。概念数据模型的特点包括： · 包含重要的实体以及它们之间的关系。· 没有指定属性。· 没有指定主键。