Page 1 of 2

Is OO Base good for this?

Posted: Wed Jun 27, 2018 1:03 pm
by Plomo
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 393 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.

Re: Is OO Base good for this?

Posted: Wed Jun 27, 2018 1:56 pm
by Plomo
It looks like i can't upload PDF files so i made a png copy.
form
form

Re: Is OO Base good for this?

Posted: Wed Jun 27, 2018 9:14 pm
by UnklDonald418
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

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/ima ... torial.pdf

Re: Is OO Base good for this?

Posted: Fri Jun 29, 2018 1:13 am
by UnklDonald418
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_

Re: Is OO Base good for this?

Posted: Fri Jun 29, 2018 4:45 pm
by Plomo
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.

Re: Is OO Base good for this?

Posted: Fri Jun 29, 2018 6:54 pm
by UnklDonald418
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.

Re: Is OO Base good for this?

Posted: Fri Jul 27, 2018 1:05 pm
by Plomo
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!

Re: Is OO Base good for this?

Posted: Fri Jul 27, 2018 7:02 pm
by UnklDonald418
- 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.

Re: Is OO Base good for this?

Posted: Mon Jul 30, 2018 4:09 pm
by Plomo
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?

Re: Is OO Base good for this?

Posted: Mon Jul 30, 2018 6:45 pm
by UnklDonald418
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.

Re: Is OO Base good for this?

Posted: Tue Jul 31, 2018 8:24 am
by Plomo
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.

Re: Is OO Base good for this?

Posted: Tue Jul 31, 2018 5:39 pm
by UnklDonald418
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/e ... mentation/

Re: Is OO Base good for this?

Posted: Tue Sep 25, 2018 10:23 am
by Plomo
HI UnklDonald418 :D Unfortunately for you, I'm back :roll: I hope you had a great summer. Mine was too short and now i'm here again trying to figure this db out while doing my regular tasks :knock:
I hope you still have the patience to help me out, but if you had not, i wouldn't blame you :super:
I'm going to change the natural primary keys into surrogate keys, i hope it lets me do it without redoing everything from scratch :(
Nope. As i suspected, it seems i can't do it. I'm continuously getting errors that i can't quite understand... Also, it's not clear to me how this works. Let's say i manage to change the table "TBL_Clients" to have a surrogate key named ID and an attribute named ID_Clients (in which i put all the clients i have). I'll leave the surrogate key alone, without any relationship, right? Then i should link ID_Clients to the homonym in the table TBL_Products... but that would make it a primary key and i can't have 2 primary key in the same table.. I'm so confused.

Have a nice day, hope to hear from you soon. Cheers.

Re: Is OO Base good for this?

Posted: Wed Sep 26, 2018 6:40 am
by UnklDonald418
I'll leave the surrogate key alone, without any relationship, right? Then i should link ID_Clients to the homonym in the table TBL_Products... but that would make it a primary key and i can't have 2 primary key in the same table..
No, the surrogate primary key "ID" would relate to an integer field "ClientID" in "TBL_Products". "TBL_Products" would have it's own primary key and the "ClientID" field would be a foreign key not a primary key. Foreign keys are not directly defined in a table like a primary key, they are defined by their relationship to a field in another table.
There is no need for the text field "ID_Clients" in "TBL_Clients".
Your "TBL_Clients" should be structured similar to the one in included in the example DATABASE SILKSCREEN PRINT-modified.odb that I uploaded earlier.
Nope. As i suspected, it seems i can't do it. I'm continuously getting errors that i can't quite understand..
Try this proceedure.
Rename you table, for instance "Table1" to "Table1Old".
Right click on "Table1Old" and and select Copy.
Right click again and select Paste to open the Copy table dialog.
Change Table name back to the original name (i.e. Table1) and select the Definition and Data option.
Select Next to open the Apply columns page.
Select all the fields and then Next
On the Type formatting page, right click on the current Primary Key field and select Primary key so that the small key icon is no longer visible.
Select Create and you should see a dialog warning asking if "a primary key should be created now?"
Select Yes
After that completes select View>Refresh Tables.
Right click on the new table and select Edit to open the table design GUI.
Select the ID field and change Auto Value to Yes.
Save and close the table.

