DB design, facilitates Double entries of internal transactions

[postmaster]

Can anyone give me hints on database design? I'm seeking for a good design
for my inventory system for corporate use. I really want to have a better
start-off on the design for the system.

Physical constraints:
Our company only have one standalone server to serve all users. i.e. File
sharing, database server, etc.

User requirements:
Users want me to build up a inventory system which not only stores the stock
details but also all relevant invoices involved during daily transactions.
Includes: sales/purchase, consignment-in/out, purchase return, sales return,
etc.

We have several retail shops selling products to customers. Internal
transactions may occur frequently among them each day.

My manager wants the data to be stored 'separately' in database. i.e. Each
shop can only access its own portion of data, with exception of the head
office, which can see all shops inventory data.

Also, I have to design the database to store the various types of invoices
for internal transactions. Each of the internal transaction has to keep
consistent invoices for both sides, i.e. sellor and buyer, and be stored in
the corresponding tables, e.g. S/P txn: sales invoice table for sell-side
and purchase invoice table for buy-side.

Question 1: Do I have to physically partition the database in order to meet
users requirements? This seems to be very common for most company, isn't it?
Question 2: What will be the best way to modify the database when a
transaction occurs?
Question 3: Which one is better while handing the transactional processes,
'begin tran/commit tran' functions in SQL stored procedures or
'.BeginTrans/.CommitTrans' in ADO coding?

Please help! It seems very confused to put things into practice. So many
problems occur during system design phase.

Many thanks,

Leonard

 

[Adrian Jansen]

Unless you are very experienced at database design, I would not attempt this
as a project. There are commercial inventory handling databases already
available to fill almost any need. Admittedly finding the right one can be
a problem, but less costly than committing a company to an untried design,
and having it go wrong.

I did a design for a manufacturing company recently, who needed what they
thought was a set of special requirements, and got the design working to
their satisfaction ( and got paid ! ) Then they found they could have
bought an add-on to their accounting system which did the manufacturing part
about as well as what I had written. Their upper management was not really
happy.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

 

[postmaster]

Adrian,

Thanks a lot for your advice. Finally, I have someone REALLY understand my
situation. I guess the company that I work for is having the similar plan to
expand the system. Inventory system is only the first thing to deal with.
Accounting functions will be add-on following that. The managers had already
tried using some off-the-shelf products, e.g. ACCPAC, but they still not
satify with the products. They want to have a highly customized system. This
is gonna be a nightmare for me.

I can say there are many obstacles ahead of me during the development of
this project. Users seem very reluctant to any change. Even if I tried to
explain or give any suggestions to them, they just don't want to understand.
The only thing they want is to have is a system that can make them fly.

If anyone having this similar experience, I'm hoping to listen your
thoughts.

Leonard

 

[Adrian Jansen]

I would start by doing some web searches for available products. And yes it
costs you ( and the company ) some time/effort to find a good package. The
customer I had, had already spent $100,000 on an inventory control package,
and it failed miserably, but mostly, I think, because they never fully
understood what they wanted. I only got called in to 'fix up' an old Excel
package I wrote 20 years ago, and decided to convert it all to Access. Then
upper management canned it, after spending a further $30,000 with me, and
having a working system which the operators were happy with.

Beware add-ons !! You need a *very* clear understanding of how to put
together a total package before even attempting such a system, never mind
*adding on* accounting. To my mind thats backwards. Accounting has to be
right, and fully working before you ever think about inventory, or you have
to keep the two systems totally separate. Inventory control is tricky
enough, bad accounting can totally screw your company.

Personally I would do some searches, find a package which you with your
experience know will fit what the company wants, then investigate fully how
to use it *in detail*. Make sure you can get good support from the vendor.
If it still meets your requirements, convince your management to use it, and
be prepared to do all the training/handholding necessary. At least that way
you dont have to do the design as well as the training.

I think you have a big task just getting them to accept any change, judging
by what you say. Certainly they must understand that computers are not
magic, and will not "make them fly" with no commitment on their part. Good
luck.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

 

[postmaster]

Thanks again, Adrian,

You are the expert I'm looking for. However, our company decided to have an
in-house development rather than invest money on buying product. It's just
the matter at the management level. I don't have much power to convince them
into any alternatives, in fact. I was hired to be an one-man IT staff in
charge of everything in a small company. My role can be all of this: DBA,
System administrator, network administrator.

I can think of Data cleansing from their existing Excel spreadsheets is
another issue that will give me headache.

I'm wondering should I keep posting my conversation to this news-thread.
This is gonna be a long story.

