CODEWITHSUNDEEP
databasedatabase-design
hat_to_the_back
hat_to_the_back

Reputation: 291

How many tables is acceptable in a Database?

I am currently designing a database for a file sharing service. Every folder that is created on this service will have its own Table(Folder_) each Folder will also be added to a Table which will have the folders and their owners.

Is this bad practice? The reason being I have there are several features which I want to incorporate into this service which require me to have a table for each folder i.e permissions/different levels of access.

e.g The owner of Folder A has added 3 users with different levels of access to the folder User1, User2, User3.

User1 has view only access.

User2 has edit access.

User3 has full access.

The reason I can't see another of doing it except for creating a table for each folder is, if I had a Table of File/Folders with the usual attributes where could I put the users who have access to the folder and their level of permission? Also how would User1 view what folders they have access to with a big search of the entire database etc

The reason I dont think it can be good practice, is the amount of tables that will be in the database?

Upvotes: 0

Views: 136

Answers (1)

JSR
JSR

Reputation: 6396

I would suggest a single table for folders, another table for users and a third table for user folder permissions. If you are making many tables that all have the same columns, you are probably not normalizing correctly.

Table: Folders
    FolderId
    FolderName
    OwnerUserId     -- references Users table to identify the owner
    (additional columns as necessary to describe a single folder)

Table: Users
    UserId
    UserName
    (additional columns as necessary to describe a single user)

Table: FolderUsers
    FolderId    -- references Folders table
    UserId      -- references Users table
    Permission  -- (ViewOnly, Edit, Full, etc)

The FolderUsers table is called a junction table, it allows a many-to-many relationship between Folders and Users, just add a row for each folder/user combination.

Upvotes: 2

Related Questions