Using DBIC: What is the correct way to design relationships for Role Based Authorization?

three18ti has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks,

I am wokring on building a website, because I don't really understand how DBIC works it was suggested that I take Catalyst out of the equation and build my database first.

I would like to write my relationships first, then using a script deploy my DB and (if necessary) pre populate it. Doing it via scripted interface will allow for more flexibility in deployment as well as DB choice.

I have been working on this and you can see my full source at https://github.com/three18ti/dbix-schema-test. I did post about an error I am receiving in a previous post DBIC Error: DBIx::Class::PassphraseColumn::new(): new_result needs a hash at constructor but it seems that it is a rare error to encounter.
Can someone please provide some pointers on how to correctly design a DB schema using DBIC?

Thanks.

Comment on Using DBIC: What is the correct way to design relationships for Role Based Authorization?

Replies are listed 'Best First'.
Re: Using DBIC: What is the correct way to design relationships for Role Based Authorization? by DrHyde (Prior) on Jun 06, 2012 at 10:46 UTC
I suggest designing the database first, including all the foreign key relationships, and only after you've done that should you create your DBIx::Class stuff. To answer your specific question how to design the relationships for role-based authentication, I suggest three tables: user id, username, password_hash, etc ... role id, name user_role user_id, role_id I'm sure that the meanings of all these field names and the relationships between them are obvious. If it isn't, then you should first read a book on relational databases.	[reply]

Replies are listed 'Best First'.

Re: Using DBIC: What is the correct way to design relationships for Role Based Authorization?
by DrHyde (Prior) on Jun 06, 2012 at 10:46 UTC

I suggest designing the database first, including all the foreign key relationships, and only after you've done that should you create your DBIx::Class stuff.

To answer your specific question how to design the relationships for role-based authentication, I suggest three tables:

user: id, username, password_hash, etc ...
role: id, name
user_role: user_id, role_id

[reply]