Hi, I'm starting on a different project and am trying to figure out the best approach for setting up the relationships. in my spark app I have different user types, but I am unsure if I should be using roles or pivot tables. Every time I play it out both seem appropriate... Problem is for certain types of users there should be only one of that type, which I can constrain via the db, but not sure how to achieve that with a role. For instance, an organization will have many employees but only one will be CEO. Should a pivot table be created to keep track of which employee is the CEO if multiple organizations are stored within the database versus seeing which user has the role of 'CEO' and filtering it that way. Any input is greatly appreciated.
I may be misunderstanding but it sounds like you are using the term role to define a users job title within the organization.
Assuming that an employee will only ever have one role I would just store the role attribute in the users table or whichever table contains their profile attributes. Even though it will lead to duplication in your user profile table for that one attribute.
If your intent is to allow employees to have more than one job title you can use the pivot table and place your CEO constraint in your code rather than the database by using validation on the request.
If you use the pivot table option you are storing the role in one place so changes to the role name effect everyone with that role. That may or may not be what you want.
@ctroms I'm using Bouncer for roles and permissions. I forgot to mention that one of the reasons I was considering pivot tables was for relationship purposes (easily get listing of all CEO's, etc). The more I think about it, the more I think I will end up with a combination of both. The reason why I kept questioning it was because I will end up having around 6 pivot tables for the relationship with the other items and the User model. I may just be over thinking it but wanted some other input also.
@Jmrtech Gotcha. I don't think there is anything wrong with 6 pivot tables if that is what your application needs. I think it more comes down to whether or not an employee will have more than one job title. If you have a many to many relationship then a pivot table is likely the way to go. If not, and you go the 'title' attribute route, you can index the title column and restrict your query with a where clause. Just make sure that your db schema's character set is using a case in-sensitive collation or you may run into a case issue on the database that could be tricky to debug.
