database - 用 NOT NULL 识别关系还是不识别？

Question

我有一个简单的数据库模型，其中包含两个实体用户和消息。

模型：

而且我不明白它应该有什么关系。据我了解，如果没有用户就不能存在消息，则它应该具有识别关系。但是，如果我将创建与 NOT NULL FK 的非识别关系。在这种情况下是否具有相同的行为？

标签： databasedatabase-design

Answer 1

Having a mandatory relationship is not the same as having an identifying relationship.

An identifying relationship has a primary key which includes the foreign key to the parent entity type as well as one or more other predicates (columns). The exception to this would be 1:1 identifying relationships in which case the foreign key is enough to uniquely identify the child entity type.

If your message table will have its own unique identifier, independent of the foreign key to user then the relationship between the two is not an identifying relationship.

There is no hard and fast rule as to what is the "right" way to model a relationship. People have differing (and often strongly held) opinions on things like whether every table should have a meaningless, automatically generated integer primary key.

In your case, I would ask about how you would think about messages. Is a particular message the, say 2437th message sent by User A or is just the 835092nd message within your system? You could look at it either way, but what makes more sense to you based on how your system will use this data?

I would also ask, your design records who sends a message, but where does it record who receives the message? Is that known to your system or are all messages posted to the "public" (whatever that means to your system)? Does this fact have any impact on your thinking about the identifier of message?