Using Base for Multi-Users, Local Network, Single Database

[j0nr]

I am researching into whether Base can be used to suit our needs. We are a small manufacturing company, building products that require recording of build measurements/data during manufacture. Our high precision products need accurate records of lots of aspects during assembly. We call these our build cards.

Currently, we use an Excel spreadsheet to create a template for the build cards with lots of inputs for the Build Technicians to input data. The spreadsheet becomes a standalone file containing all data pertaining to that build. So we end up with lots of individual spreadsheets of data…but not interconnected (well not very well). We do some meta-analyses of the data, but it involves using “trick” ways to pull data out of all the individual files.

Maintaining the template and updating all the live build cards is an enormous task, involving duplicating current build cards, creating a new replacement if the template has been updated and then copy/pasting all data from current to new. I can hear you all groaning already…

Of course for a long time, it has been realised that the right way to do this is to use a database to store the info and utilise forms as the data input “front end”.

So my situation is that I need to see if we can achieve the following:

• Have a single database that records all these measurements.
• Have a Front-end user input form for the build technicians to use during build to enter data (must be simple and fool proof)
• Allow multiple users to have multiple copies of the form open simultaneously, all writing data to the database concurrently.
• Preferably have a file based system (as in files stored in a network location) although running a database server (e.g. MySQL) not out of the question…its just that I would need to get IT involved in that

I envisage creating a Form with all the required inputs, that the users then all open from the same source, presumably read-only, but doesn’t matter as they don’t need write access to the form itself, just the ability to use it to send data to the database. This way as we update the form (new/modified inputs) there si only one form to maintain.

A big way I want it to work is that the user opens the form, selects the serial number of the product they are working on, the form is populated with any data already entered and they can then carry on entering more. I have achieved this with a single database form/db file, but I am starting to realise this only works for one user at a time. Its the “getting it to work for multiple users on a LAN” bit I need help with.

What form do I give to the users? Its needs to be very simple and foolproof. Do they need to all install LO to use a form?

Sorry if this is an opinion based question and I hope I have got across what I am trying to achive but I am not sure which terminology to start searching for and don’t want to go down a route that is not suited to what I want to achieve and waste time learning the wrong thing.

[I have “hobby-level” experience with MYSQL and PHP/HTML style data entry, so am looking at it from that POV a bit]

[Regina]

LibreOffice can be uses as front-end to a lot of different database systems. It has an own interpreter for SQL, but it can use direct SQL-communication with a back-end database system on a server too. Do you know already our database guide? https://wiki.documentfoundation.org/Documentation/Publications#LibreOffice_Base_Handbook. But keep in mind, that all things, which are described for the in-built HSQL data base are possible with an server database system too and more.

[EasyTrieve]

Of course for a long time, it has been
realized that the right way to do this
is to use a database to store the info
and utilize forms as the data input
“front end”.

Good that you see this.

So my situation is that I need to see if we can achieve the following:

Have a single database that records all these measurements.

ok

Have a Front-end user input form for the build technicians to use

during build to enter data (must be
simple and fool proof)

ok

Allow multiple users to have multiple copies of the form open

simultaneously, all writing data to
the database concurrently.

ok

Preferably have a file based system (as in files stored in a

network location) although running a
database server (e.g. MySQL) not out
of the question…its just that I
would need to get IT involved in that

Suggest MariaDB or MySQL, initially place them on an Internet hosting server. This will also allow remote development. There are also other 3rd party tools that can be used with these two which which I like, like HeidiSQL and phpMyAdmin for example.

I envisage creating a Form with all
the required inputs, that the users
then all open from the same source,
presumably read-only, but doesn’t
matter as they don’t need write access
to the form itself, just the ability
to use it to send data to the
database. This way as we update the
form (new/modified inputs) there is
only one form to maintain.

I sometimes prefer to have a number of forms, each serving specific needs, and doing it well rather than one big form.

Also I see no reason why you can’t allow multiple places of editing and updating. MySQL and MariaDB have very nice user level security which can be used to allow or disallow many aspects of the data access and editing.

A big way I want it to work is that
the user opens the form, selects the
serial number of the product they are
working on, the form is populated with
any data already entered and they can
then carry on entering more. I have
achieved this with a single database
form/db file, but I am starting to
realize this only works for one user
at a time. Its the “getting it to work
for multiple users on a LAN” bit I
need help with.

Multiple users should not be a problem. Each user connects with a possibly different user name and password.

What form do I give to the users? Its needs to be very simple and foolproof. Do they need to all install LO to use a form?

Yes, each user needs to have LO installed. And each user needs to have one or more .odb files which start up to open and connect to the database and provide the forms.

Sorry if this is an opinion based question and I hope I have got across what I am trying to achieve but I am not sure which terminology to start searching for and don’t want to go down a route that is not suited to what I want to achieve and waste time learning the wrong thing.

[I have “hobby-level” experience with MYSQL and PHP/HTML style data entry, so am looking at it from that POV a bit]

I think you’ll want someone on your team with proven LO experience who has done something like this before with LO. That should help you avoid pitfalls. LO can be a challenge at times for any of us. But it works pretty good once you figure it out. BTW, what I say here I think goes for any database products you work with, all of them have issues that need to be dealt with, and learning any of them takes time. I just don’t want to see you in a position of not quite having it done and your boss breathing down your neck asking when, when, when. Easier to have someone with some experience who can provide some measure of surety of development time.

Hope this helps you make your decisions.

[rautamiekka]

All good. Indeed, he’d need MariaDB 10.1.x (the current stable, General Availability; for now, MariaDB is a superior MySQL 5.6 fork; the vanilla Oracle MySQL should just be dropped off the options list; likewise wouldn’t recommend Percona Server, another fork MariaDB is superior to, and supports only Linux) installed to some computer, preferably a dedicated one. The good news is this should be fairly painless as far as configuration goes if he doesn’t have too many ppl, otherwise just read …

[rautamiekka]

^ Addendum: … the docs. The forms are a different problem: if they’re not provided as a website (which is another problem security-wise), the ODB would have to be synced automatically between users, which is most easily done through symlinking and network sharing.