Database Design Advice for a Social Network App Needed

Question

I am very new into backend stuffs like databases. That being said, I lack the database design knowledge.

I am wondering how and what is the best design for my scenario.

I am creating a social network app where the users can create groups and join other groups. Those groups have places in it. Those places are created by the User in the group

1. User
2. Group
3. Place

Rules:

1. One User can create and join many Groups
2. One Group can contain many Users
3. One Group can have many Places
4. Each Group have an admin User

I currently have 3 separate tables

1. USER TABLE

ID, EMAIL, USERNAME, PASSWORD, PROFILE PICTURE

2. GROUP TABLE

ID, NAME

3. PLACE TABLE

ID, NAME, COORDINATE, RADIUS

I am extremely confused in designing the proper database for it it.

Question:

1. How should I design the relation of the table.
2. I would like to have one User (maybe more) in a Group that has the permission to do certain stuff that normal User cant do. Such as Creating New Place, Deleting A Place
3. How should I define my table structure?

Any thoughts please? Any help is greatly appreciated!!

Thank you

Answer 1

You will need to have one more table in order to create what is known as a many to many relationship between the users and the groups.
Since you didn't specify the rdbms you are working with, I'll use SQL Server for my code:

CREATE TABLE TblUserToGroup
(
    UserToGroup_UserId int FOREIGN KEY REFERENCES TblUser(UserId),
    UserToGroup_GroupId int FOREIGN KEY REFERENCES TblGroup(GroupId),
    UserToGroup_IsAdmin bit DEFAULT 0        
    CONSTRAINT UC_UserToGroup UNIQUE(UserToGroup_UserId, UserToGroup_GroupId)
)

As you can see, there is also an IsAdmin column that can take values of 0 or 1. If you only want one admin user for each team, you can add a check constraint to prevent having more then one admin for each group.