Re: Is OO Base good for this?

Posted: Mon Jan 07, 2019 1:36 pm
by Plomo
Hi Donald... happy 2019! Hope you enjoyed your holidays! yes i'm back, i'm not going to give up, not yet! Since September i made some progress and learned more about databasing ;) I also switched to LibreOffice 4.2.0.4, the latest version my mac allowed me to use. I feel i'm close to a conclusion but yeah, i'm still here, stuck in a problem i don't know how to solve.
I created two forms, CONSULTAZIONE (to only read records) and INSERIMENTO (to only add records). I created the relations between al the tables, filled them with some samples and arranged in advance all the PANTONE tables (5 max each Form) even if i created the relations only for the TBL_Pantone_A.
The problem is pretty much this: i can't collect data, i can't relate the Pantone table to the Cartella table. it doens't save the relations and i think it overwrite the data in the Pantone table.. :shock:
I made 2 versions of the same DB, one of them has a intermediate table.. still not working.
I attach both.

Donald, i really hope you can give me the right tip to keep going and finally get the result i need. Anyway, i want you to know i'm really grateful for your attention and care.

P.S.: this version of database is in italian.. i hope it's not a problem, the translation is pretty intuitive, but if you need any help with it, just ask me!

Wish you the best, truly.

Re: Is OO Base good for this?

Posted: Thu Jan 17, 2019 12:30 am
by UnklDonald418
Attached is a version of your database with modifications to TBL_Cartella and TBL_Intermedia_CL_Pantoni. TBL_Ricette is new and TBL_Pantone replaces TBL_Pantone_A … TBL_Pantone_F. The result is 16 tables with all the relationships defined (see Tools>Relationships)

INSERIMENTO01 is based your form document. There are some issues putting 2 separate Forms on a single page so I added two other form documents that work better.
PantoneRecipeEntryEdit can be used to enter new Pantones and associated recipes, or edit existing ones. Using it should be fairly straightforward since most of the fields use list boxes. Be aware the if an existing Pantone is edited the changes will apply retroactively to all entries in TBL_Intermedia_CL_Pantoni that reference that Pantone.
CartellaEntryEdit needs a little more explanation.
The area above the navigation bar was copied from INSERIMENTO.
The table control below to the left below the navigation bar is where Pantone A thru F can be selected. The list box allows choosing from any existing Pantone. The selection(s) are saved in TBL_Intermedia_CL_Pantoni.
If a new selection in made then press the push button Display Pantone Detail and the Pantone Details table control to the right will be updated with the details for all the selected Panontes.
The table below shows the recipe(s) for the Pantone selected in the Pantone Details table control.
Let me know if there are any questions or problems.
DB_CARTELLE_aiuto_4 TBL INTERMEDIA - Copy.odb
(93.53 KiB) Downloaded 246 times

Re: Is OO Base good for this?

Posted: Thu Jan 17, 2019 1:21 pm
by Plomo
Hi Donald,
i'm glad to hear back from you. I'll never thank you enough for your effort ;)
I swear, i was feeling the relationships i created were too messy.. indeed yesterday i did some changes to them:
screenshot.jpg
'cause out of the blue i had a different vision and concept on the most efficient db structures to get the result i need. And it appears my relationships diagram is actually pretty similar to yours, the only difference is that i put together your TBL_Ricette and TBL_Pantone in a single TBL_RICETTA and added a TBL_Pantoni linked to it ;)

If i interpreted right the db version you kindly made and i'm testing it rightly, i think there are some features that don't fit my needs.
UnklDonald418 wrote:There are some issues putting 2 separate Forms on a single page so I added two other form documents that work better.
So you're telling me i can't have a single Form with the PDF template -
form template
form template
- including all the data about the product and all the (1 to 6) recipes for its Pantones? That would be an issue 'cause the operators need a blank A3 sheet to fill with datas by hands...

