2

I'm designing a database for a social network 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?

Rubia Gardini
  • 815
  • 5
  • 16
  • 30
  • see http://stackoverflow.com/questions/762937/whats-the-difference-between-identifying-and-non-identifying-relationships – Luc M Aug 19 '11 at 18:10

2 Answers2

4

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.

Doug Kress
  • 3,537
  • 1
  • 13
  • 19
  • So, if I have a table like "Impressions" with fields like ID, ImpressionID, UserID, PostID and other table "ImpressionsList", the relationship between "Impressions" and "ImpressionsList" would be 1:n identifying? – Rubia Gardini Aug 21 '11 at 16:30
  • Yes - however, I think I would rename `ImpressionID` to `ImpressionListID` for clarification, assuming that it was meant to be the foreign key for the `ImpressionList` table. Also, I would phrase the statement the other way: `ImpressionList` and `Impressions` would be 1:n identifying, as there are more than one `Impression` for each `ImpressionList`. – Doug Kress Aug 21 '11 at 17:19
0

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.

James213
  • 957
  • 5
  • 31
  • 57