Database Design for Form Functionality

Discuss the database features
Post Reply
bobcat54
Posts: 2
Joined: Sat Jul 01, 2017 4:58 pm

Database Design for Form Functionality

Post by bobcat54 »

Looking for creative database design, SQL code, subforms, etc as solutions.

I have already researched the basic functionality out-of-box for Base, watched tutorials, read help documentation, applied examples from other forum posts, etc. So, also, an answer of 'Basic understanding of Base is where to start' - I've done that. I am the type of person who doesn't ask for help until I've exhausted all resources. Professionally I work with large complex asset management systems in SQL, but I have never designed anything from scratch. I already understand that Base is different and it has limitations - i'm looking for creative ways to do what we need within the confines of Base.

Environment Details:
This is a summer camp looking to capture inventory, sales, and monetary transactions for campers and staff alike. We're looking to capture the deposits parents make for the campers at the beginning of the week and deposits made by staff members. The amounts the campers and staff spend in the trading post. The trading post inventory including the starting inventory, transactions and ending inventory. We need an invoice where the camp staff can help campers know what their balance is, how much they're about to spend (including selling multiple items with various quantities), and commit the sales data - to include cash with drawl as well.

The output needs to be reconcile sheets for deposits received based on payment types and reconciliation for what the initial deposit was, what was spent and the balance owed to the parent after camp is over.
We also need inventory control outputs with starting inventory, inventory adds, total sold, and ending inventory.

The attached database has the initial configuration - what i'm struggling with is the ability to reconcile the deposits, with the invoice totals, and being able to show the balances on the same form as the invoice. I think I'm missing some filter tables, and maybe some junction tables but when I've added those functions as outlined on these forums & youtube tutorials, it breaks the existing functionality of the form.
Additionally, I am having issues getting the price in the inventory table to write to the sales table to capture the price at the time of the sale (both a line item total and an invoice total).

The db file is too big to attach to this forum.

At this time configuration looks like this - I am not opposed to changes, additions of tables, etc to reach the functionality:

