Trying to build an invoicing database for a travel agency

[kclymer]

Hey guys, I have to say upfront that my knowledge of Base is very limited. I'm trying to build a database which can be used to invoice customers for a travel agency. Most importantly is flights. I have the following tables set up:
Customer
Passengers (which are not always the customer)
Airports
Invoices

Obviously the passengers need to be associated with the customers...but I have no Idea how to associate it with the customers.

The airport table I got from an open source list of IATA airports. The idea is when I invoice a flight, I can search via IATA code or airport and list it for reference on the invoice

So here's how I envision it on the invoice form:
1) search customer database and all relevant datapoints appear (Last name, first name, address etc)
2)The selection of possible passengers can be selected, but only from the passengers associated with the customer
3)Date of departure and time free text entered
4) Search departure and arrival airports and display
5) Enter net price
6) Commission is automatically calculated
7) Total price appears at the bottom
8) Invoice can then be exported to PDF, I assume via a report.

Am I making this more complex than it needs to be? I fortunately have a lot of time to complete this project, but I need to do it. Any feedback would be great...If you'd like me to upload my project I will. I would be grateful for any help anyone could provide.

[UnklDonald418]

That is an ambitious project.
Look here for an invoicing example
[Example] Invoice Forms (without macros)
And here for a database design tutorial
https://wiki.documentfoundation.org/images/0/02/Base_tutorial.pdf

[John_Ha]

Hey guys, I have to say upfront that my knowledge of Base is very limited. I'm trying to build a database which can be used to invoice customers for a travel agency. Most importantly is flights.

That is a very big mistake with huge risk.

You admit you don't know much about the subject yet if what you build behaves unexpectedly the company could break the law or go bankrupt. Do you understand the necessary tax, accountancy and other regulations? Who will maintain the database? Who will keep things up to date as taxes, regulations etc change?

Would you agree to a surgeon operating on you who said "I have to say upfront that my knowledge of brain surgery is very limited but I am reading posts on the internet about it so don't worry - everything will be OK."?

Buy a professionally written solution - it will be far cheaper than employing a professional to write a bespoke solution for you. It will cost a few pounds per month and it will work properly. Searching for travel agency invoicing software gets 1,800,000 hits.

See Database for small company and Small Business Database. Using AOO Base for Small Business Sales says:

No, it is completely impossible to use Base for small business sales. Databases are designed to do this but Base is not a database.

So, set up your database according to your detailed and well thought requirements and then you may use Base as a link between your database and this office suite (and you may connect an Access document to it as well).

Calling File > New > Database ..., and creating a new one simply because it is so easy and free of costs leads to no viable solution.

Also see [Solved] Sum values on main form and in subform grid column which is about an invoicing database and includes:

The file appears to reflect faulty database design in that few proper relationships are established between the source data tables in the database.

An invoice database can be quite complicated. Typically, at the very least, it should reflect the one to many relationship between Suppliers and Invoices, similarly between an Invoice and the Lines it contains, and similarly between a purchase Item and invoice Lines. These are each quite distinct entities and will require individual, but referenced, tables if the database is to ensure integrity and avoid unnecessary redundancy.

An initial introduction to database design relevant to Base can be downloaded from https://wiki.documentfoundation.org/ima ... torial.pdf. It is significant that many pages in that tutorial are devoted to design principles before addressing forms or queries.

Then again Base may not be a wholly suitable route if the intention is that the database is to be used for serious commercial purposes. The default database application embedded in Base is suitable for little more than learning and demonstration purposes: commercial reliability will inevitably require upgrading so that Base works with a split or separate database engine. It should also be recognised that starting an initial RDMS project by designing a commercial database is unlikely to be economically justifiable: an off-the-shelf purchase or hiring a professional may well be a cost that proves financially more rewarding. On the other hand it can indeed prove to be a challenging and enjoyable education exercise.

and

I have seen several enterprises fail when an unreliable Purchase Ledger caused loss of cash control.

Any relational database that ignores the key principles of normalization sooner or later produces unreliable results. I fear all the care and effort already devoted to "Open PLA 5.3.2.2.g.odb" will be abortive if it is not grounded on a sound relational database design. Starting at the deep end is risky and better avoided. I suggest it will be difficult, if not impossible, to achieve your aim, as quoted above, without first gaining an initial understanding of the theoretical principles that underlie a relational database. An understanding of these principles is more readily proved and tested on the design of token or simple databases before embarking on the critical complexity of one that is intended for commercial accounting.

