我如何保存日期/出席人数?

I'm looking for for an opinion.

我在找一个意见。

I have a list of people and will need to store when they are present at a location so those in charge can check them off a list. I'm not 100% sure how long the dates will be needed but I'm assuming they may need to look at previous attendance lists.

我有一个人名单，当他们在一个地方的时候，他们需要去商店，所以那些负责人可以从名单上核对他们。我不能百分之百地确定需要多长时间，但我猜想他们可能需要查看以前的出勤名单。

My first instinct is to have a column for each date but that could result in many many columns. I could just store a list of dates next to each person:

我的第一反应是每个约会都有一个专栏，但这会导致很多专栏。我可以把日期列表放在每个人的旁边:

"01/01/2012,01/15/2012,02/18/2012..."

“01/01/2012 01/15/2012 02/18/2012……”

that could result in a very long entry. It seems like neither is a good option.

这可能会导致一个很长的入口。似乎两者都不是一个好的选择。

If anyone has a suggestion or guidance on an approach please let me know. Thanks.

如果有人对方法有建议或指导，请告诉我。谢谢。

4 个解决方案

#1

A complex, but also very clean approach would be

一个复杂但又非常干净的方法是

Table "persons":

表“人”:

id
id
name
的名字

Table "dates":

表“日期”:

id
id
location
位置
date
日期
... whatever info the "dates" table needs
…“日期”表需要的任何信息

Table "attendances":

表“上座率”:

date_id (link to an entry in the "dates" table)
date_id(链接到“日期”表中的条目)
person_id (link to an entry in the "persons" table)
person_id(链接到“person”表中的一个条目)
attended (yes/no)
参加(是/否)

Then fill the database with the appropriate dates, and fill the "attendances" table according to which persons need to be present at each date.

然后在数据库中填入适当的日期，并根据每个日期需要出席的人填写“出席”表。

This is, as said, complex to implement, but it's incredibly flexible - you can have any number of dates and attendees; you can excuse people from attending a specific date programmatically; you can add people to groups...

如前所述，实现起来很复杂，但是它非常灵活——您可以有任意数量的日期和参与者;你可以允许人们以编程的方式参加一个特定的约会;你可以把人添加到组中……

#2

Link tables.

链接表。

One table of people

一个表的人

ID
ID
Name
的名字

One table of classes

一个表的类

ID
ID
Name
的名字

One table linking person to class to date.

一个表将person与类连接到date。

ID
ID
personID
personID
classID
classID
cDate
cDate

So all you would need to do to determine which students were preset on a certain date in a certain class:

所以你需要做的就是确定哪些学生在某一课上的某个日期是预先设定的:

SELECT *
FROM people p
LEFT JOIN peopletoclass ptc ON p.id = ptc.personid
LEFT join class c ON c.id = ptc.classid
WHERE ptc.cDate = '2011-11-07' AND c.id = '1';

Above (for example) would get all people in class id 1 on November 7th 2011.

上面(例如)将会在2011年11月7日让所有id为1的人。

#3

Create a table "attendance" consisting of a person_id field and a date_present field. You can't store this into columns or a long list using a string ;-).

创建一个由person_id字段和date_present字段组成的表“考勤”。不能使用字符串将其存储到列或长列表中;-)。

Than you can use queries where you join the table Person with Attendance.

您可以使用查询来连接表Person。

#4

Your first instinct would result in a horrible table design. What you should have is a seperate table that stores the users/locations/dates tuples

你的第一反应将导致糟糕的桌面设计。您应该拥有一个隔离表，存储用户/位置/日期元组!

e.g.

如。

userID   locationID   date

1       party          1/1/2011 00:00:00
1       bathroom       1/1/2011 00:05:00
1       party          1/1/2011 00:15:00

would show that user #1 was at a New Year's Eve party, then went to pray before the porcelain altar at 12:05am, then returned to the party 10 minutes later.

会显示用户#1是在新年前夜的聚会上，然后在12点05分的陶瓷祭坛前祈祷，10分钟后又回到聚会上。

#1