Diese Seite mit anderen teilen ...

Informationen zum Thema:
WinDev Forum
Beiträge im Thema:
Erster Beitrag:
vor 4 Jahren, 5 Monaten
Letzter Beitrag:
vor 4 Jahren, 5 Monaten
Beteiligte Autoren:
Yogi Yang, Ned!, David Egan, Allard, Erik Schwarz

Once More I need advise on How to...?

Startbeitrag von Yogi Yang am 15.08.2013 04:35

Ok let me highlight the actual scenario:
I have developed an personal accounting solution which is actually a conversion from Delphi.

The Delphi version of my software creates new files when a user performs Year Ending process. This was (or rather is) possible because I have used Borland Database Engine.

What happens in this process is that the software first creates a new directory for the new Financial Year and then creates all the required data files in them except the Ledger File.

In case of Ledger file it first calculates all the balances (Dr/Cr) from vouchers after that it copies the Ledger file to the new directory and then updates its Opening Balances for each ledger.

Here in this above approach there is one problem. If the user opens previous year accounting period and adds, deletes or edits any entry then the Ledger's Opening Balance of current year will become invalid.

While converting this software to WD I have opted for using HFCS as the new version has to be Multi User and I also wanted to solve the problem of invalid opening balances in current financial year.

From what I understand, it want be advisable nor practical to create new database for each year. Instead of this I have decided to keep and work with one single database. Is this decision right? Can someone guide (based on your experience) me as to what is the best way. Should I create multiple Databases for each financial year or should I keep one single database and maintain a file in which I should keep records of each financial year allow user to select financial year in which he/she wants to work?

Last but not the least - How can we give facility to user so that they can maintain accounting for multiple companies?

All help and advise is highly appreciated.


Yogi Yang


HI Yogi,

just my opinion.

As Long as database size is not critical, Keep just one file. To restrict entry to the current year, use filter or checking of wrong(old) Dates.

If database tables grows too much, you may do some like reorganization (for example delete all entries up to a certain date in the past) then the user will not be able to do entries before that day (Needs to be checked by your Software). If you Need to be able to edit all times in the past, then just Keep all the data.

There may be one reason to split to different files. The files are growing too much and cannot be handled by the database engine (uncertainly) or disk space is out. But then you Need more disks or tapes or whatever. In this case you Need to write a lot of Software to synchronize if something Needs to be edited in a former file.

Remember: good database design avoids redundant data!


von Erik Schwarz - am 15.08.2013 07:43
Hi Yogi.

I would use one database... In the ledger file, use a field to identify the company and another for the financial year. These should both be a foregin key to another file in the same database.

You could also put a 'locked' boolean field against a financial year, so the user cannot add or delete any records associated with the locked financial year.

If the financial years for each company are different then you need to think of another way to link them to each other.

Remember: good database design avoids redundant data!

^^ :spos:


von Ned! - am 15.08.2013 08:26
I would use one db for the years. For different companies,. That have nothing todo with each other you could make a different db
So if you make an administration. You create a db for that company. . So you can use the software for more companies ( administrations ) but it uses one db for each company.

If your software is used by 1 company with several administrations ( sub companies) and the company sells off one sub company. Then the administration can easily be soldoff to. The new client than has just his administraion.

In case you put it all in one db. Then you have to extract the part of the company that is soldoff. That can be a lot of work.

Just my opinion



von Allard - am 15.08.2013 11:18
Hello All,

After reading post of you all I have come to the conclusion that I should maintain one single DB for all years and in all my queries I should set the where clause to show only transactions of a particular year (as selected by user when the software starts).

As for multi companies I think it would be advise able to maintain separate database for each company. What others have to say?

If I manage companies from one single database then there is an advantage that the owner of multiple companies can easily get a consolidated BS and P&L for all companies as well as have common Ledgers between all companies.

Any ideas as to how to go about doing this practically?

von Yogi Yang - am 15.08.2013 13:46
Hi Yogi,

Any good book on relational database design is probably a good place to start, or a good weblink that covers the ideas... http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

This site is pretty old looking, but the principles are the same... it covers 1 to 1, 1 to many joins etc. how and when to use a foreign key in your database table (a File in WD.) etc.

Its not WD specific but SQL and DB design is pretty much the same across all implementations.


von Ned! - am 15.08.2013 14:20
Hi Yogi
We allow the customer to decide.

We originally designed our software to keep all companies in the one DB with an identifier field in every table. On our login screen we have a combo box so they can select the company & then all reading,writing etc uses that identifier. We also have a command line switch to bypass the company selection for users that may only access one company.

Subsequently we have had customers who want to use separate databases (typically one for their trading entities & separate ones for their private companies, family trusts etc) so we now allow that by writing to a local system ini file on first access/creation of that database. We then have a separate encrypted ini file for each database which holds all the connection parameters. If only one database is in the system ini file then that one is automatically loaded otherwise, again by command line parameter the user is either forced into a particular database or allowed to select from the list in the ini file before logging in.

This gives us the best of both worlds & allows customers to restrict individual PCs to specific databases and/or companies within a database very easily. Our system is based on machine rather than user id but it would be a fairly simple matter to link it to user id if required.


von David Egan - am 15.08.2013 23:40
Any good book on relational database design is probably a good place to start, or a good weblink that covers the ideas... http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Thanks for the link. But I believe I am quite well versed with SQL and my fundas of RDBMS are also sound. But my questions are regarding HFCS and its typicality.


Yogi Yang

von Yogi Yang - am 16.08.2013 15:22
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.