Schema design of JSLovers

[Neha]

related to this issue: #38

JSLovers requires data of different entities. This discussion is on the schema design of the data for the JSLovers.

Following are the entities:

1. users (can have different roles, based on location)
2. Meetups
3. Jobs
4. sponsors
5. collaboration

Roles:

1. super admin
2. organizers
3. volunteers
4. co-organizers
5. speaker
6. user/attendee

other

1. User can filter users, jobs, meetups
2. User can paginate
3. CRUD operations
4. location based

What we need is a visual schema design of the above, before we start code.

[akshat99812]

Could you please take a look at the schema.

1. Users Table

• user_id: Unique identifier for each user.
• name: User's name.
• email: User's email address, unique to each user.
• phone_number: User's phone number.
• password_hash: Hashed password for security.
• location_id: Foreign key linking to the Locations table.
• created_at: Timestamp for user creation.
• updated_at: Timestamp for last update.

2. Roles Table

• role_id: Unique identifier for each role.
• role_name: Name of the role (e.g., super admin, organizer).

3. User_Role Table

• user_role_id: Unique identifier for each user-role relationship.
• user_id: Foreign key linking to the Users table.
• role_id: Foreign key linking to the Roles table.

4. Locations Table

• location_id: Unique identifier for each location.
• city: City name.
• country: Country name.
• region: Region or state name.

5. Meetups Table

• meetup_id: Unique identifier for each meetup.
• title: Title of the meetup.
• description: Description of the meetup.
• location_id: Foreign key linking to the Locations table.
• date: Date of the meetup.
• visibility: Visibility status (e.g., public, private).
• created_at: Timestamp for meetup creation.
• updated_at: Timestamp for last update.

6. Jobs Table

• job_id: Unique identifier for each job posting.
• title: Title of the job.
• description: Description of the job.
• location_id: Foreign key linking to the Locations table.
• created_at: Timestamp for job posting creation.
• updated_at: Timestamp for last update.

7. Sponsors Table

• sponsor_id: Unique identifier for each sponsor.
• name: Name of the sponsor.
• website: Sponsor's website URL.
• logo_url: URL to the sponsor's logo.
• created_at: Timestamp for sponsor entry creation.
• updated_at: Timestamp for last update.

8.Collaborations Table

• collab_id: Unique identifier for each collaboration.
• title: Title of the collaboration.
• description: Description of the collaboration.
• location_id: Foreign key linking to the Locations table.
• created_at: Timestamp for collaboration entry creation.
• updated_at: Timestamp for last update.

9.Feedback Table

• feedback_id: Unique identifier for each feedback entry.
• user_id: Foreign key linking to the Users table.
• meetup_id: Foreign key linking to the Meetups table.
• rating: Rating given by the user.
• comments: Comments provided by the user.
• created_at: Timestamp for feedback entry creation.

[akshat99812]

Need some help with the roles section.
I think there should be 5 roles

• Super admin
• organiser
• co-orgnaiser
• volenteer
• speaker
• user

[Neha]

Hey @akshat99812 , thank you. I am doing a whiteboard discussion tomorrow on this. Will review after that.

[VenomFate-619]

I think in schema we should also add created_by and updated_by,
this will have the user_id.