Table Structure (1 Viewer)

Alexander Willey

Registered User.
Local time
Today, 07:14
Joined
Mar 1, 2013
Messages
31
Dear Forum

I am creating a simple timesheet database (see attached form layout)
And wanted help to get the table design right.
i was thinking this would require 3 tables

AdvisorName
Week
TimeSheet

I was thinking that AdvisorName would link to Week with 1-M and that week would have a compound key. And that Week would link to Timesheet with 1-M which in turn would have 3 keys (AdvisorID, WeekID, TimeSheetID) so a compound key of 3.

1) is this arrangement sensible?
2) How do you suggest the Week data is handled. Should I do it with a date field or a number field

Appreciate any help on this
Many thanks

Alex
 

Attachments

  • Presentation1 (2).zip
    29.4 KB · Views: 117

MarkK

bit cruncher
Local time
Today, 00:14
Joined
Mar 17, 2004
Messages
8,178
It doesn't make sense to me that Week is a table. Time is a dimension of an object, not an object in itself, so your time data should be a field in a table.

Record time as the date of birth of a person, or the date/time an employee punched in, or the OrderDate or transaction date, but see how time is always an attribute (a field, a dimension) of a more complex object, never a stand-alone object.

hth
Mark
 

MarkK

bit cruncher
Local time
Today, 00:14
Joined
Mar 17, 2004
Messages
8,178
At worst I would store a "TimeZero" as a constant, and then calculate week numbers from dates using your "TimeZero" benchmark.
Code:
Global Const TimeZero As Date = #1/1/2010#

Public Function GetTimeZero as Date
   GetTimeZero = TimeZero
End Function
So then, if you want all the punches from week 402, you do math in the WHERE clause, like...
Code:
SELECT t.PunchID, t.EmployeeID, t.PunchDateTime, t.IsPunchIn
FROM tPunches As t
WHERE DateDiff("w", GetTimeZero(), t.PunchDateTime) = 402
So your stored date/time value is, as it should be, a dimension or property of the punch object. Then, to determine the week in which that punch falls, you do math on your stored date/times, maybe using DateDiff() as demonstrated in the WHERE clause, above.

hth
Mark
 

plog

Banishment Pending
Local time
Today, 02:14
Joined
May 11, 2011
Messages
11,613
Agree with Mark. 3 tables sounds over normalized--your tables sound like they should simply be fields.

Mock something up in Access, complete the Relationship tool and then post a screenshot of that.
 

Alexander Willey

Registered User.
Local time
Today, 07:14
Joined
Mar 1, 2013
Messages
31
Thank you very much

My vision for my form layout is:

Main form: Advisor
Subform: Week
Subsubform in table layout: WeeksTimeLogs

and so I am struggling to consider a table layout other than 3 tables connected one to many

Any ideas?

thanks for your help

Alex
 

plog

Banishment Pending
Local time
Today, 02:14
Joined
May 11, 2011
Messages
11,613
Any ideas?

Yes, stop thinking about forms. They are the last step in the process.

Tables, then Reports, then Forms. No sense building a beautiful input system if you can't get the data you want out of your database. So read up on normalization (https://en.wikipedia.org/wiki/Database_normalization), work through a tutorial or two and then work on normalizing your data. Then sketch out the reports you want out of your database. Then finally work on forms.
 

Alexander Willey

Registered User.
Local time
Today, 07:14
Joined
Mar 1, 2013
Messages
31
Thank you very much for pointing out the importance of getting the design right. I have tried to attach a design.
I would like the user to select their 'Advisor' name (at the top of a form), then, within that form, for another form to display week number or week commencing, and then within that form a table where the user will add DateofLog, Project Code and Time over and over as required. When the week has finished, they then move to the next week
The user needs to add their data by week, that is to say, a week per form (page), scrolling through forms week by week (as the year passes)
What I am struggling with and need help or guidance is to know a strategy for how to create week number or week commencing, for those views from a date that has not yet been selected.
I hope that makes sense, thank you in advance for any help,
Kind regards
Alex
 

Attachments

  • Design 18.09.17.zip
    155.2 KB · Views: 112

MarkK

bit cruncher
Local time
Today, 00:14
Joined
Mar 17, 2004
Messages
8,178
I don't understand why the week number or week commencing matters in advance. Does the data/event you are recording occur on a date? If so, how is that not enough information?

Let's say I need to to record a dollar amount in an accounting system. I do not need to select in advance whether the amount is between $10 and $15 dollars, I simply enter the amount. This should be true with time also. Enter the date/time at which the event occurs. After that, you are free to determine the day, the week, the month, the year by doing math on the date. Is there something I am missing?
hth
Mark
 

plog

Banishment Pending
Local time
Today, 02:14
Joined
May 11, 2011
Messages
11,613
If you're dead set on your forms, why not build those exactly like you want them now? Open up photoshop and have at it? Then convert them to Access, then if you have time build some tables.

Honestly, from what you posted, you don't need a database. A table with only 1 real field of data (autonumbers don't count) shouldn't exist. That means the only table you really have is tblTimeLog.
 

Alexander Willey

Registered User.
Local time
Today, 07:14
Joined
Mar 1, 2013
Messages
31
Thank you,

For the user, moving forward through forms, showing upcoming weeks is to provide a simple structure for the users

I am trying to mirror an existing accounting timesheet system that shows future dates and the user writes against those weeks/days

I am thinking in terms of forms only because simple tables, queries and forms is what I can currently cope with. The system will be used by 20 or so staff and so I have to have their end user-view (forms to enter data) in mind

The scenario is part of a bigger albeit simple database with more fields, but I shared just a simple part of it to keep my question simple

Thanks again for your help,

Kind regards,

Alex
 

jumbotrano

Registered User.
Local time
Today, 00:14
Joined
Sep 6, 2017
Messages
12
If you're dead set on your forms, why not build those exactly like you want them now? Open up photoshop and have at it? Then convert them to Access, then if you have time build some tables.

Honestly, from what you posted, you don't need a database. A table with only 1 real field of data (autonumbers don't count) shouldn't exist. That means the only table you really have is tblTimeLog.
Quite a good point
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:14
Joined
Feb 19, 2002
Messages
42,981
I see four tables but not the tables you see.
tblAdvisor
AdvisorID (autonumber, primary key)
LastName
FirstName
etc.
tblProjects
ProjectID (autonumber, primary key)
ProjectDesc
etc.
tblActivities
ActivityID (autonumber, primary key)
ActivityDesc
etc.
tblTime
TimeID (autonumber, primary key)
AdvisorID (FK to tblAdvisor)
ProjectID (FK to tblProject)
ActivityID (FK to tblActivity)
ActivityDate
ActivityTimeHours
ActivityTimeMinutes
etc.

If Activities are dependent on Projects, then your schema changes to add a fifth table to relate Projects and Activities and then the time table uses the ProjectActivityID rather than both ProjectID and ActivityID.

Having a timesheet show a week at a time is fairly standard and makes sense for presentation. So at the top of your form you would enter either a week number and a year (default to current year) or a week start date (mondays only or whatever day your week starts on) That date criteria along with the AdvisorID will be used to select a set of data for the form and subform.
 

Users who are viewing this thread

Top Bottom