Philip Posted August 15, 2013 Share Posted August 15, 2013 Hi all, Just wondering if anybody would have a suggestion for this. I'm new to Yii but have been coding in PHP for quite some time. I've been tasked to rewrite an old application in work which has become a mess over time. It is written in loosely object oriented PHP and uses MySQL. I've recently bought the Yii Book and is seems very good so far. I do have one question and I've tried searching around for similar questions, but can't seem to get a definite answer. Basically the database is set up so that there is 'person' table which includes person_id, names and dates etc. Then there are two other tables called student and employee. Basically the PHP classes for those tables inherit from Person, as Student and Employee are substantially different. In the book (I'm at the chapter about models) Larry mentions having a user table, which greatly simplifies registration and login. However with my applications setup, the person_id in person is the same as either student_id in student or employee_id in employee. Registration is done by using PDO lastInsertID to get the person_id and use that for registering either a new student or new employee. So my question is, with this scenario, how would you recommend that I work with the person and student/employee models in Yii? Any advice would be appreciated! Regards Phil Link to comment Share on other sites More sharing options...
Antonio Conte Posted August 15, 2013 Share Posted August 15, 2013 This is the classic Object-relational impedance mismatch problem you are facing here. My first though is that if you can avoid using several tables for user data, you get way easier off this problem. One way to solve this is work with a generic "Person" table instead of employees and students, and use foreign keys to join the extra data you need accordingly. (Be it credits for classes or when/where to hold lectures) A flag can be used to differentiate between types of persons, and what kind of object to be created. Will such a solution work out for you? Link to comment Share on other sites More sharing options...
Larry Posted August 16, 2013 Share Posted August 16, 2013 In the new chapters I just posted yesterday, I explain how to handle situations like this using a single table inheritance approach (which is what Antonio is also suggesting). Link to comment Share on other sites More sharing options...
Antonio Conte Posted August 16, 2013 Share Posted August 16, 2013 Good to know. It was a somewhat educated guess, but I've myself started developing a membership system with a similar approach. There exists four membership types in total, and a single user may hold zero to three of these memberships. Each membership requires different data, and all of them are based on seasonal renewal. Up to three family memberships may also be tied to a specified JCN membership, but these cannot be purchased alone. Since the system requires authentification, a basic user type (That'll be Laravel's basic auth table here) is required. I'll share my table structure here: seasons ( id, name, validFrom, validThrough ) users ( id, username, password, firstname, lastname, phone, mobile ) premiums ( id, user_id*, season_id*, membercode ) subscribers ( id, user_id*, season_id*, section, row, seat ) jcn ( id, user_id*, season_id, address, province, postal, zip ) family ( id, jcn_id, user_id, season_id ) Understroke: Primary key Asterix: Foreign key This is a very complicated memberships systems, and few will hold a premium or subscriber membership. I don't know if this will help you at all, but rename users to persons and subscribers and premiums with student and employee, and you have a basic structure to build on. You'll obviously need to change the data the table holds. Link to comment Share on other sites More sharing options...
Philip Posted August 21, 2013 Author Share Posted August 21, 2013 Hi Antonio and Larry, Apologies for taking so long to get back, I'm away in a summer house with no wifi and very limited data coverage. But I'm dealing with the withdrawal symptoms. :-) Anyway, thank you very much for your replies. @Antonio, thank you for posting your table structure. It is actually very similar to what I am dealing with here. Having a single user table would be ideal, however I can understand why the tables are split up between person, student and employee, as the developer probably wanted to ensure that certain properties can only be assigned to students. Once I get back to the office, I'll have a look at breaking the student and employee tables down in to smaller tables. @Larry, great that there is an update to the book, which I'll download once I get to a more stable connection. It's been a great read so far with lots of hints and tips. Obviously I spent most time reading through the auth and auth chapter, trying to think how I can apply it to my structure. I'll just play around with the framework and see how it goes. Thanks a million for the suggestions! Regards Phil Link to comment Share on other sites More sharing options...
Philip Posted August 30, 2013 Author Share Posted August 30, 2013 Hi again Just a little update and a further question regarding the post. So I've finally gotten around to updating the database and have now implemented single table inheritance. I basically followed the advice in the the link provided by Larry in the updated database chapter - Yii - class table inheritance. Previously I had person, employee, student and teacher tables, but now I have a single user table with a column storing the user type. I then have a separate table storing addition student data. In Yii I have a model called User and another called Student which inherits from User. This works great and solves my original problem of registering a new student which is done in the actionCreate() method by doing the following: $model=new Student; $model->data=new StudentData(); In the form I can then handle the additional student data by doing the following: <div class="row"> <?php echo $form->labelEx($model->data, 'date_of_birth'); ?> <?php echo $form->textField($model->data, 'date_of_birth') ?> <?php echo $form->error($model->data, 'date_of_birth'); ?> </div> This all works perfectly and I can create a new user which is set to the correct user type and if the user is a student, the student data table is correctly updated. So it's a good solution all around for me and thanks for the suggestion. I do have a quick question though. If I am to apply the same logic to the actionUpdate, the loadModel() function is of course used. So I thought I'd edit loadModel() to contain the following: $model=Student::model()->findByPk($id); $model->data=StudentData::model()->findByPk($id); But it doesn't seem to pick up the additional student data and Yii is complaining data is not defined. Am I doing something wrong here or do I have to create an additional loadModel() method to handle the additional table data? Any advice would be appreciated! Regards Phil Link to comment Share on other sites More sharing options...
Philip Posted August 30, 2013 Author Share Posted August 30, 2013 Ok I seem to have solved the issue for now, though I'm not sure if it is the correct 'Yii' way of doing things. What I did in the end was to create a private property in the the controller called $_modelData. Then I loaded the additional model in loadModel(), stored it in $_modelData and created a $modelData variable in the actionUpdate method. public function loadModel($id) { $model=Student::model()->findByPk($id); $this->_modelData = StudentData::model()->findByPk($id); // other stuff public function actionUpdate($id) { $model=$this->loadModel($id); $modelData = $this->_modelData; // Uncomment the following line if AJAX validation is needed // $this->performAjaxValidation($model); if(isset($_POST['StudentData'])) { $model->attributes=$_POST['Student']; $modelData->attributes=$_POST['StudentData']; if($model->save()) $this->redirect(array('view','id'=>$model->id)); } $this->render('update',array( 'model'=>$model, 'modelData'=>$modelData, )); } In the view I can then do the following which loads the data from the two tables fine. <div class="row"> <?php echo $form->labelEx($model,'some_column'); ?> <?php echo $form->textField($model,'some_column'); ?> <?php echo $form->error($model,'some_column'); ?> </div> <div class="row"> <?php echo $form->labelEx($modelData, 'other_column'); ?> <?php echo $form->textField($modelData, 'other_column') ?> <?php echo $form->error($modelData, 'other_column'); ?> </div> Its seems to be a more long-winded way of doing it compared to $model->data in the actionCreate method. Does this sound like a good solution? I haven't completely finished the actionUpdate method yet, so it doesn't actually update the two tables, but I'm sure I'll get that part working. thanks again Phil 1 Link to comment Share on other sites More sharing options...
Larry Posted August 31, 2013 Share Posted August 31, 2013 I don't have time today to look at this in detail, but I would say that if it works for you, that's the most important criteria. On the more advanced level, many of the fixes you'll implement may seem hack-y, but that's often the case when using frameworks. Thanks for sharing your code! Link to comment Share on other sites More sharing options...
Recommended Posts