如何通过SQL表将SQL中的实体联系起来[英] How to relate entities in SQL through SQL tables

本文是小编为大家收集整理的关于如何通过SQL表将SQL中的实体联系起来的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我是DB设计的初学者,我需要为项目创建DB. 我可以解释我想以对象为导向的术语做什么,值得庆幸的是,DB专家将足以解释我如何在DB方面处理此问题.

我想创建一个将与位置实体(州,城市)有关系的用户(ID,名称)实体.因此,在编程语言中,我想拥有以下

class User {
String Name;
Int Id;
Location location; }

class Location {
String State;
String City; }

有人可以向我解释我如何处理?

推荐答案

这取决于您的项目要求(设计)

这种关系可能是以下内容:

*用户有一个位置,位置与一个用户有关(一对一关系)

*用户有一个或更多位置,位置与一个特定的用户有关(一个与许多关系)

*用户有一个或更多位置,位置与用户(许多关系与许多关系)有关)

其他推荐答案

根据注释,您想要的是位置表和用户表之间的一对一关系.这意味着用户将只有一个和一个位置,但是可以将一个位置分配给多个用户.因此,您可以看到应该看起来的外观,我包括以下DDL脚本或"数据定义语言",这是所有数据库管理员使用的语言:

创建用户表:

CREATE TABLE [dbo].[User](
    [UserId] [int] NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [LocationId] [int] NOT NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
    [UserId] ASC
) ON [PRIMARY]
) ON [PRIMARY]

创建位置表:

CREATE TABLE [dbo].[Location](
    [LocationId] [int] NOT NULL,
    [City] [varbinary](50) NOT NULL,
    [State] [varchar](2) NOT NULL,
 CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED 
(
    [LocationId] ASC
) ON [PRIMARY]
) ON [PRIMARY]

现在在2之间创建外键(FK).一个FK告诉数据库您要在2个表之间链接数据.也就是说,您不能将用户分配到位置表中不存在的位置.这是通过ID字段完成的.

ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [FK_User_Location] FOREIGN KEY([LocationId])
REFERENCES [dbo].[Location] ([LocationId])
GO
ALTER TABLE [dbo].[User] CHECK CONSTRAINT [FK_User_Location]
GO

网络上有许多良好的资源用于学习数据库设计.您要尽早回答的一个问题是"我要如何使我的数据库进行规范化?" 数据库归一化将严重影响您的设计.

另一件事:不要让您的应用程序对象模型决定您的数据库模型应该是什么.换句话说,您不需要在应用程序对象和数据库表之间有一对一的关系.对于非常小的数据库,可能是这样的,但是使用DB设计的最佳实践,您会很快看到这种做法是不可持续的.

其他推荐答案

表代表关系AKA关系.因此,关系模型. IE表包含满足某些参数化语句的行.语句的参数是表的列.

table User on Name, Id, LocId
    // (longhand) "user identified by [Id] has name [Name] and is located in location [LocId]"
    // (shorthand) User(Name,Id,LocId)
table Location on LocId, State and City
    // (longhand) "location [LocId] is city [City] in state [State]"
    // (shorthand) "Location(LocId,State,City)

一个人通过使用和不存在的语句组合语句来获得满足其他陈述的行,而不是存在. . DBM可以为我们进行此转换.

User
    // rows satisfying User(Name,Id,LocId)
User JOIN Location
    // rows satisfying User(Name,Id,LocId) AND Location(LocId,State,City)
User PROJECT-OUT LocId
    // ie User PROJECT Name,Id
    // rows satisfying EXISTS LocId User(Name,Id)
User WHERE Name='Fred'
    // rows satisfying User(Name,Id,LocId) AND Name='Fred'

这就是表/语句的"连接"的方式:通过参数/列名和逻辑/关系运算符.

只有您可以在桌子中确定所需的行,即您要查询的语句合并.

您回答了另一个答案(对关系本身)与关系的某​​些属性,但您没有说实际是什么,这还不足查询结果满足.

请注意,这就是您需要更新和查询数据库的全部.

给定一些陈述和可能出现的情况,因此数据库只能在某些州.我们通过"约束"告诉DBMS,以防止其他状态并优化执行.例如,如果始终存在名称,ID用户(名称,ID,局部)意味着存在状态,城市位置(位置,州,城市),即用户项目locid locid <=位置项目locid locid locid,那么我们说有一个"包含在用户位置到位置的约束.如果{locid}也是位置的关键,那么从用户的位置到位置的FK也有一个FK.我重复一遍,一个人不需要查询;违反这一点的国家永远不会使用错误的语句.

