Is OO Base good for this?

Discuss the database features

Is OO Base good for this?

Postby Plomo » Wed Jun 27, 2018 1:03 pm

Hi everyone,
i have very little knowledge of this software and I'm learning it on the go but i keep staring at tables and relations just wondering if it's really suitable for my needs. So before i go further i would like to have your suggestion about it. Please, excuse my bad english.
I work in a silkscreen company that print on plastic packaging. For every production i print a PDF form that i give to operators to fill manually. In that form there are all the information and the steps of production (e.g. type of packaging, color of packaging, PANTONE colors used, their recipes, silk used, etc etc). When the production is done i get back the form and copy the information back in the PDF on my pc and i print it again to store it up in a closet. In my pc so far all the PDF are apart from each other, spread in folders organized by clients>code of production (every production has its own code).
My needs:
- a database where to find the form of any production i want (scenario: "I need to know what )
- a database with all the recipes ever done which i can find searching by PANTONE name. (scenario: "i have to do this PANTONE on this kind of packaging, have we already done it or i have to make the recipe from scratch? ")
- preserving the currently PDF form layout (as much as possible) the operators are use to fill.

I attach the database i'm working on.
DATABASE SILKSCREEN PRINT.odb
database
(20.28 KiB) Downloaded 14 times


In wanted to add the PDF form as well but it looks like a can attach only one file... or i just don't know how to do it :(

Some information are already know and i could take it from already filled tables. Those information are: packaging type - packaging material - packaging color, all operators signs, silks, exposure, gelatin and machine.
I think the most tricky part is that every PANTONE color used has its own silk, exposure, gelatin, brand, inks and additive (but some of these info could apply even for other PANTONE colors). Also, in the form there's place for maximum 6 PANTONE colors but they could be just 1 or 2 or 3.... Just like the recipes: there's room for maximum 6 inks + 4 additive but i don't know it until i get back the PDF form from the operators.
Also, different production (so different code) could have the same PANTONE colors.. but still it doesn't mean they share the same recipes cause it depends on the color of the packaging, the silk used and so on...

So, i'm kinda lost here, any help would be appreciated. If you need to know more in order to give me solutions.. just ask.
I hope you all the best.
Cheers.
OpenOffice 3.3.0 - Mac OS X 10.6.8
Plomo
 
Posts: 6
Joined: Wed Jun 27, 2018 10:58 am

Re: Is OO Base good for this?

Postby Plomo » Wed Jun 27, 2018 1:56 pm

It looks like i can't upload PDF files so i made a png copy.

PDF FORM.png
form
OpenOffice 3.3.0 - Mac OS X 10.6.8
Plomo
 
Posts: 6
Joined: Wed Jun 27, 2018 10:58 am

Re: Is OO Base good for this?

Postby UnklDonald418 » Wed Jun 27, 2018 9:14 pm

Yes, a Base form could be designed to work similarly to your PDF form, but first there are a number of problems with your tables that need to be fixed before you could hope to design a working Base form.

It appears all of your tables have a Text field for the Primary Key. It is almost always more efficient to let the database engine automatically generate an Integer for the Primary Key value rather than relying on the user to pick a unique text value. For instance, the table FORM (which is a name that can cause considerable confusion, so from here on I will refer to it as TBL_FORM, and I recommend that you also rename it) has the field "CODICE" of type Text[VARCHAR_IGNORECASE] designated as the Primary Key.
If you go to Tools>SQL and execute the following commands
Code: Select all   Expand viewCollapse view
ALTER TABLE "TBL_FORM" DROP PRIMARY KEY;
ALTER TABLE "TBL_FORM" ADD COLUMN "ID" INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY;

Then select View>Refresh tables so that the Base front end is aware of the changes made by the HSQL database engine. You will then have a proper primary key for TBL_FORM.

All your other tables probably should be similarly modified. You may need to first delete all the relationships you have assigned. They will need to be changed anyway.

There a quite a few normalization issues with your tables. Normalized database tables store repeating table fields such as COLOR_1 thru COLOR_6 in a separate table, particularly if there can be a varying number of COLORS associated with a single TBL_FORM record. You could delete all those fields from TBL_FORM and instead create a table, for example, FORM_COLOR that would have fields (also referred to as Columns)
ID Integer, auto generated Primary Key
COLOR Text
TBL_FORM_ID a foreign key referencing the ID field in TBL_FORM.

But even that could be improved by having a separate table for COLORS
ID Integer, auto generated Primary Key
COLOR Text

then FORM_COLOR would have fields
ID Integer, auto generated Primary Key
COLOR_ID a foreign key referencing the ID field in COLORS
TBL_FORM_ID a foreign key referencing the ID field in TBL_FORM.

Likewise, SEQ COLOR1 thru SEQ COLOR6 should be stored in another table. Perhaps that table too, could reference the COLORS table, so for instance, the text string “Black” would only need to occur once in the COLORS table. Any other places it is used would just reference that record in the COLORS table. Other possible tables related to TBL_FORM would be for INK_xx, GR_xx, ADDITIVE_Ax and perhaps even TREATx.
Relational database tables need to make sense to the database engine, but they are often not in a human readable format. In fact, that is often a good test, if someone unfamiliar with relational database tables can make sense of a table, then it is a good possibility that the table is poorly designed.

PANTONE LIST also has numerous issues that I haven't covered here.

For a tutorial on normalization and database design see
https://wiki.documentfoundation.org/images/0/02/Base_tutorial.pdf
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.5 & LibreOffice 6.0.4.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 890
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Is OO Base good for this?

Postby UnklDonald418 » Fri Jun 29, 2018 1:13 am

I had some free time this afternoon so I uploaded some modifications to your database.
In the Forms area try the form document EntryForm. It is not complete but it should allow you to see how Base forms might work for you.
In the Queries area are a number of queries whose names end with LB. Those are the queries that supply data to the numerous ListBoxes on the form document.
In the Tables area I reused some of your simple tables that only needed a proper Primary Key.
Tables that that required more extensive modification and ones I added begin with TBL_
Attachments
DATABASE SILKSCREEN PRINT-modified.odb
(24.83 KiB) Downloaded 16 times
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.5 & LibreOffice 6.0.4.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 890
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Is OO Base good for this?

Postby Plomo » Fri Jun 29, 2018 4:45 pm

Why did you do that? I mean, why are you so kind. I was astonished while reading your reply: you owned me nothing and still gave me some of the most valuable things a man could have: time and knowledge. I know in this pages people share their knowledge and stuff but still i think you kinda overstepped it. Your first reply was already enough precious to me and i din't know how to thank you for that. Now after your second reply, even before open your attachment and see if i understand it or not, i have to write something. Thank you, thank you so much, beautiful stranger.
OpenOffice 3.3.0 - Mac OS X 10.6.8
Plomo
 
Posts: 6
Joined: Wed Jun 27, 2018 10:58 am

Re: Is OO Base good for this?

Postby UnklDonald418 » Fri Jun 29, 2018 6:54 pm

A key to advancing civilization is knowledge sharing.
Relational database design is not intuitive. There is a definite learning curve and while tutorials provide valuable lessons, it is sometimes difficult to discern their relevance especially when you are focused on solving a particular problem. When you add form design to the equation the learning curve gets even steeper.
Pointed demonstrations can help overcome some of the frustration and hopefully lower the learning curve a little.
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.5 & LibreOffice 6.0.4.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 890
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Is OO Base good for this?

Postby Plomo » Fri Jul 27, 2018 1:05 pm

Hi UnkiDonald. That was a pretty good answer. Thank you again.
I finally had some time to work on the database. I studied the first chapters of the tutorial you gave me. Once i thought i knew enough to do at least the first step. i did it. I collected all the data in my possession, thought about the goals to achieve and finally i planned the structure of the db and did the normalization of its tables. Of course i'm not completely sure about it. I'm going to attach it here, would you mind to take a look at it? :D

I sum up the "situation" and what i've learned so far:
the goal is to collect all the data in the PDF form, store them in the db and being able to extract a custom list of data related to each other. All this can be accomplished this way: i design the PDF form in Base using, indeed, a form. The data i put into the form will be automatically stored in tables previously created. The tables are all (with one exceptions, TBL_operators) related to each other by keys. Primary key in one table automatically populate the attribute of a second table they have a relation with (generating a foreign key in the second table). Primary Key ---> Foreign Key automatically generate a "1 to many" relationship (i've never seen 1 to 1 relationship although i know they exist :shock: ). I'll use queries to interrogate the db and extract the needed data that i finally put into reports (that i'm guessing are like forms but for outputs instead of inputs). Am i right or i'm full of misconceptions? :(

I have different doubt about how i designed the tables. Like:

- I mostly used "natural" primary keys instead of the "surrogate" ones cause i felt it was a better choice and helped me to better understand the structure of the db. I'm still not sure about it...

- 1 production can have 1 to 6 Pantone in it - one Pantone can be made with 1 to 6 ingredients plus 0 to 4 additives. Of course the form must be standardized meaning have the space to put 6 Pantone, 36 (Pantone x ingredients) and 24 additives (Pantone x additives) in it even if that production will have only 1 pantone with 1 ingredient and 0 additive. So my guess is: i create that standard form but in the tables i create only one table for the Pantone, 1 table for the Ingredients and 1 table for the Additives with just one primary key in them and then i link them to the main table with a foreign key. Is it right? Also, the quantities of ingredients and additives.. where do i put them?

..and other doubts, but right now i have no time to write them down, i have to flee! I just want to give you more information so to help you help me :lol: so please have in mind this:

At the beginning there are Codes. ;)
1 code = 1 production
1 production can have only 1 client but 1 client can have many productions
1 client can have many pantone and 1 pantone can have many clients
1 pantone can have many ingredients and many additives cause. Basically a Pantone can have different recipes (so different ingredients and different additives, or the same but in different quantities) cause the result of the recipe depends also by other factors like the type of containers or machines used etc etc...

So, i hope you can help me again to better understand the way to do it right. I wish you a really good week end. ..and i flee! Greetings!
Attachments
DB SILKSCREEN PRODUCTION.odb
(5.78 KiB) Downloaded 8 times
OpenOffice 3.3.0 - Mac OS X 10.6.8
Plomo
 
Posts: 6
Joined: Wed Jun 27, 2018 10:58 am

Re: Is OO Base good for this?

Postby UnklDonald418 » Fri Jul 27, 2018 7:02 pm

- I mostly used "natural" primary keys instead of the "surrogate" ones cause i felt it was a better choice and helped me to better understand the structure of the db. I'm still not sure about it...

While using Natural keys (Text, Date, non-integer numeric) isn't wrong, it can be problematic for several reasons.
1)When you start entering data, Natural keys often turn out not to be unique as originally thought.
2)Natural keys are dependent on the user to pick unique values.
3)When the HSQLDB engine sets up the relationships for tables related with non-integer primary keys, it internally builds integer indexes to establish the relationships.
A surrogate primary key of auto incrementing integer type avoids all of the above potential issues and often proves to be the the most efficient approach.
In the world of relational databases the table structure must make sense to the database engine which usually results in tables that aren't in a human readable format. When I see a table that is entirely human readable for example TBL_Containers, it tells me that table is a candidate for a more efficient design. I also wonder if there is a relationship between diameter and capacity, perhaps they could be combined into a single table.
i create that standard form but in the tables i create only one table for the Pantone, 1 table for the Ingredients and 1 table for the Additives with just one primary key in them and then i link them to the main table with a foreign key. Is it right?

