Design problem

[Sublime]

Hi everyone.

 

I’m having some trouble designing a database and I thought I’d ask for a little help. We need track the people coming to our facility and which services they use while here. Here are the details on what we need to do:

 

Every day we need to generate a list of the guests currently in the facility and a check list for each of them that shows which of the offered services were done or refused by the guest. (Guests will be in the facility from one day to several weeks. )

 

I thought that maybe three tables would work:

Guest 

Day

Join_Guest_Day

 

The Guest table that would have people’s ID and other information, plus their arrival date and a departed checkbox. The Join Table would have all the offered services checkboxes and such. The Day table would have the current date and a portal to the join table. I could write a script that we would run daily that would create a new record in the join table with the current date for every guest that has an empty departed checkbox. 

 

I am really not sure if I’m on the right track or not -- is there is a better way to go about this?

 

Thanks in advance for any and all help.

[eos]

If all your Day table includes is the current date, then you don't really need it..

Also be aware that if you plan to report on the usage of services, you should not use a list (checkbox).

Consider

People (id)  --< Guest (id, id_person, dateArrival, dateDeparture) --< GuestDay (id, id_guest, date) --< GuestDayService (id, id_guestDay, id_service, used / refused) >-- Service (id, activeStatus)

in brief:

People  --< Guest --< GuestDay --< GuestDayService >-- Service

and create

• records in GuestDay for every guest who is not departed, and
• records in GuestDayService for the combinations of these new GuestDay records and every service that is currently offered.

Whether you need the Service table depends on if you have more to say about a service than the mere fact that it exists. 

[comment]

IMHO, you need to have these three tables:

Guests -< ServicesProvided >- Services

The Guests table is exactly what you describe. The Services table is a catalog of all services offered by your facility. ServicesProvided is a join table with an individual record for each usage of a service by a guest. As such, it would have (at least) fields for:

• GuestID - matchfield to Guests table
• ServiceID - matchfield to Services table
• Date

This would allow you to produce any kind of report on the provided services . To produce a daily report of services provided to each guest, you would only need to find the ServicesProvided records whose date matches the date of interest and sort them by guest.

I am not sure why you would need such report to also show the services that were not provided to a guest. While it is possible, I would look for a way that does not require maintaining dummy records for something that did not happen. We already know what did not happen, by recording what did.

I also don't see why you would need a separate table with a record for each Guest/Date combination. Again, this information is already available from the Guests table, where each record has an arrival and a departure date (note: a departure date, not a checkbox).

Edited July 18, 2015 by comment

[Sublime]

Thanks, 

Some more info: There are only 5 daily services (that will not change), and for legal reasons we need to list either done or refused for each guest every day. 

 

[comment]

for legal reasons we need to list either done or refused for each guest every day. 

That doesn't mean you have to actually keep records of both  - see the attached:

 ServiceLog.fp7

Note that such report would have to be produced from the Guests table - after finding guests whose ArrivalDate ≤ gReportDate and DepartureDate ≥ gReportDate.

Edited July 18, 2015 by comment

[Sublime]

Thanks comment, that example file was very helpful.