So I'm currently learning about databases and db design and I'm stuck on this concept.

I have a database that has an owners table, and a users table. I want to essentially create an API that allows for users to register, receive an api token, and then have access to specific routes depending on their permissions/roles.

I'm a bit confused as to how to go about the relationships though. An owner can be a user but doesn't necessarily have to be. They will be in the database regardless of whether or not they've created an account.

Would the best route be to use both an owners and users table that can be related if the user's email address matches the owner's email address? Then I'd show all information from the owners table for that user (who is the owner)?

Apologies if the explanation is a bit all over the place.

CodePudding user response：

Often database tables become extremely fragmented unnecessarily. Here is a rule of thumb, if the owners table is not a one to many relationship, the details are probably better stored in the Users table and just have a column that expresses a role. Then you can filter users out with a simple query:

SELECT * FROM dbo.users WHERE role != 'owner'

CodePudding user response：

you can use both tables, with a foreign key from the owners table to the users (and this will make it flexible enough for you to add additional types of users). this way it will be simple to do what you required here.