Good day.

There are a lot of manuals for writing event calendars on the web, there are even frameworks, but I want to write a calendar for keeping records of duties with the admin panel.

Differences from event calendars:
There are no events that are tied to time.
There is a direct bundle of people -> date (day), there is no time reference.
And a person can have a different status on this day (for example, he is sick, at work, sent), but only one status (he cannot have 2 status at the same time: sick and sent, for example.)

I'm not sure that my mysql database has the right architecture, if anything, correct me.

The table is called <month>-<year> (that is, a new table is formed for each month, for example, 11-2014 for the current month).
Screenshot in the application .

Table fields:

 ID Name 1 <- Это поля дней месяца, в которых содержатся специфические ключи: Болен (S), Выходной(W), Дежурный (D) 2 3 4 .. 31

The user interface is easy to get by simply querying the desired table and forming an HTML table using PHP. Screenshot in the application .

The problem with the administrative interface when formatting data on attendants.
The selection of the required fields for the future status assignment implemented using the selectable jquery UI library. Screenshot in the application .

The question is: I do not know in what form and how to save the data in the database using ajax.

1) It is better to correct the contents for the selected positions to the desired status (put down the status D in all the selected cells, for example), and then send the ajax with all the cells to the database.

2) Or immediately form a request to the database for the selected cells and send information to the database only for the selected ones, then reload the page to form already actual data?

Well, the general question is how to correctly send data to the database using ajax? (I study jquery for the second day, do not kick much, did not work with javascript before.)

How can I correctly send data to the database from each cell?

Thank!

  • 2
    And why for each month a new table? - dlarchikov
  • Well, xs. Store month and year in separate fields of one table? It feels like, with a large amount of information in the database, the handler will quickly find the table from the list of tables than by iterating all the records in one table, comparing the month-year pair - SaLacoste
  • @SaLacoste you start writing a solution to a performance problem before it appears. This thing is called sharding, and, most likely, it will be better to implement it inside the engine. - etki
  • @Etki, ok, but on the topic of the question can you help? - SaLacoste

2 answers 2

The structure chosen is terrible. And the table must be one, and the field for the status is also one, and the field for the date must also be the same: they will always be both used in all queries! You will never need all the days of the year, and you will never need all the days of January for all years. For one day one employee should be one line.

And what data to send by Ajax, in general, there is no difference. At least a month, even if only changes, if there is no joint editing by different users, then the data will be (if there is a connection / proper server operation) both on the client and on the server are identical, and there is no need to refresh.

In addition, for general weekends / holidays, make a separate table with the production calendar. In this case, it is really enough for employees to keep only the differences, connecting them along the left join. By the way, there are different work schedules: five days, six days, three days, two after two, etc.

Therefore, at least:

Calendar : ( day_of_year , status ), filled once a year on buh.ru and the desired schedule.
Schedule : ( person_id , day_of_year , status ) - differences from the production calendar. Plus a unique index on ( person_id , day_of_year ).

 select c.day_of_year, ifnull(s.status, c.status) as status from calendar c left join schedule s on c.day_of_year = s.day_of_year and c.person_id = $person_id where c.day_of_year between $beginofmonth and $endofmonth insert into schedule (person_id, day_of_year, status) values ($person_id, "2014-04-01", "w"), ($person_id, "2014-04-02", "s"),... on duplicate key update status=values(status)

Threat While the screens did not look (I had to register!), I thought that everything was in order ...

Update

You asked for comments on the data structure ... If you only show the data, then the structure is not important (although I am not a supporter of this approach), you can even store it in a file, in a serialized form, for example.

By ajax you will need:

  1. in the handler to get the data to be sent, call $.post('update.php', data) ; http://jsfiddle.net/w4rjnr4c/

  2. in the php file, get the data array, process it and write it to the database.

You do not need to refresh the page ...

  • @Yura Ivanov, thank you very much for the objectivity of the presentation. My need is much simpler, since I don’t make a working time calendar or work schedule, and this is the duty schedule at home for personal use of our department. Thus, there is no connection with weekends, holidays, as well as a calendar of working time for a year. A person has just 3 statuses (key) or an empty field (when he is not sick, is not on duty and is not sent). I have not reviewed the database yet, but I collected what is currently jsfiddle.net/7m7qhrhh/1. I do not know how to write the data in a database in ajax. - SaLacoste
  • @SaLacoste, Updated the answer - Yura Ivanov
  • @Yura Ivanov, thanks for coming! - SaLacoste

It seems that you want from the structure of your base some kind of visual correspondence with the real image of the calendar. And why is it necessary to put a certain status on every day? Let the default person at work - this is the norm. Therefore, you only have to store the date (Day-Month-Year) and status (status identifier from the table as an option), and employee identifier from the workers table. An empty database is a calendar without notes — all days except weekends, people were at work. You better visibility on the interface shift, rather than on the structure of the database.