and, in a comment about the suggested database

It [the user designed database] demonstrates how it shouldn't be done.

[kclymer]

Hey guys, I have to say upfront that my knowledge of Base is very limited. I'm trying to build a database which can be used to invoice customers for a travel agency. Most importantly is flights.

That is a very big mistake with huge risk.

You admit you don't know much about the subject yet if what you build behaves unexpectedly the company could break the law or go bankrupt. Do you understand the necessary tax, accountancy and other regulations? Who will maintain the database? Who will keep things up to date as taxes, regulations etc change?

Would you agree to a surgeon operating on you who said "I have to say upfront that my knowledge of brain surgery is very limited but I am reading posts on the internet about it so don't worry - everything will be OK."?

Buy a professionally written solution - it will be far cheaper than employing a professional to write a bespoke solution for you. It will cost a few pounds per month and it will work properly. Searching for travel agency invoicing software gets 1,800,000 hits.

See Database for small company and Small Business Database. Using AOO Base for Small Business Sales says:

No, it is completely impossible to use Base for small business sales. Databases are designed to do this but Base is not a database.

So, set up your database according to your detailed and well thought requirements and then you may use Base as a link between your database and this office suite (and you may connect an Access document to it as well).

Calling File > New > Database ..., and creating a new one simply because it is so easy and free of costs leads to no viable solution.

Also see [Solved] Sum values on main form and in subform grid column which is about an invoicing database and includes:

The file appears to reflect faulty database design in that few proper relationships are established between the source data tables in the database.

An invoice database can be quite complicated. Typically, at the very least, it should reflect the one to many relationship between Suppliers and Invoices, similarly between an Invoice and the Lines it contains, and similarly between a purchase Item and invoice Lines. These are each quite distinct entities and will require individual, but referenced, tables if the database is to ensure integrity and avoid unnecessary redundancy.

An initial introduction to database design relevant to Base can be downloaded from https://wiki.documentfoundation.org/ima ... torial.pdf. It is significant that many pages in that tutorial are devoted to design principles before addressing forms or queries.

Then again Base may not be a wholly suitable route if the intention is that the database is to be used for serious commercial purposes. The default database application embedded in Base is suitable for little more than learning and demonstration purposes: commercial reliability will inevitably require upgrading so that Base works with a split or separate database engine. It should also be recognised that starting an initial RDMS project by designing a commercial database is unlikely to be economically justifiable: an off-the-shelf purchase or hiring a professional may well be a cost that proves financially more rewarding. On the other hand it can indeed prove to be a challenging and enjoyable education exercise.

and

I have seen several enterprises fail when an unreliable Purchase Ledger caused loss of cash control.

Any relational database that ignores the key principles of normalization sooner or later produces unreliable results. I fear all the care and effort already devoted to "Open PLA 5.3.2.2.g.odb" will be abortive if it is not grounded on a sound relational database design. Starting at the deep end is risky and better avoided. I suggest it will be difficult, if not impossible, to achieve your aim, as quoted above, without first gaining an initial understanding of the theoretical principles that underlie a relational database. An understanding of these principles is more readily proved and tested on the design of token or simple databases before embarking on the critical complexity of one that is intended for commercial accounting.

and, in a comment about the suggested database

It [the user designed database] demonstrates how it shouldn't be done.

Hi John. I can appreciate your concern, but I don't see the huge risk as the customer will at no time be handling the database. As for the tax regulations, I am an accountant by trade and my partner is the travel agent. In Germany the only tax regulations to keep in mind are with regards to VAT. Domestic flights are subject to VAT. InterEU and Flights in third countries are not subject to VAT and the commission follows suit. This is intended to be used in a small business with a storefront. If it were an OTA, of course Base would be totally unsuitable. I do have to ask, If base isn't a database, then what is it? I can be flexible on the search and find aspects of it, because the search and find feature for the airport is merely to find and display. Since flights change by the hour, we would be manually entering the net price from our supplier, and ideally Base would calculate the commission and taxes, based on whether the flight was domestic or not. Obviously the idea of using base is so we can save all of our sales for reports and generate invoices. Similarly I would like to have search and display for the Customer and of course the passengers/guests. Once those parts are complete, the rest is data entry.

[John_Ha]

I do have to ask, If base isn't a database, then what is it?

Base is a small training and demonstration database application - it is not a robust database suitable for a commercial application.

[UnklDonald418]

