I am trying to produce a time-table creator with multiple stores and every store with multiple employees. Each store may also access past schedule produced, only the present schedule is flexible. Generate an income have my SQL database setup is the fact that I've two tables, stores and employees. Each worker includes a store they work with, and time for those the era of the week, and so i saved employees all the shops in a single table and obtain them as necessary with queries.
My real question is, must i add another column (week) towards the worker table to designate which week that schedule is perfect for, or produce a new html apply for each schedule for your store's past agendas? I love the 2nd choice since it decreases the likelihood of my SQL database being corrupted. As past agendas are unmodifiable, I've no difficulties with it.
P.S. simply to make certain: basically email personal files using php, say
$name = "monir"; write("My name is $name"). Will the file say
"My name is $name" or
"My name is monir"?
Following the idea of Database normalization you need to add another table known as e.g. schedule which consists of a mention of the an worker along with a store. Additionally to that you ought to place your week number within this schedule table to distinctively identity your table row.
My questions is must i add another column (week) towards the worker table
I recommend developing a
schedules table. Employees would then fit in with the schedule and also the schedule would fit in with an outlet. This gives more versatility and enables you to definitely better store past agendas.
I love the 2nd choice since it decreases the likelihood of my SQL database being corrupted.
You won't want to avoid an engaged solution (table) in support of a static one (html page) only to prevent customers from upgrading information. You just need to create access rules inside your application.
You ought to have store, worker along with a table that joins the shops and employees as stores and employees don't have a 1-to_one relationship. Everybody I've ever known who labored in retail has already established occasions once they were requested to work on other stores.
Schedule ought to be inside a separate table using the worker id, a few days and also the scheduled hrs. A trigger ought to be placed on this table to avoid agendas from past dates from being up-to-date. Alternatively you might have a view that exposes just the current schedule and future agendas making all updates make use of the view but chooses may use the entire table. This could allow a database admin to alter yesteryear agendas if necessary although not the applying because it just uses the vista.
Because the comments produced by DA and also the other solutions indicate, getting another table is what you want. At least, try getting the next:
store: id title address etc.
worker: id first_title last_title etc.
schedule: id store_id worker_id week date start_time finish_time
While week is redundant since date is saved, it enables you to definitely query considerably faster. You do not need the archive/past bit since you can just query whether schedule.week may be the current week or otherwise.
I have really just done something quite similar, but our bodies is really a whole hell of much more complicated than this.
Personally, given your current structure, I'd decide to add the additional column. It's little more work, and provides you with a chance to reproduce the HTML files afterwards. I understand you won't want to have the ability to modify past agendas, but that is more what I'd call business logic - so just prevent it from being possible somewhere inside your code.
I'd have otherwise recommended you add another extra table for "days". Provide an ID, the beginning date and also the finish date, then use that "week_id" elsewhere in order to save duplication. You might separate out schedule records from worker records, so each worker might have a distinctive ID as well as your "schedule" table could be: ID, worker_id, week_id, date, start_time, finish_time.
It'd provide you with a much more treatments for your queries and lower the duplication within the database quite substantially for afterwards. Also, unless of course your database is actually really unstable, corruption should not be considered a problem.
For the P.S. - as lengthy while you surround your output in double-quotes, PHP will interpolate your values. So you will get "I'm monir", instead of "I'm $title".