We have an object model where most objects due to security permissions may be associated with zero-to-many persons.
One example is a Folder entity that may be associated with a number of person entities with administrative rights plus a number of person entities with "standard" rights. In this particular case we thus have two distinct many-to-many associations between Folder and Person, for "admin access" and for "standard member access", respectively.


To be able to express these different associations without having to create a join table for each association to Person, and be able to reuse code, I have been sketching on a PersonList entity. A Folder would be associated with one PersonList referring the admins and with one PersonList referring the standard members, and it is the PersonList that has the actual many-to-many association with Person:


The resulting tables would look like this:


So far so good, but there is one thing that bothers me; the PERSONLIST table. This table will only contain an ID column, and these IDs will be present in the join table anyway so the table would not be needed if writing manual SQL selects. Then of course, as I have designed PersonList as an entity, it does need a table.

Is there a better way to map these objects so I can get rid of the PERSONLIST table while still keeping the idea of a generic PersonList object that can be reused by all my different entities?

Sure. This is from section 7.5.3 fo the 3.1 ref docs, modified for your case:

Aaaah, that property-ref thingie was very convenient. Great stuff, thanks!

If I understand this correctly, then my PersonList class goes away and I use the two sets directly on my Folder class, right?

Two other things come to mind that I hope you would like to discuss:

1) Creating unique ids for ADMIN_PERSONLIST_ID and MEMBER_PERSONLIST_ID.
When persisting a new Folder I need to initialize its two personList properties with unique PERSONLIST_IDs. We normally use a global sequence for all ids. How would this best be solved in the mapping?

2) "The property-ref attribute should only be used for mapping legacy data".
Reading the reference doc it seems the author considers property-ref solutions as Bad Things, only to be used if forced by legacy data.
I am designing a new database so I wonder if the property-ref solution may be considered a Good Thing in my specific case, or if I _really_ should choose another solution (and give up on my initial requirement with reusing the same table for all "lists of Persons") ?

Cheers
Mike

Well, if you're willing to change your schema, then all sorts of possibilities open up. My personal preference would be to have just one set of users, and have user be an abstract base class. Then add a discriminator column to your user table, specifying if the user is an admin or not. Of course, this completely eradicates all hope of implementing anything approaching an ACL. Using this design, an admin user would be an admin of all folders that he has access to.

Alternatively, you implement a bidirectional many-to-many association with a join table represented as a composite element. The join table would have a "isAdmin" column. This is probably the most complex mapping, but the reward is that you get the most power out of your java classes for the lowest DB overhead. You'd combine sections 8.2 and 7.5.3 of the ref docs (v.31; the sections are named "Collections of dependent objects" and "many to many", in case you're reading an older version with different section nums).

The main thing that I'd try to avoid would be your current limitation that a folder has at most one member and at most one admin.

Thanks again for taking time to help me on this!


Sorry, it's the ACL-kind of thing I need. A person only has a certain role in relation to an individual folder.


I have these person lists on a quite a few classes, not just Folder, with all sorts of different roles. So I would need to have a "roleType" column instead, with values like "admin", "customer", "contact", etc.

With this mapping, distinguishing the different roles from each other needs more "manual intervention" in the Java code and is probably not so attractive. To achieve simplicity in both the Java code and the database schema, it feels like I should stick to the concept of a generic person list that I can reuse (one or many of) on any other class, without having to add discriminator columns or values.

Maybe my initial sketch with your property-ref solution is the best way to go. What do say about the Good Thing / Bad Thing side of this?



Sorry, I lost you there. Which mapping are you referring to? (My initial sketch does support multiple admins and multiple members, right?)

Best regards
Mike

Ignore that quote of mine. Late on a Friday isn't always the optimal time to ask me java stuff. If you had asked me a beer related question, I'd have put some more thought into it... ;)

The main disadvantage of having one list per role is that you need to do some major reworking to add a new role. If you added a "guest" user type, then you'd need a third set of users. You'd also have to put some work into validating a folder when saving it, to ensure that a user isn't both a guest and an admin. If you had only one list of users, and put the users' access rights to each folder in the user-to-folder link table, then a simple uniqueness constraint would do that for you. Though of course, your schema allows you to do that already, so that's good.

If you model the link table as a separate class, then you'd get the best of all worlds, I think:



The person and folder mappings both would include a set of roles. Maybe something along these lines:If a ternary relationship like that isn't appropriate, a set of many-to-many FolderRoles will also work, but then you may run into issues with mapping a table twice, once in each direction (because FolderRoles and PersonRoles will both use the PERSON_FOLDER_ROLE table).

Hmm, apparently first thing on Monday doesn't find me at my best, either. I don't suppose you have a coffee-related question? ;)

Well, I have always wondered whether you should pour the milk into the coffee, or if it's better the other way around... :-)

Being weak on composite mappings I must admit that your latest mapping is a little bit scary, but apart from that it is quite interesting! (I'll do some reading on that stuff)
In the cases I can think of now, I don't require to have the mappings back from a Person, so I guess one possibility is that I go with a many-to-many mapping on the resource side, and nothing on the Person side.

Many thanks for great help!
Best regards
Mike