Sounds reasonable. Form, SubForm, SubSubForm … structures allow linking the various tables together on a form document.
For instance, select a customer to see a list of related products on a SubForm grid. Select a product on that grid to see a list of pantones on a SubSubForm grid. Select a pantone on that grid to see the recipies on a SubSubSubForm grid.
With he current structure Print_methods, Machines and Containers are one to one relationships (a product has a single Print_method, Machine and Container).
I wonder if Operators could be associated with specific Machines or Print_methods.
the quantities of ingredients and additives.. where do i put them?

Since TBL_Recipies has fields(columns) for ID_ingredient and ID_additive, I think that is where the associated quantities also belong.
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.5 & LibreOffice 6.0.4.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 890
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Is OO Base good for this?

Postby Plomo » Mon Jul 30, 2018 4:09 pm

Thank you again for your kind reply, UnklDonald418.

Plomo wrote:I sum up the "situation" and what i've learned so far:


...you didn't say anything about it, did i summed it up right? :D :shock:


UnklDonald418 wrote:A surrogate primary key of auto incrementing integer type avoids all of the above potential issues and often proves to be the the most efficient approach.


Ok, fair enough. I'm going to change the natural primary keys into surrogate keys, i hope it lets me do it without redoing everything from scratch :( So, every natural keys i've created will become a "normal" attribute and the relationships will be only through surrogate keys... right?

