1

I've found slightly relevant posts here: SQL Schedule Calendar

and here: SQL Scheduling - Overbooked Report

But these are really based on retrieving from tables, I'm looking to create a table or tables. Even if you can just help me out with what to google for help. Unfortunately I'm trying to create what I would call a schedule, but since the word schedule is already used for something else in SQL it is hard to google.

A few bits of background info: 1 - I would like to use SQL, as I have a tiny bit of knowledge about using it to retrieve data (never created tables or designed databases, just retrieved), though I open to other suggestions if something better or easier than SQL can do the job.

2 - based on limited research, I like the idea of SQLite - again, open to suggestion

3 - this will be interrogated by a Python program on a Raspberry Pi which will turn the lights on and off using GPIO

There are a lot of demands below, but this is a learning process, I'm using this toy project to teach myself Raspberry Pi, Python, SQL, and probably PHP one day. So I really appreciate any help, be it code snippets or simply just pointing me toward resources that will help me learn. I'm quite new to this forum so I hope I'm behaving!

This is a picture of what I'm trying to achieve, its basically a schedule for turning security lights on and off: Sample of calendar output

This is Excel, and this table has some obvious limitations. Here are some demands not currently met by this Excel table:

I would like to be able to have any "on" and "off" times, not just hour-long slots. I would like to be able to have overlapping events. I would like, ideally, to have events set for individual days, but also for "weekdays" and "weekends" I would like to have temporary and permanent events - so ideally, a user can say "turn on from X time to Y every night this week" without affecting the permanent schedule. I would like to easily expand "front" and "back" to any number of other lights (this is a very long-term demand but future proof and re-usability is nice) Ultimately, I would like to have a web-based interface, where a user can easily log in and set a temporary or permanent change to the schedule.

Any help is hugely appreciated.

fishkake
  • 41
  • 1
  • 1
  • 6
  • Welcome to stackoverflow. Please take the a few minutes to read the [asking](https://stackoverflow.com/help/asking) section of the help center. Long story short - your question is likely to be closed as too broad, and for this kind of questions, you better look for somewhere else to post them, like a professional forum (which SO is not). – Zohar Peled Jul 23 '17 at 15:21
  • I did read up a bit, trying desperately not to break the rules - I don't get how this is too broad, I've included a picture showing exactly what I want to achieve, and asked for some help to show me what tables to create. I read the "what not to post" page and didn't think I'd broken any rules. – fishkake Jul 23 '17 at 17:36
  • Would it make more sense if I asked just a simpler version, such as what table structure to use for a simple schedule, and brought the rest in as different questions? Not having a hissy fit, genuinely trying to improve! – fishkake Jul 23 '17 at 17:38
  • OK, checked again and I might have breached some of the brevity points made in "how to ask a good question". I will try again, more specifically. Thanks for your pointer. – fishkake Jul 23 '17 at 17:47

0 Answers0