数据库设计选择
我正在尝试创建一个工作时间表创建器，其中包含多个商店，并且每个商店都有多名员工。每个商店还可以访问过去创建的时间表，但只能修改当前的时间表。我的 SQL 数据库设置方式是有两个表：stores 和员工。每个员工都有一个他们工作的商店，以及一周中所有日子的工作时间，因此我将所有商店的员工保存在一个表中，并根据需要通过查询获取它们。
我的问题是，我应该在员工表中添加另一列（周）来指定该时间表是哪一周，还是为该商店过去的时间表的每个时间表创建一个新的 html 文件？我喜欢第二个选择，因为它减少了 SQL 数据库被损坏的机会。由于过去的日程安排是不可修改的，所以我对此没有任何问题。
PS 只是为了确保：如果我使用 php 写入文件，请说 $name = "monir"; write("我的名字是 $name").文件会显示“我的名字是$name”还是“我的名字是monir”？
I'm trying to create a work schedule creator with multiple stores and each store with multiple employees. Each store can also access past schedule created, but only the current schedule is modifiable. The way I have my SQL database setup is that I have two tables, stores and employees. Each employee has a store they work for, and time for all the days of the week, so I saved employees from all the stores in one table and get them as necessary with queries.
My question is, should I add another column (week) to the employee table to designate which week that schedule is for, or create a new html file for each schedule for that store's past schedules? I like the second choice because it decreases the chances of my SQL database being corrupted. As past schedules are unmodifiable, I have no issues with it.
P.S. just to make sure: if i write to a file using php, say $name = "monir"; write("My name is $name"). Will the file say "My name is $name" or "My name is monir"?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
遵循数据库规范化的概念，您应该添加另一个名为例如时间表的表，其中包含对员工的引用和一家商店。除此之外，您还应该将周数放入此计划表中，以唯一标识您的表行。
Following the concept of Database normalization you should add another table called e.g. schedule which contains a reference to an employee and a store. In addition to that you should put your week number in this schedule table to uniquely identity your table row.
正如 DA 的评论和其他解决方案所表明的那样，拥有第三张表是正确的选择。至少，尝试进行以下操作：
存储：
编号 |名称 |地址 |等
员工：
编号 |名字 |姓氏 |等
时间表：
编号 |商店 ID |员工 ID |周 |日期 |开始时间 | end_time
虽然由于存储了日期，所以周是多余的，但它允许您更快地查询。您不需要存档/过去位，因为您只需查询 Schedule.week 是否是本周。
As the comments made by DA and the other solutions indicate, having a third table is the way to go. At a minimum, try having the following:
store:
id | name | address | etc.
employee:
id | first_name | last_name | etc.
schedule:
id | store_id | employee_id | week | date | start_time | end_time
While week is redundant since date is stored, it allows you to query much faster. You don't need the archive/past bit because you can just query whether schedule.week is the current week or not.
我建议创建一个
schedules表。然后，员工将属于该时间表，而该时间表将属于一家商店。这提供了更大的灵活性，并允许您更好地存储过去的日程安排。您不想仅仅为了阻止用户更新信息而避免采用动态解决方案（表格）而采用静态解决方案（html 页面）。您只需在应用程序中创建访问规则即可。
I would suggest creating a
schedulestable. Employees would then belong to the schedule and the schedule would belong to a store. This provides more flexibility and allows you to better store past schedules.You don't want to avoid a dynamic solution (table) in favor of a static one (html page) simply to prevent users from updating information. You simply need to create access rules in your application.
您应该有商店、员工和一个连接商店和员工的表，因为商店和员工没有一对一的关系。我认识的每个从事零售业工作的人都有过被要求到其他商店工作的经历。
时间表应位于单独的表中，其中包含员工 ID、周数和预定时间。应在此表上放置一个触发器，以防止更新过去日期的计划。或者，您可以有一个仅公开当前计划和未来计划的视图，并使所有更新都使用该视图，但选择可以使用整个表。这将允许数据库管理员在需要时更改过去的计划，但不能更改应用程序，因为它只使用视图。
You should have store, employee and a table that joins the stores and employees as stores and employees do NOT have a one-to_one relationship. Everyone I have ever known who worked in retail has had times when they were asked to work at other stores.
Schedule should be in a separate table with the employee id, the week and the scheduled hours. A trigger should be put on this table to prevent schedules from past dates from being updated. Alternatively you could have a view that exposes only the current schedule and future schedules and make all updates use the view but selects can use the whole table. This would allow a database admin to change the past schedules if need be but not the application since it only uses the view.
实际上我刚刚做了一些非常相似的事情，但是我们的系统比这复杂得多。
就我个人而言，鉴于您目前的结构，我会选择添加额外的列。这不需要太多的工作，并且让您能够在事后重现 HTML 文件。我知道您不希望能够修改过去的计划，但这更多的是我所说的业务逻辑 - 所以只需防止它在代码中的某个地方出现即可。
否则我会建议您添加另一个额外的“周”表。给它一个 ID、开始日期和结束日期，然后在其他地方使用该“week_id”以节省重复。您还可以将日程表条目从员工记录中分离出来，这样每个员工都有一个唯一的 ID，您的“日程表”表将是：ID、employee_id、week_id、date、start_time、end_time。
它可以让您更好地控制查询，并在以后大大减少数据库中的重复。另外，除非您的数据库确实非常不稳定，否则损坏不应该成为问题。
对于您的 PS - 只要您将输出用双引号括起来，PHP 就会插入您的值。所以你会得到“我的名字是 monir”，而不是“我的名字是 $name”。
I've actually just done something quite similar, but our system is a whole hell of a lot more complicated than this.
Personally, given your present structure, I'd choose to add the extra column. It's not too much more work, and will give you the ability to reproduce the HTML files after the fact. I know you don't want to be able to modify past schedules, but that's more what I'd call business logic - so just prevent it from being possible somewhere in your code.
I'd have otherwise suggested you add another extra table for "weeks". Give it an ID, the start date and the end date, then use that "week_id" elsewhere to save duplication. You could also separate out schedule entries from employee records, so each employee would have a unique ID and your "schedule" table would be: ID, employee_id, week_id, date, start_time, end_time.
It'd give you a lot more control over your queries and reduce the duplication in the database quite considerably for later on. Also, unless your database is really really unstable, corruption shouldn't be a problem.
For your P.S. - as long as you surround your output in double-quotes, PHP will interpolate your values. So you'll get "My name is monir", rather than "My name is $name".