UnklDonald418 wrote:for example TBL_Containers, it tells me that table is a candidate for a more efficient design


You mean besides adding surrogate keys? Like how?

UnklDonald418 wrote:I also wonder if there is a relationship between diameter and capacity, perhaps they could be combined into a single table.


Nope, a specific diameters doesn't imply a specific capacity (cause also the height is involved).

UnklDonald418 wrote:With he current structure Print_methods, Machines and Containers are one to one relationships


So even if in the relations panel it's written 1 -n it's actually a 1 - 1 relationship? Or do i have to manually change it?

UnklDonald418 wrote:I wonder if Operators could be associated with specific Machines or Print_methods.


Unfortunately it's not possible since one operator could work on any machine, also more than one operator can work on a single production (shifts)

This still really confuse me: if in the TBL_Recipies i put only one column for the ingredients (and the relative grams) and one column for the additive (and the relative grams), once i have a productions with a Pantone made of 6 ingredients and 4 additives how does the db engine put all the ingredients and additives?
OpenOffice 3.3.0 - Mac OS X 10.6.8
Plomo
 
Posts: 6
Joined: Wed Jun 27, 2018 10:58 am

Re: Is OO Base good for this?

Postby UnklDonald418 » Mon Jul 30, 2018 6:45 pm

you didn't say anything about it, did i summed it up right?