Tables w/fields after:
Campers: autoIDPK, firstname, lastname, address information, email, sessionid(tied to session table), notes,
Deposits: depositid, customerid(tied to camperid but needs to be able to pull staff as well - staff and campers need different attributes so i don't know how to tie them together and still maintain separate attributes), deposittypeid (tied to deposittypePK), paymentamount, payment date, check#, notes, staffid (tied to the staff table but for who took the deposit)
DepositTypes: PK field and the type field
Inventory: productid, categoryid (tied to productcategorytable), product descrip, cost, retail, tax, total retail, units in stock, vendorinfo
InventoryCategories: pk, and categoryname
Sales: invoiceid(PK), customerid (tied to camperid, but again need to find a way to pull in staffid as customers), staffid (whos processing the sales in the trading post), invoicedate, notes, invoicetotal, beginningbalance, endingbalance (i dont think this is the correct spot for the balance fields but the goal is to see the and track the starting balance based on customer when the invoice started and after the invoicetotal is subtracted).
SalesDetails: invoicedetailid(PK), invoiceid (tied to sales), productid(tied to inventory), quantity, unitprice (cannot get the inventory total retail to write into this field), and line total (as qty * unitprice).
Sessions: sessionid (PK) and sessionname
Staff: staffid(PK), firstname, lastname, address information, birthdate, datehired, emailaddress, mobilenumber, phonenumber, title, photo, campname
I currently have a query for invoiceID and InvoiceTotal but cannot get that value back into the Sales table which holds the overall invoice data without breaking my current invoice form.

Forms:
Sales - acting like an invoice. campers buy multiple items in one transaction. Staff needs to be able to open an invoice with customer data, date, their information (i have this part built). Then add inventory items and quantities to the sales details (i have this part working) with the unit cost (not working) and the 'line item total' - which i have calculating in the form but not writing to the tables (it needs to write to the tables). Invoice total - this is writing to a query but no back to the sales table or some other table with all transactions (both deposits and debits).
This 'invoice' has the main form pointed to sales. There are three subforms - pointed to inventory to search the products, to sales details to commit the products (minus the cost fields), and the query table that totals the invoice. I need to find a way to add the current balance for that camper, and a way to deduct the current invoice and log it without breaking the form.
I have a deposit form that tracks the initial deposit for each camper and writes to the deposit table.
OpenOffice 4.1.3 on Windows 7 / Windows 10
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Database Design for Form Functionality

Post by UnklDonald418 »

This appears to be an ambitious project integrating Invoicing, AR and Inventory Control. Not having a copy of your database would make it difficult for anyone here to offer much help especially with forms.
I am assuming you are using an Embedded database.
Go to Tools->SQL and execute the following commands

Code: Select all

CHECKPOINT DEFRAG;
SHUTDOWN COMPACT;
Exit the database and check the file size. It may then be small enough to upload here. Another option is to use MediaFire
https://www.mediafire.com/
Of course, remove any sensitive data before uploading.
Have you looked at [Example] Invoice Forms (without macros)
viewtopic.php?f=100&t=56006
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Database Design for Form Functionality

Post by UnklDonald418 »

Database design can be a complicated process especially when you are trying to integrate several functions. There a a number of books on Database design. There is also a good tutorial on designing a Base database that at least gives you an idea of how it should be done
https://wiki.documentfoundation.org/ima ... torial.pdf
What you get here are opinions on how someone here might proceed. That doesn't mean it's the only way. So ...
Many commercial databases divide the functions into separate modules, which ultimately can be interlinked to create the whole. For instance a professionally designed basic system often has separate modules for Accounts Receivable, Accounts Payable, Invoicing, Inventory Control, Purchasing, and Payroll with the potential for a few more depending on the business model. Each module will have at least one table and usually several. For a simpler business model some of the basic modules can be simplified, combined or eliminated.
It's common for people to jump right in and design their tables before they understand what data they really need to store and how it each piece relates to the whole. The results are often disappointing and troublesome.
It's easier to lay things out on paper so that it's easy to move things around. One system I have seen uses a sticky note for each data item so they can be easily moved from one table to another. It's much easier to see relationships that need to be established than looking at table definitions.
That's my lecture.

Looking at what you posted, let's begin with
the goal is to see the and track the starting balance based on customer when the invoice started and after the invoicetotal is subtracted
I'll refer to that as an Account. Accounts keep track of customers, debits (Invoices) and credits (deposits, payments) to arrive at an Account balance.
Your table Sales comes close but it appears to contain fields that probably belong elsewhere, such as
staffid (whos processing the sales in the trading post)
This should probably be with the Invoice header information. Often there are separate tables for Invoice Header/Summary information, and Invoice transaction details. A single invoice header can refer to one or many transaction details. Keeping all the header information with each transaction would be inefficient, with lots of duplicated information and the potential for conflicting information being stored.
An Account table might also help with another problem you mentioned with your Deposits table
staff and campers need different attributes so i don't know how to tie them together and still maintain separate attributes
Which begs the question, what attributes are you referring to?
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
bobcat54
Posts: 2
Joined: Sat Jul 01, 2017 4:58 pm

Re: Database Design for Form Functionality

Post by bobcat54 »

@UnklDonald418
I totally agree with you - database design can be complicated once you get into the weeds. Thank you for the additional resources - both on database design and those posts available here.

On the lecture - I am intimately familiar with the modular interlinked design and also am very cognitive of the fact that I work with these designs, I did not nor have I ever attempted to create one on my own - hence why I'm reaching out for help. I did a basic on paper outline initially and am finding as I build forms and attempt to pass data, that model isn't working - the sticky note layout might be the way to go, on a white board to draw connections.
Account table might be the way to go with a junction between the sales table (it should probably be called the invoice table but it's sales, but we'll get to that) and the account table, the deposits and the accounts, and with drawls and accounts?
The other idea I'm exploring is a transaction table that houses a transaction number in attempt to tie those same items together.

Ok so, in your reply you also talked about the sales table holding the StaffID - the sales table is the header table with the salesdetails table being the container for the many products that apply to the single sale/invoice - I should make the terminologies match to clean it up. So completely agree with that and have it set up that way.

On the staff and campers tables - we want to track who takes the deposits, whos processing the sales from the staff end. So the person on the receiving end. With that being said both campers and staff need to be able to deposit and make purchases. The attributes I was originally referring was those surrounding camp staff vs campers - specifically staff has a 'campname' that campers do not. I rebuilt these two tables as people and applied a category and a staff details table that is tied to through the peopleID - however i'm running into a key issue when i have both campers tied through the people id and staff through the peopleid - from what I've found i think i need to create a junction table to create a foreign key?? Is this the only way to do foreign keys in Base??

I ran the SQL scripts and was only able to get the DB down to 145kb - http://www.mediafire.com/file/y4ryci55o ... gPost1.odb so media file link it is - first time using mediafire so let me know if the link is bad. It is also a clean DB, with the definitions and relationships, and forms copied over to remove all personal data from it - so if something is a little weird also, let me know - i could've missed pulling something over.
OpenOffice 4.1.3 on Windows 7 / Windows 10
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Database Design for Form Functionality

Post by UnklDonald418 »

No problem downloading your file from MediaFire. I will look at it as time allows. Hopefully others will do the same.
i think i need to create a junction table to create a foreign key?? Is this the only way to do foreign keys in Base??
Junction tables are used to establish a many to many relationship between two tables.
In Base a foreign key relationship can be defined either via SQL or use Tools->Relationships to draw connections between the tables. Any connection drawn there will be passed on the HSQLDB database engine as a foreign key relationship.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Database Design for Form Functionality

Post by UnklDonald418 »

As I mentioned earlier this is an ambitious project so it may take you some time to reach some of your goals. I will offer my advice based on my experience in working with databases. It won't be the only way to do things and there may be better ways.
I had some time to look at what you have done so far so …

Generally, it is considered to be bad form to save the results of a calculation in a table field because there is the potential to have conflicting values. While there are situations where that rule can be broken it should be adhered to as much as possible.
"tblSales" has fields for Beginning Balance, Invoice Total and Ending Balance. If you store the Invoice Total then the others can be calculated by subtracting the sum of the Invoices from the sum of the Deposits for each camper.
Storing the Invoice Total in the "Sales" table is a problem that will likely require a macro. Since the Macro Recorder doesn't work with Base it will have to be a coded macro. Base macros can be coded in Star Basic, Python, Bean Shell or Java. There may be advantages to a more modern language but most people still use Star Basic. The learning curve for macro coding is very steep in part because the Open Office API is very complex and rather opaque. The problem with macros, besides the difficulty in using the Open Office API, is that often they aren't very portable and can be easy to break and hard to fix. So it is best to avoid them whenever possible.
Andrew Pitonyak has made his book ”OpenOffice.org Macros Explained” and some other macro coding information freely available at:
http://www.pitonyak.org/oo.php
There are also two object inspection tools that can help see through some of the fog surrounding the API, the Xray tool and the MRI tool extension. I prefer the MRI tool and actually left some of the code for invoking the MRI tool in the macro code below. It has been commented out so that it doesn't interfere with the normal operation of the macro.
http://extensions.services.openoffice.o ... ction-tool

I've been looking at your Trading Post Sales form and made a few changes.
I renamed the control where you display the the calculated Invoice Total to ”fmfInvTotal”.
I added another Formatted Field control to the MainForm and using the control Properties dialog I named it ”fmfInvoiceTotal” and on the Data tab I selected the Data Field to connect to ”Invoice Total”. Then on the General tab I set the Visible property to No. You could leave it visible but that would probably just lead to confusion because it doesn't immediately display the current total.
Next in the Standard folder of the macro library for TradingPost1.obd I added a module containing the following macro code

Code: Select all

REM  *****  BASIC  *****

Sub UpdateInvTotal(oEvent as object)
Dim oObj1, oObj2
Dim oForm,  oFormsCol, oFormDoc, ContMod, ContView
Dim sITpt as string

'If Not Globalscope.BasicLibraries.isLibraryLoaded("MRILib") Then
'      Globalscope.BasicLibraries.LoadLibrary( "MRILib" )
'End If
'oMRI = CreateUnoService( "mytools.Mri" )

'oMRI.inspect oEvent
  oObj1 = oEvent.Source.Model.Parent
  oForm = oObj1.Parent
'oMRI.inspect oForm
  oFormsCol = oForm.Parent
  oFormDoc = oFormsCol.Parent
  oObj2 = oObj1.getByName("fmfInvTotal")
  sITot = right(oObj2.Text, Len(oObj2.Text) - 1)   'removes the '$' from the displayed formatted value
  ContMod = oForm.getByName("fmfInvoiceTotal")
  ContView = oFormDoc.CurrentController.getControl(ContMod)
'oMRI.inspect ContView
  ContView.Model.BoundField.updateString(sITot) 'update the control
  If ContView.Model.commit then   'save to table
  else
    print "Error saving Invoice Total"
  endif
End Sub
Finally on the “Get Total” button control I connected the above macro to the “When Losing Focus” Event.
If everything is correct when you press the Get Total button the calculated total is read from the "fmfInvTotal" control and stored in the "fmfInvoiceTotal" control. When the invoice is saved the “Invoice Total” field in the Sales table should be updated.

I've been thinking about the issue with campers vs. staff and wonder if the People table idea might work. It could contain fields for the information and attributes for both and use a Boolean field to flag staff members. You mentioned the staff members might be making deposits, so I am assuming they will also patronize the store. If you store them in separate tables you may need a separate Invoice form for each.

I haven't really looked much at inventory control. There would likely need to be an invoice finalization process, beyond which the invoice can no longer be edited. Updating inventory would occur as part of the finalization process. It too would likely need a macro. Looking at the form Trading Post Sales I see you the Add Data Only property set to Yes for the MainForm so perhaps inventory update could be could be run as part of the save process.

A couple of small details.
In tblInventory you are storing both the CategoryID and the CatagoryName. One or the other would suffice. If the store inventory is very large storing only the CategoryID could result in a significantly smaller data file.
Also, you appear to be storing photos of staff members in the database. If there are very many photos or very high resolution you can quickly run out of memory. There is a tutorial showing how to store them externally
viewtopic.php?f=83&t=44124
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Post Reply