Base is a front end for a Hyper SQL database engine (HSQLDB). It is included with OpenOffice in an Embedded database version (look in the lower left of the main Database screen). It makes use of a 10 year old version of HSQLDB (version 1.8) and as John Ha commented

Base is a small training and demonstration database application - it is not a robust database suitable for a commercial application

A Base .obd file is actually a zip archive containing all the tables, queries, forms and reports. It is not uncommon for something to go wrong with the zip process resulting in a corrupt and unusable database file. So frequent back-ups are essential.
There is also a Split database model that stores the data outside of the .obd file so the data loss problem is minimized and it makes use of a more recent version of the HSQL database engine.
[Wizard] Create a new 'split' HSQL 2.x database
In either case regular back-ups are essential.
Base provides sufficient power for many small business applications where it is used in a single user environment. While it is possible to use Base in a multi-user (client server) environment it probably isn't a good choice for that. Actually, Base can also be used as a front end tor other database engines.

[Villeroy]

Your project is far too overambitious for a beginner.

[kclymer]

Your project is far too overambitious for a beginner.

Thanks for the non-help. Now do you have anything useful to say? I thought not.

[kclymer]

Base is a front end for a Hyper SQL database engine (HSQLDB). It is included with OpenOffice in an Embedded database version (look in the lower left of the main Database screen). It makes use of a 10 year old version of HSQLDB (version 1.8) and as John Ha commented

Base is a small training and demonstration database application - it is not a robust database suitable for a commercial application

A Base .obd file is actually a zip archive containing all the tables, queries, forms and reports. It is not uncommon for something to go wrong with the zip process resulting in a corrupt and unusable database file. So frequent back-ups are essential.
There is also a Split database model that stores the data outside of the .obd file so the data loss problem is minimized and it makes use of a more recent version of the HSQL database engine.
[Wizard] Create a new 'split' HSQL 2.x database
In either case regular back-ups are essential.
Base provides sufficient power for many small business applications where it is used in a single user environment. While it is possible to use Base in a multi-user (client server) environment it probably isn't a good choice for that. Actually, Base can also be used as a front end tor other database engines.

I don't see how this is a mere "training database". Isn't access also an embedded database? People use Access all the time commercially. Assuming a split database is set up, can it be used as I plan?

[UnklDonald418]

Any software including MS Access can experience a catastrophic crash, that's why backups are so important. MS Access has a development team of paid programmers to provide bug fixes and add new features. I worked with Access for over 10 years without writing a single line of SQL code, but I found I couldn't get very far using Base without some SQL coding skills. On the other hand, I had one project that I unsuccessfully attempted several time using Access and VBA, but completed in an afternoon using Base and some newly acquired SQL skills.
What little attention OpenOffice Base has seen since being sold to Oracle and subsequently transferred to the Apache Software Foundation in 2008 has been performed by volunteers. LibreOffice, a variant of OpenOffice, has seen slightly more attention.
While you certainly could continue using the Embedded database (you wouldn't be alone) you do need to be aware of the data loss problem and diligently perform frequent backups. Another issue mentioned earlier, the Embedded database is locked in with HSQLDB version 1.8 which is also vintage 2008.

Assuming a split database is set up, can it be used as I plan?

Yes, the big difference is the Split (JDBC) database model requires a dedicated directory rather than a single file. One advantage is that the data is stored in external files that have not been zipped (compressed) so the data loss problem is minimized (but you still need to perform regular backups). Another advantage is when new versions of HSQLDB are released you can easily update a Split database by downloading and replacing the hsqldb.jar file in the driver sub-directory of the database directory.
You can easily copy tables, queries, forms and reports between Embedded and Split databases using Cut and Paste or Drag and Drop.

[kclymer]

Ok thank you so much for the help! I thought you were going to tell me that Base is useless. I have both Apache Open Office and Libre Office installed. I find Apache lacks some of the features as Libre but Libre is a bit more unstable. One option I've considered is getting a student from the computer science department to help for maybe some money. I live in Munich and one of the best computer science programs is a TU Munich. I think if I do this i have the advantage of having someone with more know-how than me set it up as you prescribed with a split database. I think someone who knows how to do this could accomplish it in 10 hours. I think 15€ per hour is pretty good for a student. Then i have the advantage of learning because then I can reverse engineer the work.

[John_Ha]

Your project is far too overambitious for a beginner.

Thanks for the non-help. Now do you have anything useful to say? I thought not.

Trust us - Villeroy is giving you excellent advice. You will be extremely foolish to ignore him.