Hi Conor and rest of the list...
Well... I'm afraid I need to keep it separated. In the real model each
child is a list that is accessed in different ways (and do different
things) depending on which child it is.
I was wondering what it would be better (more correct) from a
"relational" point of view:
Keeping the ids of the children in the parent (as foreign keys) or
having the id of the parent in the children. I guess from a relational
point of view it's more correct having the id of the parent in the
children, right?
So I think it's better if I go with Conor's last solution:
child1 = relationship(
"Child",
primaryjoin=lambda: and_(Child.parent_id == Parent.id, Child.type == 1),
uselist=False)
child2 = relationship(
"Child",
primaryjoin=lambda: and_(Child.parent_id == Parent.id, Child.type == 2),
uselist=False)
I guess the relationship in this case is a 1:N (1 parent has 2
children) and it sounds "right" to keep the id of the parent in the
children, and not viceversa.
I also asked the same question in stackoverflow
(http://stackoverflow.com/questions/4055332/relational-database-design-two-relations-11-or-one-12),
and someone said it's more correct this last approach (of keeping the
parent_id in the children and distinguish the type of the children
through a Child.type field).
2010/10/29 Conor <[email protected]>:
> On 10/29/2010 11:51 AM, Hector Blanco wrote:
>
> Thanks Conor!
>
> The callable works like a charm! It's great news! (I've been trying to
> figure out this for 3 days... yeah... I guess I'm not that smart)
>
> Now that I have it working, a “design” question pops up. Nothing
> technical, really.
>
> As Connor mentioned in his reply:
> “Usually we consider the table with the foreign key as the "child"
> table, but that's just being picky”
>
> That's very true, and now I don't know how to design it...
>
> It would be easier to design it if we had more concrete names instead of
> "Parent" and "Child". What is the actual use case? Is this a tree hierarchy?
> Does each parent have exactly two children?
>
> I can do it the way I asked or...
>
> 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 “id”
> # AND type == 1
> # I'll deal with the joins and that depending on your answer,
> guys
> )
>
> child2 = relationship(
> # Would be same as above
> # AND 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 asked in my former question would imply creating a new
> relationship AND adding a new foreign key to the parent.
>
> I'd like to know what people that know much more about databases think :)
>
> I'm confused as to why you would want separate "child1", "child2", etc.
> relationships instead of a single "children" relationship. Is Child.type
> really something you want for distinguishing children, or is it something
> you added to try and make the relationships work?
>
> Assuming you really do want to keep separate "child1" and "child2"
> relationships, and they are both one-to-one relationships, they would look
> like this:
>
> # omit uselist=False if this is a one-to-many relationship
> child1 = relationship(
> "Child",
> primaryjoin=lambda: and_(Child.parent_id == Parent.id, Child.type == 1),
> uselist=False)
> child2 = relationship(
> "Child",
> primaryjoin=lambda: and_(Child.parent_id == Parent.id, Child.type == 2),
> uselist=False)
>
> -Conor
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