您和其他评论者遭受了常见的困惑,这些混乱被教导为方法的分数或不幸的是方法是方法的实际一部分.例如,"关系"被使用,含义数据库中的表/关系中的某些约束.一个混乱的人说,用户和位置之间有" a""""多:1"关系.这实际上意味着表/关系用户项目ID,locID IE存在用户之间的名称用户(名称,ID,locID)(带有IDS的1:1)和位置(带有1:1的位置)具有属性是很多:1,即,许多用户可以位于某些数据库状态的同一位置.然后,这与从用户到位置的包含约束或FK进一步混淆.然后人们隐约认为这样的东西"连接"表:但是它们限制了表(即数据库),并且与查询无关.

阅读有关NIAM或FCO-IM或对象角色建模的信息,这些模型将告诉您如何清楚地考虑设计. (Halpin的书告诉您如何从ORM2到ER及其他映射,而不会因常见的误解而扭曲.)

本文地址:https://www.itbaoku.cn/post/597310.html

问题描述

I'm a very beginner on DB designing and I need to create a DB for a project. I can explain what I wanna do in Object Oriented terms and thankfully a DB expert would be kind enough to explain me how I can deal with this in a DB aspect .

I wanna create a User (Id, Name) entity that would have a relation with a Location entity (state, city). So in programming language i would like to have the following

class User {
String Name;
Int Id;
Location location; }

class Location {
String State;
String City; }

Could someone explain me how I can deal with this?

推荐答案

It depends on your project requirements (Designing)

The relationship might be the following :

*User has one location and Location related to one User (One To One Relationship)

*User has one location or more and Location is related to one specific user (One To Many Relationship)

*User has one location or more and Location is related to more than user (Many To Many Relationship)

其他推荐答案

Based on the comments it looks like what you want is a Many-To-One relationship between the Location table and the User table. This means that a User will have one and only one Location, but a single Location can be assigned to multiple users. So you can see how this should look, I have included the following DDL script, or "Data Definition Language", which is the language all Database Administrators use:

Create the User table:

CREATE TABLE [dbo].[User](
    [UserId] [int] NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [LocationId] [int] NOT NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
    [UserId] ASC
) ON [PRIMARY]
) ON [PRIMARY]

Create the Location table:

CREATE TABLE [dbo].[Location](
    [LocationId] [int] NOT NULL,
    [City] [varbinary](50) NOT NULL,
    [State] [varchar](2) NOT NULL,
 CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED 
(
    [LocationId] ASC
) ON [PRIMARY]
) ON [PRIMARY]

Now create the Foreign Key (FK) between the 2. A FK tells the database that you want to link data between 2 tables. That is, you cannot assign a User to a Location that does not exist in the Location table. This is done through the Id fields.

ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [FK_User_Location] FOREIGN KEY([LocationId])
REFERENCES [dbo].[Location] ([LocationId])
GO
ALTER TABLE [dbo].[User] CHECK CONSTRAINT [FK_User_Location]
GO

There are many good resources on the web for learning database design. The one question you will want to answer early is "How normalized do I want to make my database?" Database normalization will heavily influence your design.

One more thing: do not let your application object model dictate what your database model should be. In other words, you do not need to have a one-to-one relationship between your application objects and your database tables. It might be that way for very small databases, but using best practices for db design you will quickly see that it is a practice that is not sustainable.

其他推荐答案

A table represents a relationship aka relation. Hence the relational model. Ie a table contains the rows that satisfy some parameterized statement. The parameters of the statement are the columns of the table.

table User on Name, Id, LocId
    // (longhand) "user identified by [Id] has name [Name] and is located in location [LocId]"
    // (shorthand) User(Name,Id,LocId)
table Location on LocId, State and City
    // (longhand) "location [LocId] is city [City] in state [State]"
    // (shorthand) "Location(LocId,State,City)

One gets the rows satisfying other statements by combining statements using AND, OR, AND NOT, EXISTS, IMPLIES etc. You get the corresponding tables by combining tables using JOIN, UNION, MINUS, PROJECT-OUT, <= (respectively). The DBMS can do this conversion for us.

User
    // rows satisfying User(Name,Id,LocId)
User JOIN Location
    // rows satisfying User(Name,Id,LocId) AND Location(LocId,State,City)
User PROJECT-OUT LocId
    // ie User PROJECT Name,Id
    // rows satisfying EXISTS LocId User(Name,Id)
User WHERE Name='Fred'
    // rows satisfying User(Name,Id,LocId) AND Name='Fred'

That is how tables/statements are "connected": by parameter/column names and logical/relational operators.

Only you can decide what rows you want in your tables, ie what statements you want your queries combined from.

You responded to another answer (itself confused about relationships) with some properties of the relationships, but you didn't say what the relationships actually were, & that's not enough to know what goes in the tables or what statements the rows in a query result satisfy.

Notice that this is all you need to update and query a database.

Given some statements and the situations that can arise it follows that the database will only ever be in certain states. We tell the DBMS about that via "constraints" so it can prevent other states and also optimize execution. Eg if it is always the case that EXISTS Name,Id User(Name,Id,LocId) IMPLIES EXISTS State,City Location(LocId,State,City) ie that USER PROJECT LocId <= Location PROJECT LocId then we say there is an "inclusion constraint" from User's LocId to Location's. If also {LocId} is a key of Location then there is also a FK from User's LocId to Location's. I repeat, one doesn't need this to query; states violating this will never arise from using the statements except in error.

You and other commenters here suffer from common confusions which are taught as misprepresentations of methods or unfortunately are actual parts of methods. Eg "relationship" instead gets used meaning certain kinds of constraints on the tables/relationships in a database. One confusedly says there is "a" "many:1" "relationship" between users and locations. What this actually means is that the table/relationship User PROJECT Id,LocId ie EXISTS Name User(Name,Id,LocId) between users (which are 1:1 with Ids) and locations (which are 1:1 with LocIds) has the property of being many:1, ie that many users can be located in the the same location in some database states. Then this is further confused with there being an inclusion constraint or FK from Users to Locations. Then people think vaguely that such things "connect" tables: but they constraint tables (ie the database) and are irrelevant to querying.

Read about NIAM or FCO-IM or Object Role Modeling which will tell you how to think clearly about design. (Halpin's books tell you how to map from ORM2 to ER & others without warping by common misconceptions.)