Your description looks reasonable.
So, every natural keys i've created will become a "normal" attribute and the relationships will be only through surrogate keys... right?

Yes.
You mean besides adding surrogate keys? Like how?

Changing all the foreign keys to integers instead of text fields should fix this table.
So even if in the relations panel it's written 1 -n it's actually a 1 - 1 relationship? Or do i have to manually change it?

The relations panel is correct. In the TBL_Products table each record (row) can have only one Print_method, Machine and Container. But for instance, a single TBL_Containers record can be used for multiple products.
This still really confuse me: if in the TBL_Recipies i put only one column for the ingredients (and the relative grams) and one column for the additive (and the relative grams), once i have a productions with a Pantone made of 6 ingredients and 4 additives how does the db engine put all the ingredients and additives?
Since TBL_Recipies has the foreign key ID_pantone, a query could display all the recipes for a given ID_pantone. Since you have already defined the relationships, you could use the Query Design GUI to generate a query using fields selected from TBL_Pantone, TBL_Recipies, TBL_Additives and TBL_Ingredients. Then, if you use the query as a data source for a SubForm with ID_Pantone in the Master and Slave selections it will display all the recipes for the selected pantone.
It is difficult to know how the results will look until you have entered some test data into your database.
more than one operator can work on a single production

In that case you will also need an INTERM_TBL_Operators table. You have to decide if it links to maybe TBL_Products or TBL_Pantone or even TBL_Recipies.
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.5 & LibreOffice 6.0.4.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 890
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Is OO Base good for this?

Postby Plomo » Tue Jul 31, 2018 8:24 am

UnklDonald418 wrote:In that case you will also need an INTERM_TBL_Operators table. You have to decide if it links to maybe TBL_Products or TBL_Pantone or even TBL_Recipies.


The field operator it's more like the field note, it won't be used to query the db. Also every PDF Form will have 4 operator signs (one each step of production), potentially all different to each other. I created the table Operators just to have a drop down menu in the PDF Form cause i already know all operators names.

UnklDonald418 wrote:Then, if you use the query as a data source for a SubForm with ID_Pantone in the Master and Slave selections


I guess i have to keep reading the tutorial :D cause i still have to properly understand how queries work (i thought they were just tools to get reports, not to fill up forms. Also, what's a Master and Slave selection? :shock: )
I think i'm going to first put some surrogate keys, do some adjustments and create that PDF form for inputs.
If it not bothers you i would like to keep you update to be sure i'm going in the right way :D

Thank you again, have a beautiful day.
OpenOffice 3.3.0 - Mac OS X 10.6.8
Plomo
 
Posts: 6
Joined: Wed Jun 27, 2018 10:58 am

Re: Is OO Base good for this?

Postby UnklDonald418 » Tue Jul 31, 2018 5:39 pm

what's a Master and Slave selection?

On a form document, these are used to link a SubForm to a master form. They are found on the Data tab of a Form Properties dialog when the form document is in the Edit/Design mode.
You might find the LibreOffice Base handbook helpful.
https://documentation.libreoffice.org/en/english-documentation/
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.5 & LibreOffice 6.0.4.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 890
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA


Return to Base

Who is online

Users browsing this forum: No registered users and 3 guests