Leonard

 

[Adrian Jansen]

Ok, if management dont want to use outside product, I just hope you have
enough experience to keep them on the right track.

Just to get down to details, for a change, you do realise that the very
ideas behind things like 'double entry bookkeeping' are totally outmoded by
computer systems ? The whole point ( at least as I understand it ) was to
catch errors in hand computing totals in ledgers. If computers make this
sort of error, you have *big* problems. So 'double entry' is replaced by a
couple of ID numbers associating the transactions with debits and credits in
appropriate places. Makes the figures look right to an accountant, but by a
completely different method.

Similarly with inventory transactions, you no longer have a 'quantity in
stock' from which you add and subtract numbers, but a historical list of
purchases and sales, the sum of which gives you the current stock at any
time, after a given starting value.

The point is that you must re-think a lot of manual processes to put them
into a framework where data processing is easy and efficient.

Once you have that framework designed, Access, or its bigger brother SQL
Server can easily handle the task. But the design is the killer, and the
fun part

BTW I dont regard myself as an expert in Access by any means, but maybe I
have a bit of experience in data handling.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

 

[postmaster]

Yes, I know the "double entry" is handled very differently in computer.

Also, can I ask if you ever had clients not telling you all crucial
requirements, which highly affect the structure of database, before your
design brought to programming level? How can I explain to them that any of
those additional requirements may possibly require program rewriting? The
reason for asking is because, in my case, every time when the prototypes are
given out for them to comment, new requirements are obtained. Some of the
requirments are only for convenience in using. They just don't understand
why I didn't put them to first priority while program coding. I'm repeating
the coding, prototyping, and design modifying steps until they satisfy with
it. But, time is passing, the senior manager keeps complaining about the
time I spent on the system. 'Cos, he only concerns the date of
accomplishment. He don't want to know about any technical issues. I am the
person should responsible for all technical things, he thinks. I think I
should learn more on the skills to requirement engineering and
commuincations.


Leonard

 

[Nunya]

Several comments...
I agree with Adrian that you probably shouldn't attempt to build this system
from scratch. Even under ideal circumstances, I think you'd be in over your
head. If you're truly needed for network/system administration & support,
there's no way you'd have time for such a project -- it's daunting even
full-time. Most off-the-shelf inventory software is built by teams of
experienced database & application developers, with
experience/specializations in SQL, programming & accounting. Most have
accountants as part of the team for business logic.

