Daily Time Schedule (MySQL)

Question

This is what I have:

days_of_week
+----+-------------------+
| id | name              |
+----+-------------------+
|  1 | Monday            |
|  2 | Tuesday           |
|  3 | Wednesday         |
|  4 | Thursday          |
|  5 | Friday            |
|  6 | Satday            |
|  7 | Sunday            |
+----+-------------------+

time
+----------+-------------+
| id       | time        |
+----------+-------------+
| Integer  | hh:mm:ss    |
+----+-------------------+

schedule
+---------+---------+----------+---------+
| id      | user_id | time_id  | day_id  |
+---------+---------+----------+---------+
| Integer | Integer | Integer  | Integer |
+---------+---------+----------+---------+

Where should I put the activity column, i.e. Breakfast 08:30 for example. Now 'Breakfast' has to be stored somewhere here. I cannot figure out where for the moment, but if anyone knows how to correctly Normalize this please share with me, and tell my why you are doing it that way or recommend that. Thanks a lot in advance.

What I want to achieve:

Monday: Date of that day here
08:00 Breakfast
08:30 Something else
09:00 Introduction

Tuesday: Date of that day here
08:00 Breakfast
09:00 Hackathon begins
12:30 Lunch

You get it.

To clarify:

The user can select to add a schedule, this schedule is then created and includes schedule for what will happen during those selected days, for instance, if there is an Hackathon occurring for 3 days, the user can create a schedule for those three days, with time and activity that will take place. E.g. 08:00 Breakfast, 09:00 Introduction, 09:30 Equipment Installation

Found this: Very normalized, Method for storing/displaying repeating weekly schedule

However, it's more for repeating weekly schedule, which is not what I want to achieve.

Haha! Of course I am the person designing it. However, I am still not sure where to place the activity stuff, or should that be in a separate table and then connected with the schedule table? — John Smith, May 05 '15 at 14:25
"is that you do not need the first two tables", this does not make sense. Think about maintainability in the long-run. — John Smith, May 05 '15 at 14:38
@Strawberry, show me examples, SQLfiddle or any tutorial that uses your model and that it actually works and is maintainable in the long-run without causing huge problems, thanks a lot. — John Smith, May 05 '15 at 15:22
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/77016/discussion-between-john-smith-and-strawberry). — John Smith, May 05 '15 at 15:27
Fine. Elaborate on your model, and please share a working example that uses exactly your model, and tell me why I wouldn't encounter problems in the long-run when using it. — John Smith, May 05 '15 at 15:36
because the model is normalized. Does it satisfy 1NF? yes. Does it satisy 2NF? Yes. Does it satisfy 3NF? Based upon the information so far provided, yes. — Strawberry, May 05 '15 at 15:41
@Strawberry, what about this, http://stackoverflow.com/questions/16351033/method-for-storing-displaying-repeating-weekly-schedule — John Smith, May 05 '15 at 15:44
That's for repeating events. You've not mentioned repeating events in either your description or data set (with the exception of Breakfast - but that's a minor redundancy) — Strawberry, May 05 '15 at 15:48
Sure, but look at the normalization the answerer did, isn't that the type of normalization I should strive for? — John Smith, May 05 '15 at 15:51
As Mike Hillyer says, the complete normalization of tables may be desirable, but you may find "that full normalization can introduce [unnecessary] complexity to your design and application" http://mikehillyer.com/articles/an-introduction-to-database-normalization/. In this instance, I can see no benefit afforded by normalization beyond what I and others have suggested. — Strawberry, May 05 '15 at 15:55
Incidentally, even if I had repeating events, that's not how I'd do it. I'd store a seed date in just the way I've described above, and then store information about the nature of the repetition. — Strawberry, May 05 '15 at 16:00

Arth · Accepted Answer · 2015-05-05T14:45:56.233

1

Are you going for a repeatable week on week schedule?

If not I'd consider dropping the days of week and time tables and using a standard DATETIME field. You could pop any restrictive measures in your model validation and it could also makes detecting clashes a bit easier.

Are you looking to regularly repeat activities?

If so I'd make an activities table and reference that from the schedule.

If not, I'd probably settle for a description and time.

SIMPLE

Schedule (id, description, datetime)

REPEATABLE ACTIVITIES

Schedule (id, activity_id, datetime)
Activity (id, description)

WEEK ON WEEK SCHEDULE (w/ repeatable activities)

Schedule (id, activity_id, day_of_week_id, time)
Activity (id, description)
Day_of_week (id, title)

Always go for the simplest option that fulfills your requirements!

edited May 05 '15 at 14:45

answered May 05 '15 at 14:38

Arth

12,789
5
37
69

1

It's not like a school schedule (which almost) repeats itself every single week. It's a schedule on build once and once those days are over, you move on to next time. The activity (e.g. Breakfast etc.) are manually added the user is able to manually type them in any language. Thanks for your reply with answer. – John Smith May 05 '15 at 14:43
@JohnSmith That's still not much of an explanation, but it's something at least. Suggest you edit your question accordingly. – Strawberry May 05 '15 at 14:44

score 1 · Answer 2 · answered May 05 '15 at 15:07

1

Normalize! Since you are already doing it:

// others omitted
activity(id, description)
day(id, time_id, activity_id)
schedule(id, day_id)

I might miss something, but open for correction.

answered May 05 '15 at 15:07

daxeh

1,083
8
12

I think full normalization is the way to go, after all I found an example, except it is related to something, but they normalized up to 4 tables. – John Smith May 05 '15 at 15:09
1

Thats great, I think you are already/almost there. Just needed to identify the entities, and then only normalize or remove redundant entities.. 2 to 3normal-form is good enough given the relationships unless its really complex. But your schema is heading the right with a few tweaks. No harm having more tables, good to avoid redundant data. Hope this helps. – daxeh May 05 '15 at 15:14

Daily Time Schedule (MySQL)

2 Answers2