Database Schema

[MarkLuvsCS]

Hello everyone,

I'm working on an android project and I can't see how the database needs to be setup. I currently have an idea on how it should function, but I would love to get some input so I can setup the database and not worry about that aspect. I'm not sure how I format this since it's just text, so please forgive the wall.

For the project I'm trying to have two main parts: 1. Users joining Groups and creating Events to participate in. 2. Communication of the different sections, so User-to-User, Group-to-ManyUsers, Events-to-ManyUsers. I'm thinking of the latter as sort of like a Group message board where every user sees the messages provided in the groups. Same with Events so everyone at the Event could see that group of messages. I'd also love to implement a check-in style system for the Events, but that's not really a priority.

Database Schema

Users:
UserID PK int(auto increment)
Name varchar(50)
Pass
Email

Member_Groups:	
UserID FK
GroupdID FK

Groups:
GroupID PK int(auto increment)
GroupName varchar(50)
GroupAdmin (UserID?)
GroupDescription varchar(100)
GroupType
GroupType2
GroupType3
GroupType4

Events:
EventID PK int(auto increment)
EventName varchar(50)
EventAdmin (UserID?)
EventDescription varchar(100)
EventAddress varchar(50)
EventDate
EventTime

Group_Messages:
GroupID
MsgID

Messages:
MsgID int(auto increment)
Sender (UserID) FK
GroupID FK
MsgTitle varchar(20)
Content varchar(255)
Date
Time

Thank you for your time

 

[Cogman]

Make sure pass is the hashcode + salt for the the user, never ever store plain text. On top of that, you should use something as strong as SHA-3 or similar (Bcrypt and BLAKE2 might be options).

What is GroupType[2,3,4]? Does it really need to be 3 separate columns? Could it perhaps be another table? It is usually a bad sign when your database has columns like thingy1, thingy2, thingy3

Does every event need a description/name? It might be better to have an EventType table which contains descriptions of unique events. What are these events anyways? Is there some sort of data which should flow with the event? What is the difference between an event and a Message?

Why do messages have a date and a time column? Most sql implementations support a datetime construct which contains both bits of data.

Finally. Have you considered RabbitMQ or similar techs which already do event passing/queuing stuff.

 

[MarkLuvsCS]

Make sure pass is the hashcode + salt for the the user, never ever store plain text. On top of that, you should use something as strong as SHA-3 or similar (Bcrypt and BLAKE2 might be options).

This is for a course project. I was planning on having a hash+salt with md5 but right now it's not a requirement for the project, so I've put that feature on last.

What is GroupType[2,3,4]? Does it really need to be 3 separate columns? Could it perhaps be another table? It is usually a bad sign when your database has columns like thingy1, thingy2, thingy3

This probably is poorly designed, I just had a single particular instance which needed 2,3,4 and thought it would be better to have fewer tables. I could make a separate table for that once case though.

Does every event need a description/name? It might be better to have an EventType table which contains descriptions of unique events. What are these events anyways? Is there some sort of data which should flow with the event? What is the difference between an event and a Message?

I think so. Ultimately the app has people join a multitude of groups of different types, and each group will have different events based on the group itself. If i have time I planned on adding some check-in style system to an event so specific users could recognize when people arrive to the event.

Why do messages have a date and a time column? Most sql implementations support a datetime construct which contains both bits of data.

Good to know, was trying to think this through my head what might work, but that'll be better.

Finally. Have you considered RabbitMQ or similar techs which already do event passing/queuing stuff.

I'll take a look into RabbitMQ, it has a java API i may be able to use. I may go that route.

Thanks for your help Cogman. I'm separated the quotes to answer each question to the best of my ability.

 

[Cogman]

This is for a course project. I was planning on having a hash+salt with md5 but right now it's not a requirement for the project, so I've put that feature on last.

That's fine, but I would suggest not using MD5 for your hashing algorithm. No new project should use MD5. Some pretty major vulnerabilities have been found.

This probably is poorly designed, I just had a single particular instance which needed 2,3,4 and thought it would be better to have fewer tables. I could make a separate table for that once case though.

You should prefer having more tables vs more columns. Tables are pretty cheap and VERY flexible (what happens if you have a 5 come along? or a 6?) Don't worry about sparsely populated tables, they are pretty small and have pretty much no performance impact.

For example, we several tables on our production machines which have less than 30 rows


Good luck.