So i'm assuming those 2 separate Forms would be PantoneRecipeEntryEdit and CartellaEntryEdit while INSERIMENTO is meant to be just to display records (but i can still modify the data on the left and those above the navigation bar... :?: )
UnklDonald418 wrote:Be aware the if an existing Pantone is edited the changes will apply retroactively to all entries in TBL_Intermedia_CL_Pantoni that reference that Pantone
The fact is that it shouldn't be like this ;) Let me explain it.
As i said, modifying my relationships diagram i added a TBL_Pantoni in wich i want to insert all the existing Pantone colors i can find, not those we already used/will use in our productions, i'm talking about all the colors the Pantone Inc. "produces". They are quite all named with numeric code (P 100, P 101, P 102 and so on) and that is the only data to put in that table. I know it's probably unnecessary and it will create an awkwardly huge list-box of hundreds of code-name ;) but it helps understand that Pantones themselves don't already have recipes and even if they had, we use the recipe we create when printing a graphic that use that Pantone color (lets say P 100) 'cause that P 100 could have x different recipes datas depending on the variables of that specific production.
Therefore if a second graphic (from the same or a different client) has the P 100 again, i need to create another recipe for that Pantone. It means that attached to every Cartella there must be all the Pantones (those used for that graphic, of course) and their recipes displayed, even if they are the same in every details on another Cartella.
So if i should ever need to edit a recipe it would be only that particular recipe of that Pantone used in that Cartella.
UnklDonald418 wrote:If a new selection in made then press the push button Display Pantone Detail and the Pantone Details table control to the right will be updated with the details for all the selected Panontes.
The table below shows the recipe(s) for the Pantone selected in the Pantone Details table control.
This is a really outstanding way to display and interact with recipes, unfortunately i fear it doesn't fit my needs. I can see it in a Form apart created only to consult all the recipes maybe filtered by type or color of packaging, material or whatever, but in the "CARTELLA DI LAVORAZIONE" form the details and recipes of all the Pantones (A,B,C,D,E,F) should be always visible without having to interact with the form (the only interaction in that form should be the input and eventually the correction of the datas)

I think those are the major questions to solve, anyway i feel we are close to a good solution! Thank you again Donald, hope to hear you back soon.
Have a great day.

Plomo

Re: Is OO Base good for this?

Posted: Thu Jan 17, 2019 5:30 pm
by Plomo
Hi Donald,
i just had a flash of inspiration and before come back home from office i want to share it with you, look this:
flash-of-inspiration.jpg
i put the TBL_Elenco_Pantoni (the list of all the Pantoni colors) inbetween TBL_Ricetta and TBL_Cartella. So, 1 Pantone can have many recipes and 1 Pantone could be associated at any Pantone's "slot" (A or B or C or D or E or F) so technically "many" (even tho it doesnt make sense to have the same Pantone in multiple slots in the same Cartella)

What do you think about it?
Have a great day!

Re: Is OO Base good for this?

Posted: Thu Jan 17, 2019 10:44 pm
by Villeroy
There is no practical reason to avoid many-to-many relations like you do.

Re: Is OO Base good for this?

Posted: Fri Jan 18, 2019 9:21 am
by Plomo
Villeroy wrote:There is no practical reason to avoid many-to-many relations like you do.
The point is that i'm not doing it on purpose, it's that i'm not sure how to design it :(

Re: Is OO Base good for this?

Posted: Fri Jan 18, 2019 6:16 pm
by Villeroy
Do it like "Uncle Dondald" did. TBL_Pantone is a list of pantoni (whatever that is), TBL_Cartella is a list of cartellas (whatever that is) and TBL_Intermedia_CL_Pantoni stores the information which cartellas (plural) belong to which pantoni (plural). This is how a many-to-many relation is built in a relational database.

A more simple example: viewtopic.php?f=100&t=40444 where each person may have zero, one or more things and every thing may belong to zero, one or more persons. The list of persons is mapped to the list of things by an intermediate table "P_T". In that example I used only 2 columns in "P_T", both building one combined primary key. This avoids duplicate combinations of things and persons.
When you build a form to make this relation editable, you always link a main form to one side and the subform with a table control to the intermediate table. The other side is represented by a column of list boxes within the subform's table grid. This way you select one item in the main form and get a group of the other item's listed in the subform and selectable from list boxes.

