MySQL > Should psuedokey table columns be identifying or non-identifying relationships?

Take the following database tables

|========|
|user    |
|========|
|id      |
|username|
|password|
|========|

|=========|
|blog     |
|=========|
|id       |
|date     |
|content  |
|author_id|
|=========|

blog.author_id is supposed to connected to a particular user.id, whichever user who wrote the blog entry obviously开发者_Go百科.

My question is with regards to 1:1, 1:n identifying and non-identifying relationships... I don't really understand them very much. Should this relationship be one of these types of relationships or not? And if so, which one? And what is the advantage of this?

In this example, there's a 1:1 relationship between a blog record and an author. The reason they exist as separate entities/tables is the grouping of information -- user related stuff doesn't belong with a blog record, and it could be duplicated if someone writes more than one blog.

The reason you want that implemented as a foreign key constraint is because the constraint ensures that the author for the blog record exists in the user table. Otherwise, it could be nonsense/bad data. The foreign key doesn't stop duplicates -- you'd need a primary or unique key for that -- the foreign key only validates data.

Now that Nanne clarified the identifying/non-identifying terminology for me, the blog.author_id would be the identifying relationship. Because it's identifying who (what user record) the author is.

The id column in both tables can be assumed to be be the primary key, because an artificial/surrogate key is the most common primary key. Which makes these columns the non-identifying relationship...

As a blog and a user are seperate things, and not defined by eachother, these are non-identifying relationships. One can be something without the other, evne though the author-id might be mandatory.

Also see this link for more explanation about the two terms: What's the difference between identifying and non-identifying relationships?