Well, this is a limitation of traditional object-oriented design (at least
in Python - I can't think of any other languages which would allow it
either). An object can only be an instance of a single class. In python,
objects have a "__class__" attribute which points to the class they are an
instance of, so there's no way to have an instance which is both a Doctor
and a Patient.
One solution to this problem is multiple inheritance - you could create a
new class "DoctorPatient" which inherits from both Doctor and Patient. But
I don't think SQLAlchemy supports multiple inheritance, and this approach
is generally frowned upon anyway. What happens when you add Receptionist
and Cleaner roles? Will you also add ReceptionistDoctor,
ReceptionistCleanerDoctorPatient etc. subclasses? In fact, your situation
is a classic example of why composition is preferred to inheritance for
many situations. Inheritance forces a very rigid structure on your
application, whereas composition allows much more flexibility.
I'm not sure if it was clear from my example before, but there's nothing
wrong with your database design. From an SQL point of view everything was
fine. It's only the way you were choosing to model that information in
Python that was causing the problem.
Simon
On Tue, Dec 1, 2015 at 3:55 PM, amit geron <amit.ge...@gmail.com> wrote:
> Thanks again for your answers.
>
> Although this seems like an elegant solution, I would still want to know
> is what is the reason for this limitation:
>
> "If someone can be both a Doctor and a Patient at the same time, I don't
> think 2 classes inheriting from User can really work any more" - Why?
>
> Amit
>
> On Tuesday, December 1, 2015 at 5:26:28 PM UTC+2, Simon King wrote:
>
>> If someone can be both a Doctor and a Patient at the same time, I don't
>> think 2 classes inheriting from User can really work any more. Off the top
>> of my head, I might start with something like this (completely untested,
>> and the names are horrible, but hopefully you get the idea)
>>
>> class User(Base):
>> __tablename__ = 'user'
>> id = sa.Column(...)
>> email = sa.Column(...)
>>
>> patientdata = saorm.relationship("Patient", back_populates="user")
>> doctordata = saorm.relationship("Doctor", back_populates="user")
>>
>> @property
>> def is_patient(self):
>> return (self.patientdata is not None)
>>
>> @property
>> def is_doctor(self):
>> return (self.doctordata is not None)
>>
>> class Patient(Base):
>> __tablename__ = 'patient'
>> id = sa.Column(sa.ForeignKey(User.id), primary_key=True)
>> user = saorm.relationship(User, back_populates="patientdata")
>>
>> class Doctor(Base):
>> __tablename__ = 'doctor'
>> id = sa.Column(sa.ForeignKey(User.id), primary_key=True)
>> user = saorm.relationship(User, back_populates="doctordata")
>>
>>
>> You'd need to make some changes to the way you work with these objects
>> though. For example, when creating a new Patient, you'd need to explicitly
>> create the associated User object as well.
>>
>> Hope that helps,
>>
>> Simon
>>
>> On Tue, Dec 1, 2015 at 10:34 AM, amit geron <amit....@gmail.com> wrote:
>>
>>> Hi Simon,
>>>
>>> Thanks for the reply.
>>>
>>> I will try to better explain what I was trying to do:
>>>
>>> - Created a general class User, which has a index (id) and unique
>>> attribute (email).
>>> - Created 2 sub-classes of User: Patient and Doctor, with primary
>>> foreign key User.id.
>>> - Basic assumption was that a patient is never a Dr. and vise-versa,
>>> hence if a user has registered as a Patient, he can never register as a
>>> Dr.
>>> (and vise-versa).
>>> - The latter assumption was broken with a new requirement, that a
>>> user may register with the same email both as a Dr. and a Patient.
>>> - As the tables were defined, it was possible (via MySQL
>>> commands) to add entries for an existing user in the opposite table,
>>> simply
>>> by specifying the user id upon insertion.
>>>
>>> So, the question is how can I do the last step using SQLAlchemy
>>> supported methods. If this is a design issue, please explain and advise
>>> what should be changed in order to support this structure.
>>>
>>> P.S. I changed the approach to solve this issue, but still want to gain
>>> better understanding of how to design and implement relationships). I've
>>> decided to completely separate the classes and remove the User class.
>>>
>>> On Tuesday, December 1, 2015 at 11:45:15 AM UTC+2, Simon King wrote:
>>>>
>>>> I don't understand exactly what you are trying to do here, from a
>>>> database perspective. Your table setup suggests that you are using
>>>> joined-table inheritance:
>>>>
>>>>
>>>> http://docs.sqlalchemy.org/en/rel_1_0/orm/inheritance.html#joined-table-inheritance
>>>>
>>>> ie. attributes that are common to all classes in the hierarchy live in
>>>> the base table, and attributes that are specific to one of the subclasses
>>>> live in a subclass-specific table. So when you have a user of type A, with
>>>> id 1, the common attributes will be in the user table with id 1, and the
>>>> A-specific attributes will be in the A table, again with id 1.
>>>>
>>>> If that's really what you've got, then it doesn't make any sense to
>>>> create a B object with an id of 1, since half of its identity would be the
>>>> same row as your A instance. I don't think SQLAlchemy will allow that.
>>>>
>>>> There are other inheritance patterns that might make sense for your
>>>> situation, or if you are trying to share state between and A and a B
>>>> instance then you probably don't want inheritance at all, but a shared
>>>> relationship instead. If you can tell us what you are trying to do, we
>>>> might be able to suggest an approach.
>>>>
>>>> Simon
>>>>
>>>> On Mon, Nov 30, 2015 at 4:46 PM, amit geron <amit....@gmail.com> wrote:
>>>>
>>>>> As I already mentioned, I tried your suggestion but with no success.
>>>>> The names are unique anyway, and I don't understand the how it's related
>>>>> to
>>>>> my question..
>>>>>
>>>>> Could you please provide a working example that will demonstrate how 2
>>>>> objects inherit from the same class, and hold the same primary key that is
>>>>> a primary foreign key of the derived class?
>>>>>
>>>>>
>>>>> On Monday, November 30, 2015 at 5:59:38 PM UTC+2, Jonathan Vanasco
>>>>> wrote:
>>>>>>
>>>>>> It should still work as a reference because the pacakge you use
>>>>>> doesn't override this.
>>>>>>
>>>>>> The extension's API makes this clear:
>>>>>> http://flask-sqlalchemy.pocoo.org/2.1/api/#models
>>>>>>
>>>>>>
>>>>>> _tablename__
>>>>>> <http://flask-sqlalchemy.pocoo.org/2.1/api/#flask.ext.sqlalchemy.Model.__tablename__>
>>>>>>
>>>>>> The name of the table in the database. This is required by
>>>>>> SQLAlchemy; however, Flask-SQLAlchemy will set it automatically if a
>>>>>> model
>>>>>> has a primary key defined. If the __table__ or __tablename__ is set
>>>>>> explicitly, that will be used instead.
>>>>>>
>>>>> --
>>>>> You received this message because you are subscribed to the Google
>>>>> Groups "sqlalchemy" group.
>>>>> To unsubscribe from this group and stop receiving emails from it, send
>>>>> an email to sqlalchemy+...@googlegroups.com.
>>>>> To post to this group, send email to sqlal...@googlegroups.com.
>>>>> Visit this group at http://groups.google.com/group/sqlalchemy.
>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>
>>>>
>>>> --
>>> You received this message because you are subscribed to the Google
>>> Groups "sqlalchemy" group.
>>> To unsubscribe from this group and stop receiving emails from it, send
>>> an email to sqlalchemy+...@googlegroups.com.
>>> To post to this group, send email to sqlal...@googlegroups.com.
>>> Visit this group at http://groups.google.com/group/sqlalchemy.
>>> For more options, visit https://groups.google.com/d/optout.
>>>
>>
>> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
