Reduce complexity in this database design [closed]

Question

The database structure is like this : each user has create their list , and have a access right assign:

For example

table: user
attribute: UserID

table: list
attribute: ListID

table: user_list
attribute: ListID,UserID, Rights

There are two kind of rights :read , edit

Everything work fine until i have a rights call public ( everyone can edit) So that if i have 100 member with 10 list, i have to create 1000 userID - Rights: edit rows

that is incredibly increase in scale.

How to solve this:

1)Create separate table named public and put the ListID in it?

2) Store the row as ListID , null UserID, and with Rights 'Public'?

Which method is better? Thank you.

Answer 1

I would suggest that you create a column for each of the rights read, edit, public so that you can assign the permissions for each user_list. When you get a new permission you add a new column and so on.

This makes it easy for you to check the permissions of a user on a specific list

Answer 2

I have a strong preference for your option 1 e.g.

table: user_public_list
attribute: ListID