Page 1 of 1

General Question about Database

PostPosted: Mon Jan 08, 2018 3:12 am
by VolunteerFireFighter
I do a lot of things, but I've never played with database until now. I am a volunteer firefighter and I've been given the responsibility of taking over our outdated (and atrotious) filing and reporting system (paper). I started looking in my open office for a way to do this as well as a few google searches. I've created my table, and I'm attempting to create my forms. Here's what I need. I'm looking for a way to have drop down lists in my forms, that change what other fields are available. We have 6 major types of calls and with each type of call is a requirement for different information.

For example, I have it set up to automatically fill in the run number.
After that, I need to know the type of call, so that i know what information I will need to enter Next. So i would like a drop down list that has the options;
Structure Fire
Wildland Fire
Mutual Aid
Other (Weather, Line Down, etc.)

Again, depending on which call i have, dictates the type of information i will need entered into the rest of the form. A structure Fire will not require a drivers license number to be entered, and it will require the time that the fire was reported under control. Just as a medical call will require the UNIT that is transporting the patient, destination hospital, patients complaints and injuries, as well as the times that the ambulance arrived, departed, CPR was started and if we used a Helicopter to transport at anytime.

So is there a way to force each answer from a drop down menu to change the available fields after itself? I know i could just leave everything in, but that would result in a TON of extra fields not being filled in. I'm trying to make this as idiot proof as possible. We are a volunteer department, and I never know who will be filing out reports as we never know who will be available for a call due to obligations with work and families.

If anything, Would it just be easier to create a different form for each of the main call types and link them all to the same database?

Re: General Question about Database

PostPosted: Mon Jan 08, 2018 4:17 pm
by Villeroy

Re: General Question about Database

PostPosted: Tue Jan 09, 2018 12:40 am
by UnklDonald418
Good database design can be a complex process (there a number of books on the subject) and there are things that need to be considered before designing tables. In addition to the resources Villeroy provided I would suggest you look at this tutorial to get an idea of what is involved

Based on the information that you provided I would suggest making a list of all the different bits of information you want to record in your database. Then create a new list and include any items from the first list that are common to all call types. Then make 6 more lists each containing items that are unique to each of the call types.

The list containing the common items would be your main table probably with the “run number” as the Primary Key (it is best to use an automatically generated integer value for this). The other 6 lists would each be separate tables and in addition to the unique items they each should also contain a field referencing the “run number” from the main table (in database parlance a Foreign Key) so that relationships can be established between the various tables and an additional automatically generated integer Primary Key.

The word “Form” can have multiple meanings in Base. In one sense a Form is a collection of controls that reference a single data source (a single table or a query for multiple tables). When you select a Form from the Forms area of Base what you see displayed on the screen is a form document which can contain multiple Forms (collections of controls).

You would need one Form for the main table and SubForm's for each of the other tables. These Form/SubForm combinations could appear either on a single form document or separate documents, whichever makes it easiest to use. If you use separate form documents for each then perhaps a switchboard form might help.

Also, be aware that Base as supplied with the Open Office suite is in an Embedded format. That is a good learning platform but it suffers from a number of flaws making it unsuitable for serious database uses like yours. However, those issues can be overcome by using a split database model.
[Wizard] Create a new 'split' HSQL 2.x database
Of course there are trade offs such as the table design GUI doesn't work very well with split databases, but if you use the table design method described in the Tutorial I referenced earlier in this post that won't be an issue.

Re: General Question about Database

PostPosted: Tue Jan 22, 2019 12:31 pm
by david1809

You have done alot of things with database thats great, I also want to play with database but i dont have much knowledge about database ,even i have problems with many basic things such as , what is SQL and whats the statements are used in SQL. If anyone knows please help me with this.

Re: General Question about Database

PostPosted: Fri Feb 08, 2019 12:41 am
by UnklDonald418
The Base handbook covers the Create Query in Design View tool that will generate basic SQL queries
There are also a number of online tutorials, here a two I have used
Check your local library, most have books on learning SQL