Database concept for scheduling an hour to meet

[manitislate]

Hi guys, I've been thinking about this design for a while and wanted to get a second opinion if anyone would care to share. I think the concept will work, but somehow I think it's just not right.

Here is the idea:

I want to connect users together into a groups of 5 to 10 people, depending on how many the group would like. They would select what hour works best for them, and what hours are ok for them (sort of a 1-100 scale of preferability).

 

I'm keeping it basic right now, but I would like to have the option of adding user group names, personal likes (like I like fishing), and what not that may come up in the future.

Here are the tables I was thinking of making:

 

User: Userid, UserEmail, passwordhash, Availability Days (save day and hour of availability), prefered group size, likes (fishing, diving), likes (other), avatar, etc...

Monday: Userid, Hour 1(what value 1-100 user gave for the hour), Hour 2,...Hour 24

Tuesday: Userid, Hour 1....Hour 24

Wed-Sun: same as monday...
 

 

It apears like it would work, but it just seems cooky. How can I store days of availability with a weight of preferance? Also, what if later I want to add 1/4 hours? Not too good.

[Ofarchades]

Definitely don't create a table for each day of the week.

 

Without knowing the specifics of what you're trying to do, try something more along the lines of:

 

users: user_id (unsigned int, primary key, auto increment), [everything else]

availability: availability_id (unsigned int, primary key, auto increment), user_id (unsigned int, indexed), day (unsigned small int), hour (unsigned small int), preference (unsigned small int)

 

(Alternatively, the availability table could have an "hour_from" and "hour_to" column instead of just "hour")

((Feel free to add more indexes depending on your query requirements))

 

Then for each day and hour (or hour range) the user is available, create a row in the "availability" table.

 

In the future, you could perhaps change the "hour" column from an integer to a decimal to allow for quarter or half hour measurements.

 

Do you think this would work for your purposes - or have I missed something?

Edited May 13, 2015 by Ofarchades

[manitislate]

Hi Ofarchades,

 

Thanks for the help, I knew I was doing something wrong. I think what you suggested will work, I made a few additions:

UserTable: UserID, Email, Likes_ID, Group_ID, etc..
AvailabilityTable: Avail_ID, UserID, Day, Hour, Preference
LikesTable: Likes_ID, Description

Group Table: Group_ID, Name, Meeting_Day, Meeting_Hour

 

So the use would be to make a php script to match users by availability and likes. Once matched, they can be added to the group table and the group id added to their user table.

 

I figured out that if I use military time (2300 = 11 pm) then it shouldn't be a problem if I wish to add 1/4 hours later.

 

I have two concerns now.
1) How could I add another group if the user wished to have 2 groups?

2) Isn't this a lot of linking and searching? If I had a lot of users, wouldn't it run slow?

 

I attached a mock up of the database so far.

[Ofarchades]

Using military time is a nice idea.

 

1) How could I add another group if the user wished to have 2 groups?

 

You could have a pivot table linking the users to their groups instead of having the group_id column on the user table. It might look like:

 

pkid (unsigned int, primary key, auto increment), user_id (unsigned int), group_id (unsigned int)

 

Index user_id and/or group_id depending on what sort of lookups you'll be doing.

 

2) Isn't this a lot of linking and searching? If I had a lot of users, wouldn't it run slow?

 

So far this is a fairly standard relational database design. The idea of these databases is to link related information. As long as you don't make any crazy mistakes with the queries, it should be fine.

Edited May 13, 2015 by Ofarchades

[manitislate]

Hi Ofarchades,

Thanks so much for your help. I think I'm straight now. I was doing some reading on database design and what you have said has really helped me to put all the pieces together. 

I figured out that what I am trying to do, with letting users pick more than one group, is a many to many relationship (many users to a group and many groups for each user). I wasn't sure if this was that situation, but what you suggested is definately an example of a many to many. Only thing is I saw on this site that you don't need a primary key, you just make each group a primary key:

CREATE TABLE `pivot` (
  `user_id` varchar(6) NOT NULL default '',
  `group_id` varchar(6) NOT NULL default '',
  PRIMARY KEY  (`a_id`,`b_id`)
}

Any way, much thanks again, you saved me a lot of trouble

[Psycho]

Hi Ofarchades,

 

Thanks for the help, I knew I was doing something wrong. I think what you suggested will work, I made a few additions:

 

UserTable: UserID, Email, Likes_ID, Group_ID, etc..

AvailabilityTable: Avail_ID, UserID, Day, Hour, Preference

LikesTable: Likes_ID, Description

Group Table: Group_ID, Name, Meeting_Day, Meeting_Hour

 

So the use would be to make a php script to match users by availability and likes. Once matched, they can be added to the group table and the group id added to their user table.

 

I figured out that if I use military time (2300 = 11 pm) then it shouldn't be a problem if I wish to add 1/4 hours later.

 

I have two concerns now.

1) How could I add another group if the user wished to have 2 groups?

2) Isn't this a lot of linking and searching? If I had a lot of users, wouldn't it run slow?

 

I attached a mock up of the database so far.

 

The DB schema is incorrect and will not work. The user table should only hold things such as name, email, etc. Any "associations" (likes, groups, etc.) need to be stored in an associative table. That allows you to have no value, one value, or multiple values. The structure would look something like this:

 

Users

user_id, email

 

Likes

like_id, description

 

Groups

group_id, name, meet_dow (day of week, should be 1-7 since that is how MySQL understands Sun - Sat), meet_time, meet_length (:30 = 30 minutes)

 

User_Likes (used to associate likes to users, one record for each user-like pair)

user_id, like_id

 

User_Groups (used to associate groups to users, one record for each user-group pair)

user_id, group_id

Edited May 14, 2015 by Psycho

[Ofarchades]

The DB schema is incorrect and will not work.

 

His proposed schema would not be scalable, but it's by no means "incorrect" or broken. In any case, in the previous two replies we already discussed the addition of a pivot table as you subsequently suggested in your post.

Edited May 14, 2015 by Ofarchades

[Psycho]

OK, my wording was not necessarily "precise". But the comment that it "would not work" was correct based upon his intended use

 

 

 How could I add another group if the user wished to have 2 groups?

 

And, although I don't think he specifically stated it, I inferred that the user would have multiple likes.

[manitislate]

You guys are so awesome.

 

Thanks Psyco for the input. It has reinforced the structure I have winded up with (and couldn't have done it without Ofarchades help).

 

b.t.w. - ever notice that every forum always has a user named psycho who is an expert and guru to boot? Is that because Psycotic people are just really smart people, or is it that really smart people always feel a bit psycotic?