The environment you're describing is also disturbing -- you're being
constrained at every turn before you've even begun. Management is
short-sighted and is making technical decisions without any analysis/design
behind them. e.g., single server for everything, they don't want to hear
your suggestions, users resistant to change). The single server is also
rather risky and foolish, as they're making their whole business dependent
on that one server (and more so once they move from their manual/one-off
inventory solutions to a centralized system.
This sounds like a death march project. I've been on a couple. One was
major customization of a commercial accounting package for a local
government to replace a custom system developed and used in-house for years.
The users didn't want any change. That former employer is out of the custom
software business.

You're right to be concerned about the data-conversion/data scrubbing
process. Users used to free-form entry in spreadsheets or text documents
usually hate/resent/don't understand the requirements for strict validation
of entries in databases (Why can't I put "Won't know till Thursday" or "TBD"
in that invoice amount field?). Often large chunks of such data are
virtually impossible to convert.

I was going to disagree with Adrian on the idea of double entry bookkeeping
till I read his remarks more carefully. Now I'll just nitpick
It's true that for internal use, you don't need to duplicate all the info in
a computer accounting system (though technically the pair of corresponding
transactions, even if just a pair of ID's/amounts, is still double entry.
However, depending on internal or external auditing requirements (or even on
reporting/presentation needs and application performance), there may be
requirements for more double-entry-like (and less normalized) tables.

Now some recommdations... if you must attempt this:
Access is a fantastic appplication, but I would tend to recommend SQL Server
over Access for this project for several reasons:
* If you have to do transactions over WAN/internet (and even more so if
through dialup rather than high-speed connections). I know many of the
experienced pros have built robust, reliable Access systems with remote
connections, but it requires expertise.
* Robustness/data integrity: SQL Server allows on-the-fly backups, and can
recover from logs if anything goes wrong (if you leave logging on, back up
logs often enough, and provide enough hard drive space for logs and data!).
Access databases, especially in multiuser apps, can/do corrupt if
connections are broken, and they can't be backed up reliably while users are
connected. Again, pros often make fantastic Access systems supporting
dozens of users, but that requires expertise.
* Security: Access requires that users have full read/write/delete) access
to the directory in which the mdb is located, meaning users can copy, edit
(if not secured with user-level security), or delete the file. Both SQL
Server & Access provide fine-grained access control. SQL Server allows you
to use Windows authentication (single login).
If you do use Access, DO use user-level security... but that is rather
difficult to do well.
* Stored Procedures and transaction control on the server.
If you use SQL Server, you ABSOLUTELY should have that on a separate server
from your network file server, for both security and performance reasons.

And finally, some answers to your specific questions:
Question 1: Do I have to physically partition the database in order to meet
users requirements? This seems to be very common for most company, isn't it?
No, you don't need to, and absolutely shouldn't use separate
tables/databases/forms for different shops. You can use user/role-level
security, stored procedures, and/or views to control what stores/individuals
have access to view and change. Yes, it is a very common requirement.

Question 2: What will be the best way to modify the database when a
transaction occurs?
Not exactly sure what you're asking here.

Question 3: Which one is better while handing the transactional processes,
'begin tran/commit tran' functions in SQL stored procedures or
'.BeginTrans/.CommitTrans' in ADO coding?
I'm a strong believer in stored procedures. They are a natural for
transaction control. They allow you to minimize network traffic by
executing several operations within a single procedure rather than sending
multiple commands/queries. They improve performance by allowing the server
to utilize pre-compiled execution plans. And they allow you to restrict
direct table access by allowing read/write/delete only through your stored
procedures.
If you use Access, you'll use ADO for transaction control.


Best of luck!

 

[Nunya]

I've never had clients that did tell me all the crucial requirements I
just stumbled across this humorous little rant tonight:
http://www15.brinkster.com/vbnotebook/vbProjectsGoWrong.asp

Managers tend to be focused on profitability (as they should be) and their
personal rewards and recognition.., not necessarily in that order So if
you can present things in terms of cost, you might have a chance. Use a
spreadsheet or MS Project to show a project plan for known requirements with
estimated hours AND dollars. Make sure you map out a time line showing a
reasonable allocation of your hours (your probably not going to work 24/7,
and a significant portion of your time must still be devoted to IT admin
duties). Don't skimp on your estimates -- you're trying to sway opinion,
and even conservative estimates often turn out to be low, even without
feature creep, especially on large projects venturing into new frontiers.
Provide estimates of additional hours/day/weeks required for their changes
(and more importantly, put a dollar figure to it -- e.g., your wage), even
if you know they're a stab in the dark. Then make it clear that any changes
require a tradeoff (either he provides more people and money (which in at
least the short term will actually slow down rather than accelerate
development), timelines will slip, or features will have to be reduced.
Then let the manager choose the priorities and tradeoffs.

You might also compare it to change orders when building a shop, office, or
house. The contractor agrees to build to a fixed design for a specific
price. But if ANY changes are made, everybody understands they add to the
time and cost of building, and the buyer will have to pay for those changes.

Having given this sage advice, I admit I've had VERY limited success in
making managers understand or accept the constraints and realities of
development.

For some reason, there's pervasive ignorance, denial, blind/unwarranted
optimism, or even outright fraud on the part of both the business and
technical sides about the true costs in time and resources (usually higher,
sometimes startlingly so, than anybody wants to admit) and benefits
(generally lower than anybody projects).

I found Rapid Development by Steve McConnell to be a good resource, if for
no other reason than to reassure me that I wasn't crazy to think there had
to be a better approach to software projects. The book provides a lot of
anecdotal and statistical evidence about the values of requirements, design,
and programmer working environment; the ill effects of common management
mistakes, particularly responses to projects behind schedule (piling on warm
bodies, forced overtime); and the costs of adding functionality later vs.
designing for it up front. This book also provides excellent discussions
about software project timelines, and the tremendous uncertainty that can
only be reduced through repeated experience with similar projects and with
progress within a project (generally, as you get closer to completion, your
projections should become more accurate/precise.

 

[Adrian Jansen]

Cant say it better than "Nunya". I think he has covered your situation very
well.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

Several comments...
I agree with Adrian that you probably shouldn't attempt to build this system
from scratch. Even under ideal circumstances, I think you'd be in over your
head. If you're truly needed for network/system administration & support,
there's no way you'd have time for such a project -- it's daunting even
full-time. Most off-the-shelf inventory software is built by teams of
experienced database & application developers, with
experience/specializations in SQL, programming & accounting. Most have
accountants as part of the team for business logic.

.... snipped ...