-1

Possible Duplicate:
When we need to use 1-to-1 relationship in database design?

Relational databases/SQL: What are typical use cases for one-to-one parent-child (non-inheritance, tree-like to self/super table) relationships?

Of course, this would implement/become a parent-child list instead of a tree (assuming a regular one-to-many).

Think about sports leagues, see http://en.wikipedia.org/wiki/English_football_league_system: the English Premier League (soccer) is of the same type League as its (single) child, the Football League Championship. At a lower point you can see the children "split" into Conference North and Conference South. This can only be modeled with a many-to-one relationship from League to League, resulting in a regular tree.

If you changed the described many-to-one parent-child relationship to one-to-one, you would get a list instead of a tree, much like the top part of the English soccer leagues. This is what I'm asking for. As a consequence the children of a node would become the child or next element (because of the fact that we get a list now).

I wonder when you would regularly need a list structure like that in relational databases, given that they are technically possible.

So again, what are typical use cases / examples for parent-child one-to-one relationships? (doesn't have to be a complete list, I just can't think of any real-life examples)

Note: I don't mean inheritance relationships here. These should be one-to-one at all (?) times, but that's not the question...

Edit: Note, that by "parent-child" relationships I mean foreign keys to the same (or a super) table. I forgot that "parents" and "children" are often used in false contexts. I am not about one table entity owning another table's entity via simple one-to-one relationship, which most people believe to be parent-child relationships. I'm talking about "one-to-one trees" here.

Community
  • 1
  • 1
Kawu
  • 13,647
  • 34
  • 123
  • 195
  • http://stackoverflow.com/questions/6352324/when-we-need-to-use-1-to-1-relationship-in-database-design/6362543#6362543 – Damir Sudarevic Jul 21 '11 at 12:29
  • See my edit. By parent-child I mean one-to-one FKs to the same or a super table. – Kawu Jul 21 '11 at 13:35
  • Perhaps an example of what you are talking about with sample tables and data would help people understand what you are asking. – HLGEM Jul 22 '11 at 17:35
  • this question is both unclear and, to the extent that it makes sense, overly broad and not constructive. – C. A. McCann Jul 23 '11 at 06:17
  • 1
    Even as a crusty old DBA I am finding this hard to comprehend. How about a diagram or show us a table (or tables)? – Kev Jul 23 '11 at 11:50
  • The design is **one table** consisting of one id column, plus a parent_id pointing to the parent entity via one-to-one cardinality. That's all there is to it. Do you really need an diagram for this? – Kawu Jul 25 '11 at 14:09

1 Answers1

2

In SQL for most practical purposes it is impossible (or at least extremely difficult) to use a mandatory one-to-one (bijection) constraint between two tables. SQL normally only allows you to insert to one table at a time, so the constraint is violated as soon as you insert to the first table. Usually what is meant by "one-to-one" in a SQL context is one-to- zero/one. SQL "FOREIGN KEY" constraints are always optional on one side of the constraint.

In principle, if your DBMS can handle it then such constraints could be useful wherever business rules require two distinct entities to have a one-to-one relationship. E.g. One husband, one wife. One manager per department and one department per manager.

nvogel
  • 24,981
  • 1
  • 44
  • 82
  • See my edit. By parent-child I mean one-to-one FKs to the same or a super table. – Kawu Jul 21 '11 at 13:25
  • @kawu - it would be more constructive to ask this user to "update" his answer based on your edits. – Kev Jul 23 '11 at 11:45