Thanks again for the detailed explanation. I think I now understand. Amit
On Tuesday, December 1, 2015 at 6:15:02 PM UTC+2, Simon King wrote: > > 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 <[email protected] > <javascript:>> 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 <[email protected]> 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 <[email protected]> >>>>> 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 [email protected]. >>>>>> To post to this group, send email to [email protected]. >>>>>> 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 [email protected]. >>>> To post to this group, send email to [email protected]. >>>> 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 [email protected] <javascript:>. >> To post to this group, send email to [email protected] >> <javascript:>. >> 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
