Database design: please help me to understand relationship types

I'm designing a database for a social networ开发者_运维问答k website with DBDesigner Fork and I need help to understand the relationship types... I'm getting really confused about which type I should use in each situation.

These are the types: 1:1, 1:n, 1:n (non-identifying), n:m, 1:1 (descendent obj.), 1:1 (non-identifying)

Could you give me a brief explanation and a pratical example in each case?

---

There are three basic types that directly correlate to the databases themselves:

• 1:1 - One to one
• 1:n - One to n
• n:m - Many to many

And really, those in turn boil down to two one question - can the foreign key exist in the child table (1:*), or do you need an intermediate table (n:m).

One to one is straight forward. It's typically used for sub-typing. Given the two tables:

person
    id int NOT NULL
    name varchar(255) NOT NULL


parent
    id int NOT NULL
    person_id int NOT NULL
    spouse_id int NULL

There are two relationships - a 1:1 identifying (a parent IS a person), and non-identifying (a parent may have a spouse). Now, taking it a step further:

children
    person_id int NOT NULL
    parent_id int NOT NULL

The 'children' table is a way of mapping 'parents' to the 'person' table to associate the child-to-parent, many-to-many relationship.

Also, a 'parent', in this example, would be a descendent object of 'person' - in that it extends person. Most descendent object relationships would be non-identifying.

---

Look here to get a better idea on how relationship types work as this will probably give you a pretty good explination on the topic as well as a better understanding of how the entity-relationship model works.