LibreBase - Estimating Database

[skoz55]

Hi,

I am a contractor and looking to create an estimating database and am looking into LibreBase. My goals are:

1. Have a form that is broken into 8 categories (general conditions, excavation, concrete, framing, etc.) Within each category, I populate a description and unit cost which is from a table I can create storing these items.
2. Once I have all my line items, then go back in and enter the actual units
3. I then can print a detail report showing all the descriptions with units, which would be my Scope of Work
4. Then a second report that shows the summary costs by just the main categories

Before I start diving into the learning process, is this something I can do in Libre?

Currently, I do this in Excel; however, I have issues with a tieing in a general database, links breaking, I can’t lock the worksheet but also expand and contract, etc.

Thanks

Scope of Work.png

Summary Budget.png

[keme]

Yes, this is something you can do in LibreOffice.

Base is the database subcomponent, which requires rigidly structured input and can produce reliable output. Calc is the spreadsheet subcomponent(Excel equivalent), for freeform calculation models and data prototyping. Either can be used, or perhaps both in conjunction.

[skoz55]

Can I do it all in Base, so that all of my estimates for all of my jobs are stored in place?

Besides learning the basics of a database, what direction would you suggest I go in learning to do this? Are there any templates out there that might have similar components I can learn from?

Thanks

[keme]

Basic technicalities of a relational database (the only type supported by LibreOffice Base):

• Your data are stored in tables, called “relations”.
• Tables consist of rows (typically identified by row number) and columns (typically identified by a descriptive heading).
• A relation row is a record, identified by a value known as the Primary Key, or PK.
  
  The PK must have a unique value for each record. PK value is assigned when a new record is entered.
• A relation column is a field, identified by a field name.
  
  The field name must be unique within the relation. Field names are assigned when you set up your database. You also need to define the data type for the field.
• Each relation holds info about a particular type of objects or “instances”
• A record should hold data pertaining to one particular object or instance.
• A field should hold info about the same property for all objects/instances.

Relationships may be set up between relations, to automate interaction.

[keme]

Best practices for a relational database:

There is a well defined procedure known as normalizing, which ensures that data integrity is maintained through logical subdivision and reduced data duplication. There are 4 steps that I regularly use. Each step brings your database to a new “normal form”:

• 1NF (first normal form): Every field holds an atomic value.
• 2NF: Every non-key field is fully dependent on the PK
• 3NF: Every non-key field is directly dependent on every candidate key
• BCNF (Boyce-Codd normal form)
  
  BCNF is a “constraining version” of 3NF, and only relevant when there are paritally overlapping candidate keys.

Details of the procedure, background and the notation system normally used in the process (relational algebra) can be found out there on the web. Often when you set up your tables with a mindset observing the logic of my previous comment, the relations will already be in BCNF. Going beyond BCNF is not really useful for your purpose.

[keme]

The above may seem daunting, and it takes some reading and a bit of work before it becomes second nature. The learning threshold is taller for database development than for creating documents using the other office apps. You are crossing the line from user to developer. Good luck, and come back to ask here whenever you are stuck.

You can make a working database model without doing the normalizing, but as your system grows, your data integrity is at risk. Don’t rush!

To your question

Can I do it all in Base, so that all of my estimates for all of my jobs are stored in place?

Yes, based on what I understand of your requirements, I guess that you can.