Rick Willingham said:
Hi Ed,
Thank you for the detailed response. Allow me to explain my thought
process on some of the things you commented on and maybe you can
evaluate...(comments in-line)
----- Ed Warren wrote: -----
Rick
Moving in the right direction. Keep up the good work, here are some
suggestions, please these are not a complete review of the tables you have
provided.
The following is not a complete list but some things to consider.
tblTimeClock:
You have duplicated information with the fields TimeClockDate and
PunchInTime; PunchOutTime PunchIn/out are date/time fields and serve to
provide the date along with the ability to calculate the 'time punched in'
using a datediff( ) function. So you don't need to have the TimeClockDate
field.
tblTimeClock
I went with a date field and in / out fields for several reasons. First,
I wasn't that smart. Second,the user might enter the date of the activity
one or more days AFTER the event of the activity. The entry might be based
on a TimeClock report where all events are sorted by date, or on time cards
where the events would be sorted by installer. The user is (typically)
already using a (non-input masked) Date field in the companies main
database. Third, I have a listbox and the listbox is re-sortable by the
user by date, first name, last name, time-in, time out. (Alot of searching
to find that code). I expect the reports that I have yet to create will
have similar sorting needs.
BonusID is also tied to the Installer so probably not needed in the
tbltimeClock, you can recapture that via the installer table.
Also BonusID provides all the information required for calculating the Bonus
amount so BonusAmount can go.
BonusID - I've been seeing that calculated or duplicate fields are
generally frowned upon. In this case, I want the value saved because the
rate might change in the future and I don't want this value updated when
that event occurs.
If no bonus reason is a free text field and different for each entry it
stays if not it could be wrapped up into the BonusType table.
BonusReason - This is a duplicate field, but I have to change forms,
queries, macros, just to eliminate it... I will definately put more thought
into the avoidance of duplicate fields should I do another DB.
May even be able to combine the BonusID/NoBonusID into one concept and only
have one table/lookup and field in the table e.g. BonusTypeID.
I have two separate tables because... Of all of the possible bonuses the
user might put into the system, two of them are pre-defined and used for
automated calculation and the rest are not. Those two are 'loading
bonuses'. If the installer meets certain timeclock rules, he earns the
bonus. The 'loading bonus' he earns is dependant upon his current skill
level. If he doesn't meet the rules, then the reason for NOT getting the
bonus is calculated by update queries.
Installers table has a bunch of stuff not related to the object Installer:
vinID assigned, current hourly rate (pull out into another table), date
achieved installerX status should be pulled out into another table.
My thought on the VanId is that an installer is typically associated with
one van, but one van may be associated with more than one installer. Also,
in JobActivity I want to allow for the possibility that an installer might
be in a van other than his 'normal' van. And I hope to make the form such
that when an installer is selected from a combo box that the 'default' van
number comes up as primary choice.
I don't understand why I would want current hourly rate in another table -
the rates are individualized - not standardized, and the information is seen
only by authorized accounting people. Maybe explain in more detail?
I don't understand here either. As far as I can determine, the levelX
dates are functionally dependant upon the PK. (Over the weekend I read about
normalization.) The table might be split, but I cannot think of a good
reason as to why.
What happens when you add another level of qualification? You have to go
back and change your basic database design.
With the tables split you only need to add a row in the related table with a
new level of qualification (no change in database design).
You are also carrying around extra 'blank' data for all those that have not
completed level 4 qualification.
Table Activity. You need a
tblClients:
ClientID (PK primary key)
Client First name
Client Last Name
Client Address1
Client Address2
Client City
Client State
Client Zip
Client Telephone
Then
tblClients (PK-CleintID) 1: M (FK-ClientID) tblOrders. "Table Clients
relates to tblOrders in a one to many relationship with ClientID as the
primary key in tblClients and ClientID the foreign key in tblOrders.
The good thing about being the designer, is you can 'have it your way'. My
ideas are just thoughts without knowledge of the business case you are
trying to model. If always 1:1 you need to keep this the way you have it.
I agree. Unfortunately, this company has a one-to-one/Client-Order
relationship. A new order means a new Client number. No point in changing
that now. Sorry I neglected to mention that when I posted this, could have
saved people a lot of thier analysis time...
Try to think of each table as an Object e.g. employee. The tblEmployee
should only have information that applies to that object and not include any
information about how that object relates to other objects.
Example An order has a:
OrderID (PK)
clientID (FK)
date/time ordered
date/time install started
date/time install ended
installerID (Fk) to Installer
JobLevelID (FK)
JobLevelCommissionRate
JobCommissionAmount (May really be a calculated field based on a formula ( =
[TotalOrderAmount]*[JobLevelCommissionRate]) and not stored in the database.
Except that I wanted to store the value in case the commission rate changes in the future...
total order amount
EndOfServiceResponsibilityDate (if this is a constant date from date install
ended then can be calculated and not required in the database)
The good thing about being the designer, is you can 'have it your way'.
The problems I have run into with this 'pattern' has been with mileage rates
(very similar problem). The rate changes, the user enters data for a date
before the change, but does the data entry after the new value has been
entered and the wrong value is calculated and saved. I solved this by
having a table
tblMileage:
MileageID: PK
Date date/time
MileageRate Currency
In your case maybe
IndividualCommissionID: PK
IndividualID (FK)
Date date/time
Commission Currency
or
IndividualCommissionID: PK
IndividualID (FK)
ComissionTypeID (FK)
Date date/time
Commission Currency
or
Keep it as you have already done (it works)
Now given the date of the entry, I can find the MileageRate for the maximum
DateTime in the tblMileage occuring BEFORE the date of the entry.
Right , this does not have to be saved - it will be coming out...
tblOrderPayments:
PaymentID (PK)
OrderID (FK)
PaymentDate (date time)
AmountReceived (currency)
Orders (OrderID PK) 1:M TblOrderPayments (OrderID(FK))
Note payments are not part of the order object, they exist on their own as
payments. With this structure you can run a query that will tell you how
much has been paid on each order and total balance. Find clients that have
not made a payment in xx days and more. Note, this structure will need to be
modified if you have clients that have multiple order any pay for several of
them in the same payment. But that is a more complicated issue.
The intent of the application is to calculate installer commissions, etc.
and not track the orders and payments. The company has a DB for tracking a
pile of information (clients and orders and payments, etc), but so far no
one here has been able to explain to the developer how the commissions
really work and all of the variables that the users have in their current
manual systems. So because I'm challenged by this, I jumped in thinking
that I might be able to solve the problem and earn some sort of bonus.UMMMM--- Congratulations, now the truth comes out!!
Trying to capture a manual system in a database.
This is where we all started and you are well ahead of where I was the first
time I walked that path.
You will find People are by their nature not consistent with how they handle
data and sorting through the levels of complexity will provide years of
fruitful work, frustration, and reward. My only recommendation is work,
work, work to understand the system you are modeling before going too far,
but at some point you will have to 'jump in' and start swimming. You will
probably build several versions of this before you are done. (we all go that
way).
Best of luck, keep kicking.
Ed Warren
I hope this is enough to give you encouragement but not so much to
discourage, you're on the right track, just not quite in the station yet.
Thank you again for all of this good information and your responses, very
valuable to me and hopefully others.