Re: Is OO Base good for this?

Posted: Fri Jan 18, 2019 6:43 pm
by RoryOF
Just for information: Pantone is a colour matching system. One can specify a particular Pantone colour and ink/paint can be ordered to match that.

Re: Is OO Base good for this?

Posted: Fri Jan 18, 2019 6:47 pm
by Villeroy
RoryOF wrote:Just for information: Pantone is a colour matching system. One can specify a particular Pantone colour and ink/paint can be ordered to match that.
How is the database related to the colour matching system? Receips for pigments?

Re: Is OO Base good for this?

Posted: Fri Jan 18, 2019 6:51 pm
by RoryOF
I haven't been following the detail of this thread, so cannot say. I just caught your reference to Pantone and thought I would explain the meaning I understood for it.

Re: Is OO Base good for this?

Posted: Fri Jan 18, 2019 7:12 pm
by UnklDonald418
Villeroy wrote:There is no practical reason to avoid many-to-many relations like you do.
The point is that i'm not doing it on purpose, it's that i'm not sure how to design it
TBL_Intermedia_CL_Pantoni in the version I uploaded is an intersection table that does that.
However, you also said
P 100 could have x different recipes datas depending on the variables of that specific production.

Which means "Pantone_ID" may have a direct relation to TBL_RICETTA but definitely not to TBL_Cartella making fields "ID_Pantone_A" … "ID_Pantone_F" and TBL_Intermedia_CL_Pantoni all useless.
There appears to be a many-to-many relationship between TBL_RICETTA and TBL_Cartella that can be established by executing

Code: Select all

CREATE TABLE "TBL_CART_INT_RIC" (
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
"CartellaID" INTEGER NOT NULL,
"RicettaID" INTEGER NOT NULL,
CONSTRAINT FK_CLINT FOREIGN KEY ("CartellaID") REFERENCES "TBL_Cartella" ("CL_ID"),
CONSTRAINT FK_RIINT FOREIGN KEY ("RicettaID") REFERENCES "TBL_RICETTA" ("ID_RICETTA")
);
at Tools>SQL, followed by selecting View>Refresh Tables. Then look in Tools>Relationships to verify a one to many relationship between TBL_Cartella and the intersection table TBL_CART_INT_RIC and another between TBL_RICETTA and TBL_CART_INT_RIC which results in a many to many relationship between TBL_Cartella and TBL_RICETTA.
all the recipes maybe filtered by type or color of packaging, material or whatever,
That would be relatively easy with normalized tables but would be very difficult with your current TBL_RICETTA.
I recommend that getting a working database be your first priority and then work on designing specific forms. Base forms are quite flexible but I doubt they can entirely replicate your pdf template so some trade-offs may be required.

Re: Is OO Base good for this?

Posted: Mon Jan 21, 2019 10:52 am
by Plomo
Hello everyone,
thank you all for your interest, i really appreciate it.
Villeroy wrote:Do it like "Uncle Dondald" did. TBL_Pantone is a list of pantoni (whatever that is), TBL_Cartella is a list of cartellas (whatever that is) and TBL_Intermedia_CL_Pantoni stores the information which cartellas (plural) belong to which pantoni (plural). This is how a many-to-many relation is built in a relational database.
As Donald later explained that wouldn't work. You're still right about using intermediate tables, i just have to get "where" to put them :D
UnklDonald418 wrote:Which means "Pantone_ID" may have a direct relation to TBL_RICETTA but definitely not to TBL_Cartella
Now i see it. Yep, you're right. At the end, "Pantone" is not identifier of anything. It's just a normal parameter like Serie, Toni, Spatola etc etc...
Also yes, the many-to-many relationship we need seems to be between Cartella and Ricetta. I'm going to create it right away by following your instructions.
UnklDonald418 wrote:I recommend that getting a working database be your first priority and then work on designing specific forms.
I see and you are right on it but my fear is to waste time working on something that after all can't be visualized in a proper way due to those trade-offs. Operators need to be able to write down all those data on a paper not too much different from the actual one.
RoryOF wrote:Just for information: Pantone is a colour matching system. One can specify a particular Pantone colour and ink/paint can be ordered to match that.
That's basically correct.
Villeroy wrote:How is the database related to the colour matching system? Receips for pigments?
Client: "I need you to silkscreen print this NEW graphic. it's made with Pantone A and Pantone B."
Operators: "Have we already printed graphics with the same Pantone/Pantoni on a packaging of the same color and material? If so, can i have the recipes with the inks and additives we used so i don't have to start from scratch?"
Database: "Yes/No. I'll print you a blank Cartella (form) and eventually the recipes to be taken as a starting point"
n.b. it could end up using the same old recipe as much creating a new one

