Relational database design - Two Relations 1:1 or one 1:2 ?

Question

Hello everyone!

This question is about how to design a SQL relationship. I am pretty newbie in this matter and I'd like to know the answers of (way) more experts guys...

I am currently migrating a ZopeDB (Object oriented) database to MySQL (relational) using MeGrok and SqlAlchemy (although I don't think that's really too relevant, since my question is more about designing a relationship in a relational database).

I have two classes related like this:

class Child(object):
    def __init__(self):
        self.field1 = "hello world"

class Parent(object):
    def __init__(self):
        self.child1 = Child()
        self.child2 = Child()

The "Parent" class has two different instances of a Child() class. I am not even sure about how to treat this (two different 1:1 relationships or a 1:2 relationship).

Currently, I have this:

class Child(rdb.Model):
    rdb.metadata(metadata)
    rdb.tablename("children_table")
    id = Column("id", Integer, primary_key=True)
    field1 = Column("field1", String(64))   #Irrelevant
    def __init__(self):
        self.field1 = "hello world"

class Parent(rdb.Model):
    rdb.metadata(metadata)
    rdb.tablename("parent_table")

    id = Column("id", Integer, primary_key=True)
    child1_id = Column("child_1_id", Integer, ForeignKey("children_table.id"))
    child2_id = Column("child_2_id", Integer, ForeignKey("children_table.id"))

    child1 = relationship(Child,
        primaryjoin = ("parent_table.child1_id == children_table.id")
    )

    child2 = relationship(Child,
        primaryjoin = ("parent_table.child2_id == children_table.id")
    ) 

Meaning... Ok, I store the two "children" ids as foreign keys in the Parent and retrieve the children itself using that information.

This is working fine, but I don't know if it's the most proper solution.

Or I could do something like:

class Child(rdb.Model):
    rdb.metadata(metadata)
    rdb.tablename("children_table")
    id = Column("id", Integer, primary_key=True)
    parent_id = Column("id", Integer, ForeignKey("parent_table.id"))  # New!
    type = Column("type", ShortInteger) # New!

    field1 = Column("field1", String(64))  #Irrelevant
    def __init__(self):
        self.field1 = "hello world"

class Parent(rdb.Model):
    rdb.metadata(metadata)
    rdb.tablename("parent_table")

    id = Column("id", Integer, primary_key=True)
    child1 = relationship(
        # Well... this I still don't know how to write it down,
        # but it would be something like:
        #   Give me all the children whose "parent_id" is my own "id"
        #   AND their type == 1
        # I'll deal with the joins and the actual implementation depending 
        # on your answer, guys
    )

    child2 = relationship(
        #  Would be same as above
        #  but selecting children whose type == 2
    )

This may be good for adding new children to the parent class... If I add a "Parent.child3", I just need to create a new relationship very similar to the already existing ones.

The way I have it now would imply creating a new relationship AND adding a new foreign key to the parent.

Also, having a "parent" table with a bunch of foreign keys may not make it the best "parent" table in the world, right?

I'd like to know what people that know much more about databases think :)

Thank you.

PS: Related post? Question 3998545

Answer 1

This is probably a little out of context, since I use none of the things you've mentioned - but as far as the general design goes, here are a couple ideas:

1. Keep relationships based on common types: has_one, has_many, belongs_to, has_and_belongs_to_many.
2. With children, it's better to not specify N number of children explicitly; either there are none, one, or there could potentially be many. Thus your model declarations of child1 and child2 would be replaced by a single property - an array containing children.

To be totally honest, I don't know how well that fits in with what you're using. However, that's generally how relationships work in an ORM sense. So, based on this:

1. If a model belongs to another (it has a foreign key for another table), it would have a parent [sic] property with a reference to the parent object
2. If a model has one model that belongs to it (the other model has a foreign key to the first model's table), it would have a child [sic] property with a reference to the child object
3. If a model has many models that belong to it (many other models have foreign keys to the first model's table), it would have a children [sic] property that is an array of references to child objects
4. If a model has and belongs to many other models... you might want to consider using both parents and children properties, or something similar; nomenclature is less important than you having access to a group of models that it belongs to, and another group of models that belong to it.

Sorry if that's totally unhelpful, but it might shed some light. HTH.

Answer 2

I'll admit that I'm not too familiar with object databases, but in relational terms this is a straightforward one-to-many (optional) relationship.

create table parent (
  id           int PK,
  otherField   whatever
)

create table child (
  id           int PK,
  parent_id    int Fk,
  otherField   whatever
)

Obviously, that's not usable code as it stands....

I think this is similar to your second example. If you need to track the ordinal postion of the children in their relationships to the parent, you'd add a column to the child table such as:

create table child (
  id           int PK,
  parent_id    int Fk,
  birth_order  int,
  otherField   whatever
)

You'd have to be responsible for managing that field at teh application level, it's not something you can expect the DBMS to do for you.

I called it an optional relationship on the assumption that childless parents can exist--if that's not true, it becomes a required relationship logically, though you'd still have to let the DBMS create a new parent record childlessly, then grab its id to create the child--and once again manage the requirement at the application level.

Answer 3

Expanded in Response to Comments

The issue is, you are thinking in the terms that you know (understandable), and you have the limitations of an OO database ... which would not be good to carry over into the Relational db. So for many reasons, it is best to simply identify the Entities and Relations, and to Normalise them. The method you use to call is easy to change and you will not be limited to only what you have now.

There are some good answers here, but even those are limited and incomplete. If you Normalise Parent and Child (being people, they will have many common columns), you get Person, with no duplicated columns.

People have "upward" relations to other people, their Parents, but that is context, not the fact that the Parent exists as a Person first (and you can have more than two if you like). People also have "downward" relations to their Children, also contextual. The limitation of two children per Parent is absurd (you may have to inspect your methods/classes: I suspect one is an "upward" navigation and the other is "downward"). And you do not want to have to store the relations as duplicates (once that Fred is a father of Sally; twice that Sally is a child of Fred), that single fact exists in a single row, which can be interpreted Parent⇢Child or Parent⇠Child.

This requirement has come up in many questions, therefore I am using a single generic, but detailed, illustration. The model defines any tree structure that needs to be walked up or down, handled by simple recursion. It is called a Bill of Materials structure, originally created for inventory control systems, and can be applied to any tree structure requirement. It is Fifth Normal Form; no duplicate columns; no Update Anomalies.

Bill of Materials

For Assemblies and Components, which would have many common columns, they are Normalised into Part; whether they are Assemblies or Components is contextual, and these contextual columns are located in the Associative (many-to-many) table.

Two Relations 1:1 or one 1:2 ?

Actually, it is two times 1::n.

Ordinals, or Ranking, is explicit in the Primary Key (chronological order). If some other ordinal is required, simply add a column to the Associative table. better yet, it is truly a derived column, so compute it at runtime from current values.