I am working on a project and my task is to design a database. I tried to read as much as possible, but I still have my doubts how to solve it using a optimal solution.
I'm designing a database that will store users of varying types. Predominantly they will be Director, Employee and Growers. There is a possibility that this number may increase, so I want to be able to expand.
I am implementing this database using the Entity Framework, and my plan is to have a users table inheriting from ASP.Net identity, which will be responsible for logging into the site.
The entities are the following:
Company -- one-to-many--> Grower
Grower-- one-to-many--> Ranch
Ranch-- one-to-many--> WaterTank
This seemed quite simple to me, as most of the relations were one-to-many, thus not requiring a extra table.
The issue evolved once I wanted to implement users with different access points and roles.
Firstly, I was wondering whether there was a more optimal way of getting all the WaterTank entities for one company, other than using joins. I was wondering whether adding a one-to-many relationship directly between company and watertank would be a optimization.
Secondly, I want to implement a access restriction to the entities. The user type "Director" is able to see all ranches and watertanks for his company, the employee is able to see a smaller, defined list, and a grower is only able to see his own ranches and the watertanks. I believe that I would have one user table, having all the general info about users, and then maybe another table joining users and their ranches? But then I am being redundant as the "Director" sees every ranch that the company sees. My issue is that the relation between the grower and the ranch is one-to-many, but because we have multiple users, a ranch although belonging to only one grower, can be edited and viewed by multiple users. And not every user sees the same entities. My possible solutions:
a) Introducing another table mapping every user to every other entity thus defining the relation explicitly, but then it omits the structure of the database and seems redundant. Also, adding an entity to a ranch, would require me to add it to every role of a user.
b) Creating several user types, ones that define which watertanks they own
c) For the case of a grower, inherit it from the user class, and use the Role table to define the types of users, but then I am not sure what to do with the director user,
Thank you in advance for any input!