CODEWITHSUNDEEP
databasedatabase-design
Dev9567485
Dev9567485

Reputation: 55

Identifying relationship or non-identifying with NOT NULL?

I have a simple database model with two entities User and Message.

model:
model

And I don't understand what relation it should have. As I understand, if message can't exist without user it should have identifying relationship. But if I will create non-identifying relationship with NOT NULL FK. Is it have the same behavior in that case?

Upvotes: 0

Views: 230

Answers (1)

Joel Brown
Joel Brown

Reputation: 14398

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?

Upvotes: 1

Related Questions