Good table design is a great foundation for a successful
application stack. Table design patterns basically resolve
into master tables and transaction tables. When we know
a thing or two about the master tables (or entities if you
prefer), we can infer a great deal about the transactions.
This blog has two tables of contents, the
Topical Table of Contents and the list
of
Database Skills.
A Time Billing System
Imagine we have been asked to recode the company's
time-billing system. Because this is for the company
we work for, we have some inside knowledge about how
things work. We know that:
- There are, of course, customers.
- ....and employees who record time
- Each hour we record goes against a Work Order
- There are different kinds of jobs, like
project management, programming, programming
management, and others.
Knowing only this, is it
possible to anticipate what the system will look like?
A safe answer is "no", on the claim that we will
undoubtedly learn more, but this safe answer happens
to be wrong. We can in fact anticipate
the overall shape of the system, and new information
will shift details, but it will not change the shape.
We can anticipate the nature of the transactions
if we determine the upper bound of complexity
and the combinatorial completeness of the
system.
The Upper Bound of Complexity
We can safely assume that the big number to get
right is going to be the billing rate. Our employer
assumes we will get everything else right, but the
billing rate is going to have them chewing their fingernails
until they know we understand it and have coded it
correctly.
The cool thing is that we already have enough information
to establish an upper bound on the complexity of
the system by looking at the master tables, where a master table
is generally one that lists details about real things
like people, places, things, or activities.
So far we know (or think we know) about three master tables:
- Customers
- Employees
- Services
Now we define the upper bound of complexity as:
The upper bound of complexity
occurs when the billing rate is determined by all three
master entities.
In plain English, calculating a billing rate can be as
complicated as looking up a rate specific to a customer
for a service for an employee but cannot be more
complex than that because there are no other entities
with which to work.
Combinatorially Complete
We can also anticipate all possible calculations for
the billing rate by working through the complete set
of combinations of master entities. This would look
like the list below. Note that we are not trying to
figure out right now which of these is likely to occur,
we just want to get them listed out:
- Each service has a default rate
- Each customer has a negotiated rate
- Each employee bills out at a default rate
- The combination customer-service may have a rate
- The combination customer-employee may have a rate
- The combination customer-service-employee may have
a rate (this is the upper bound of complexity, all
three master entities determine the rate).
Unless we live in a super-simple world where only the first
item in the list is present, we will end up dealing with
several if not all of the combinations listed above.
Each of these combinations then becomes a table, and
we know the billing rate will be determined by a
resolution.
New Information
Now comes the big day and we interview with somebody
we'll call "The Explainer" who is going to officially
explain the billing system. Can he break what we
already know? No. At most he can:
- Make us aware of new master entities, perhaps
there are "projects" and "contracts" that get their
own billing arrangements.
- Dispel our notions about some of the combinations
by saying, "Oh we never give a customer a default
rate, the default rates come out of the services."
Going in Cold
What about the case where we know absolutely nothing
about an assignment when we go in to begin the interviews?
We can do a good job of thinking on our feet if we draw
"The Explainer" towards the master entities. As we gain
confidence that we know what the master entities are,
we can ask questions to probe Combinatorial Completeness
and the Upper Bound of Complexity.
One caveat: This method works for transactions between
master entities. When "The Explainer" starts describing
something that cannot be recognized as an interaction
between master entities, do not try to stuff the problem
into this box, it may not fit.
What About the Application?
At this point, we can also anticipate a lot of
what the application will look like. We will need
maintenance screens for all of the master entities,
and a really slick UI will allow for very easy editing
of those various cross-reference combination tables.
As long as that much is done, we are almost finished,
but not yet.
There will be some billing process that pulls
the time entries, finds the correct billing rate for
each one, and permanently records the invoices. If
we use a resolution this task is
child's play to code, debug, and maintain.
Then of course there is the presentation, the actual
bill. Depending on the company, these may be delivered
as hardcopy or in email. That will of course have to
be coded up.
Conclusion
There are two conclusions. First, as originally stated,
many transactions can be anticipated when you know what
the master entities are.
But secondly, and every bit as important, once the table
design is sound, the application pretty much writes itself.
On a personal note, this is probably why I do not find
application coding as exciting as I once did. Once I
realized that the real challenge and satisfaction was in
working out the tables, the coding of the app became a
bit of a drudge, it requires no judgment as far as
business rules are concerned.
Update Contact :
No Wa/Telepon (puat) : 085267792168
No Wa/Telepon (fajar) : 085369237896
Email : Fajarudinsidik@gmail.com
No Wa/Telepon (puat) : 085267792168
No Wa/Telepon (fajar) : 085369237896
Email: Fajarudinsidik@gmail.com
atau Kirimkan Private messanger melalui email dengan klik tombol order dibawah ini :