Client: "I need you to silkscreen print AGAIN this graphic. Pantoni didn't change."
Operators: "We did it but we made some changes to some/all Pantone's recipes due to X"
Database: "Ok, i'll update the relative Cartella"

-----------------------------------------

I'm sorry, i had to create the database in my language to better confront with my colleagues...
I'm going to try to replace all the names in english. Meanwhile, here's a brief vocabulary

Cartella: folder (cause the form will be printed on an A3 paper and then folded)
Elenco: list
Inserimento: input
Consultazione: consultation
Esposizioni: exposure
Inchiostro/tono: ink
Gelatina: jelly
Gomma: rubber
Neutro: the packaging not yet printed
Ricetta: recipe
Tappo: cork
Tessuto: silk


Thank you again everybody! I wish you all the best.
Plomo

Re: Is OO Base good for this?

Posted: Mon Jan 21, 2019 12:40 pm
by Plomo
So,
i thought that, considered all the help i'm getting, the least i could do was to put everything in english.
Here's a screenshot of the db relationships (i added the intermediate table as Donald told me)
I just wanted to share this right away, now i'm going to design a form specifically for testing. I'll be right back :D

Cheers.
screenshot-db-translate.jpg

Re: Is OO Base good for this?

Posted: Mon Jan 21, 2019 6:46 pm
by Plomo
Hello everyone,
As i said i did a form for testing the new relationships diagram. Unfortunately i must go back home right now. I'm going to share it anyway just to get ahead (godamn time zone! :? ) but i'll be able to do it only tomorrow morning (14 hours from now).
- I made some simplifications: now there's only one Serie each Pantone 'cause theoretically we should use inks and additives of the same series..
- I have to digit every Pantone name 2 times, the first one associated to datas such as SIlkscreen, Rubbers, Exposure etc etc and the second one to Inks, additive and so on (see the pdf form attached few message before). I know it's redundant but if it's possible i'd keep it this way (i'll still ask my boss if we can get rid of the first one). So i thought for the moment we could keep the first one not linked to any tables, just a text box on its own...

Let me know what you think, i'm looking forward to try this test!

I wish you all the freaking best!
Cheers

Re: Is OO Base good for this?

Posted: Mon Jan 21, 2019 9:00 pm
by John_Ha
RoryOF wrote:I haven't been following the detail of this thread, so cannot say. I just caught your reference to Pantone and thought I would explain the meaning I understood for it.
... and not, of course, to be confused with panettone which tastes quite delicious.

Re: Is OO Base good for this?

Posted: Tue Jan 22, 2019 11:04 am
by Plomo
Hi everyone,
i guess i rushed too much, the test form was too much incomplete to even think to work... I set the list-boxes's Type fo list contents to Sql and List content with the relative tables.
The first part is working fine, i can add Clients, Pacakaging etc etc but when it comes to Silkscreen and Exposure: they are grayed out. Also if i click on any of the RECIPE 1 fields this pops up:
error.png
but I filled those fields, i don't get why it says that..
After that i found out that CL_ID (now DOSSIER_ID for more consistency) and ID_RECIPE were not primary key, i switched them to PK hoping to solve the problem but it didn't (still, i think they should be PK :? )

What is actually going on here? (database attached)

P.S.
John_Ha wrote:to be confused with panettone
John you're right. So if you want to be sure to avoid any mistakes, Pandoro is the way to go!
Pandoro_cut_01.jpg
Pandoro_cut_01.jpg (22.33 KiB) Viewed 8106 times