How to handle user permissions in a database schema?

I’m currently creating a database model for a system containing users, which can have permissions to some categories. Simplified, it looks like this:

User
- username
- password

Category
- name

I now want to give users permissions to some categories, but not to others. So I thought of doing this like so:

User
- username
- password

Category
- name

Permission
- ForeignKeyField(username)
- ForeignKeyField(Category)

My question: is this a logical way or are there better or more usual ways of doing this?

Answer

The only thing I see missing are ID’s. I would have my Schema set up as such:

User
- ID
- Username
- Password

Category
- ID
- Name

Permission
- ID
- UserID
- CategoryID

Leave a Reply

Your email address will not be published. Required fields are marked *