Please I need a help on something, I am new on elxir/phx. I came from the background of Core Php coding style without framework trying to lay my hands on elixir/phx tech. I have a project that will require user authentication table, users table (This will hold more info about the use) and also user roles/permissions table ( that’s activities a user can do or cannot do on the app), but I am having challenge on how to manage this tables, So my question is, is it better to have the tables managed separately or should I merge all of them into one table? Thank you guys in advance.
You said you need 3 tables, so I think you need 3 tables.
The preferred way is unrelated to phoenix and ecto, but a lot more to your preference and targeted data normalisation.
1 Like
Thank you very much for your response, I am aware one can have them in separate tables in a relational way, also one can have possibly the entire three tables collected into one. But my question is, which is the most effective way please. thanks.
Depends more on the database than on ecto.
Please I am sorry I think ecto I added here is misleading, I have edited the top tag. I am still new to the technology please.
The most effective way depends on a lot of things to do with your design goals and the database you are using. In terms of a pretty generic answer, when using a RDBMS your best bet is to aim for a good relational model and make exceptions when you need to for good reasons. This approach means you are by default set up towards query flexibility, no duplication, easy updates, consistent correct state. In rare cases you will have something that is harder to deal with or needs to be carefully optimised for a specific query / access pattern, at this point you might want to consider some denormalisation as a trade off. On the other hand if you are building around a document database or something else, none of the above really applies.
Well, you said you have a PHP background, so I assumed you have already a database background as well.
it all depends…
most likely you need ONE users table that does auth, and holds the user - see and maybe use https://hex.pm/packages/pow
for the roles - you most like need a roles table AND a join table eg users_roles that assigns the different roles to a user…
1 Like
Exactly like this
Yes please, but in mysql db way, having data scattered in the different tables and mapped together sometimes is expensive when and the record grows, So trying to query tables in join mode is expensive to the server, and this where I also considering, not knowing how well postgres manages such.
wow, I have not come across this, I give it try then, thanks.
you can embed the roles on the users table - I would recommend against it - but it just might be easier to do if you are just getting starting - https://thoughtbot.com/blog/embedding-elixir-structs-in-ecto-models#embedding-multiple-structs-with-embeds_many
To be honest, this sounds as if indices have been wrong… Even MySQL, with all bad things said about it, should be able to handle joins properly, assuming that there exist proper indices on all ends.
EXPLAIN is to help you with such stuff.
Well, MySQL, work fine when you are don’t have too much record to deal with, but ones you have much records, left joining becomes expensive to deal with, perhaps that is only common with community edition, I have not used the enterprise edition though.
Hi, I think you are about to design db schema with following criteria:
- An user has many roles, a role has many user
- A role has many permissions, a permission belong to a single role
- An user has many permissions, a permission belong to many users
- Eventhough an user could have many permissions because he belongs to many roles, his permissions can be actived/deactivated
2 Likes
