Many Tables - Common Structure


I wrote a web app in PHP/MySQL to handle the data of a medical team. the prototype has worked well and I am now rewriting it using Yii because it needs to expand from 1 team to perhaps 100 - and I am learning Yii as I go.

To simplify my question each team uses two tables, operation_N and followup_N where operation_N has a one-to-many relation with followup_N. The structure of operation_N is the same for all teams, likewise followup_N. Lets say N = 1…100

I have figured out how to make my AR model work with a dynamic table name depending on N, but I am struggling with how to handle FK relations between the tables. It seems very clumsy to have N sets of FK assignments, one set for each team. Is there any way I can use the common structure of the N sets of tables to in effect use some "template-like" technique? Or is is best to abandon FK altogether?

Any pointers as to where to look would be very much appreciated.

David Johnson

This is an interesting problem. One solution out of many would be to add a key to each table that defines the team, then use the two tables for all the teams. If there is a lot of data this isn’t efficient, but you could probably partition the tables on the keys (not sure if your db supports partitioning).

You could define the relation (double check syntax):

'team' => array(self::HAS_MANY, 'ChildTable', 'foreign_key_id', 'condition'=>'team.team_id=team_id');

Basically creating a relation where the team keys must match on the join.

Is everything (db structure and model functionality) the same across teams? If that is the case, that is how I would start.

Hope that helps.

Hi, thanks for thinking about this. Putting all teams in one table would not fit other application criteria, like guaranteeing patient confidentiality. I was wondering about putting each team in a separate database db_N with tables operation, followup, patient, in effect using the database as a namespace. The system would only need to access two databases, one for admin/housekeeping and one for the team currently using it. That seems neater, and in principle the team’s data could be on a different server - perhaps their own - so security of data is under their control. However my experience is limited, so any warnings would be welcome!!

David Johnson

Although I understand your concerns, I feel creating seperate tables or even databases for each team is a poor design choice. While it might look like a cheap way to implement access control through the database alyout, you will actually accumulate a lot of technical debt this way. Access control should be sometehing that happens in the application’s realm, not the database’s.

On top of shness’ suggestion I’d like to point you to the wonder called “scopes” and in particular to CActiveRecord.defaultScope(). They are a perfect tool for defining which record a certain user should be allowed to see. It is even possible to control which fields should be populated in the result set.

Hope this helps :)

Thanks for your advice and for introducing me to scopes. As a newcomer to Yii I had not got round to studying scopes but I now see how much neater the code could be using scopes, especially in implementing table relations (there are doctor and patient tables as well as operations and followups). I had thought that the partitioning of data by group would make an easily expandable modular system but I take your point about ‘technical debt’. (Interestingly I was using a scopes-like syntax to access my dynamic table names Patient::model()->group(N)->find(…) but using real scopes would be much neater). Thanks again.

David Johnson

Glad I could be of help. In case you are unfamiliar with the term “technical debt,” I’d highly recommend to you this article on Wikipedia. The phenomenon isn’t new in particular, yet the term hasn’t been heard by many. I’ve seen a great deal of projects being overwhelmed by their technical debt, so it needs to be assessed and dealt with properly. As part of the problem, t.d. is sometimes hard to spot without the necessary experience. This is especially true when it comes to software and database design. Regarding latter one: May I kindly suggest the book SQL Antipatterns? Definitely a good read :)

As for being a newcommer: We all were one at some point. When I started working with Yii over two years ago, I wasn’t aware of the very existence of scopes for the first two or three months. Now I love them unconditionally since they are quite powerful and allow for pretty clean code.