2

I have three tables (blocks, roles, users) where the following is true in the schema:

blocks have many users
users have many blocks
users have many roles, but the roles are specific to the block (and the user)
blocks have many roles, but the roles are specific to the user (and the block)

For example:

user => john
belongs to block => 'metro' with role => 'builder'
belongs to block => 'rural' with role => 'investor'

user => dan
belongs to block => 'metro' with role => 'resident'
belongs to block => 'rural' with role => 'investor'

I have a join table named block_role_user with the following columns:

id   block_id   role_id   user_id   created   modified

I guess this would be a hasandbelongstomany relationship amongst all three tables. I thought about laying this out with a hasandbelongstomany table between blocks and users and another hasandbelongstomany table between roles and users but this will not fully define the unique relationship between a block, role and user.

I'm looking for advice on the best way to approach this and appreciate any advice.

I've looked into binding the model, but this doesn't seem to apply for the unique three part relationship I have here.

I thought about creating a model called blockroleuser and setting up the relationships using standard cakephp convention. If this is an acceptable way, can you give some guidance on naming the model and controller files?

Thanks again...

pinned5th
  • 23
  • 4

1 Answers1

0

If I got you right, then you are looking for a relation less loose than HABTM, which will define a specific connection between a User, a Block and a Role.

Personally I would probably use a model that defines these relations using belongs to, where a User can have many of those, something like:

User hasMany Whatever

Whatever belongsTo User
         belongsTo Block
         belongsTo Role

This is probably more or less what you ment when you were talking about a BlockRoleUser model, the proper naming might depend on what exactly blocks are. Of course a generic name like for example Involvement or even UserBlockRole might be fine too, I don't think you should bother too much about this.

Anyways, this looks pretty straightforward and strict to me, and if necessary for your application, then additional HABTM relations could be set up too utilizing the same table. These HABTM relations would most probably only be used for selects only, for example when there's need for querying all the Blocks a User is related to, or when testing whether a User is related to a specific Block, etc...

ndm
  • 59,784
  • 9
  • 71
  • 110
  • I appreciate the advice and explanation. All the assumptions you made were correct on my end (specific connection between user, block and role; mainly used for selects (and getting users specified to a particular block with the role only coming into play for deciding if the user can edit something related to the block). Thanks for guiding towards a model. This is the way I was going, but was getting hung up on the naming convention. I think 'involvement' would be a great name, but agree that the convention really doesn't matter as long as it accurately describes and is understood later. – pinned5th Aug 01 '13 at 02:07
  • By the way, I will check the answer as useful as soon as my reputation score will allow me - thanks again. – pinned5th Aug 01 '13 at 02:13
  • Hi @pinned5th, did ndm's solution work out for You? I'm facing a similar situation (other objects but same relations) and I was wondering if that approach has done the job for You. – Christian Feb 23 '18 at 15:24