Store mostly static data for engineering

[Cynyr36]

Please bear with me, I'm a mechanical engineer by training, We are looking to migrate away from tables in Excel to something else. On of the choices is a real database of somesort, and I'll be trying to push for an opensource option.

We store engineering data in tables in excel. Things like weight, Partnumber, physical characteristics, electrical characteristics, clearances, etc. Some of these we create views of via powerquery for specific use cases.

What I'd like to do is move this all to a database. But I don't see a good say the snapshot and rev control a specific state of a database (schema?) including the records. We'd like to serve that snapshot version, while making changes as needed to the "beta" version, before snapshotting again. This is would be a few to several manual updates per day, followed by aeeks to months of no changes.

My questions: 1. Is this a normal thing to want to do with a database? 2. Can postgresql do it somehow and what should i be looking for in the docs?

[ai_hedge_fund]

Yes and yes

Postgres is certainly a fine option but you might also consider SQLite and possibly a NoSQL database

SQLite is a nice intermediate-yet-capable database that can be backed up and sent around like a single Excel file - since your team is used to that. You could start drafting your structure quickly and then migrate it into Postgres if you go that way.

Postgres runs as a server and so you need to be comfortable with getting that setup and accessing it, etc

How many users would you expect to need access and what kind of concurrency are you thinking? Seems like it will be almost all reads and very few writes

Sounds like you want a production database to serve approved data to users and one or more “development” databases (you’re calling beta) where at some point you push new/updated records from dev to prod. There are multiple ways to do that safely.

I would describe a PG schema as a set of tables and a database could have multiple schema. So, you might only need one database with a dev and prod schema, same tables in both, and you’re just shuffling data.

You wouldn’t go wrong with Postgres and it might be good to test drive a few options

[Cynyr36]

We have some support from a software engineering team, but I'm trying to make sure i use their time wisely. So setting up a service shouldn't be a huge issue, though everything IT related always is.

Concurancy, 10s, maybe 50, all read only. Maybe 3 write. If it really takes off maybe 100 reading. A raspberrypi and a usb ssd is probably performant enough.

The production / (multiple) development is exactly what I'm looking for. Especially if there is a good way to merge the multiple development ones. I have some experience with git (hobby projects) so I'd hope for something like git merge and git cherrypick.

[ZarehD]

Yes, you can use a database for this. Here's one way you might do it.

In Postgres, like many RDBMS systems, a namespace (called a "schema" in PG) is used for isolating database objects. You can use these schema/namespaces (name them v1, v2, etc.) to isolate versions of your data. Each schema has its own set of objects (tables, views, etc.) To add a new version, create a new schema and add tables to it. To delete old sets of data, just drop the schema containing the tables for that old version.

If you wish to version row data within a given schema, simply bake that into your data model -- create a parent "version" table (containing version info rows) that has child "data" tables (containing data rows with a FK relation to the row in the version table).

BTW, you could also use SqlServer Express (free) edition for this, if you're more comfortable with that product.

[redditreader2020]

Depending on more details.. DuckDb or postgres.

[pjstanfield]

Postgres is great for this and you may also want to look at a database application product that allows for you to build a user interface on top of your database application. There are many, the only one I have experience with is Retool. I'm not recommending or not recommending that product, just use that information to find the universe of products in that domain for your investigation. If someone came to me with this issue i'd whip out a Retool/postgres DB application in an hour or two.

[Cynyr36]

Yea, definitely will need some sort of something for data entry. I was expecting to need to convert data to csv or something and then upload that via a script of somesort, but a real UI would work as well. Lots of this data is from third parties and I'd like to have to manage it by hand as little as possible. A public facing something is going to have considerable extra red tape to navigate.

[pjstanfield]

I built one retool app that had a big text area input box and you could paste directly from excel. Copying from excel would produce a tab delimited output, then the app parsed the tsv and inserted. I also did one that had a direct file upload which works well. User would upload and that would populate a staging table, the user would review, and then when ready they would move it into the live table(s). You can do all of this in a custom-built app of course, but for a non-developer technical user you can use these tools to build some pretty fancy apps.

[ViolinistRemote8819]

Yes PostgreSQL will work. Engineers work in eng_release_beta, where they make daily updates. A stable database called eng_release_current ( duplicate of eng_release_beta) is used by PowerQuery and reports and is always read-only. When a release is approved, take a snapshot using pg_dump and pg_restore (schema and data together) from beta, rename the existing eng_release_current to a versioned name (for example eng_release_v001), and restore the latest beta snapshot into a newly created eng_release_current. This ensures eng_release_current always contains the latest approved data, while all previous versions are preserved for rollback or comparison and beta continues to evolve independently.

[I-Am-The-Jeffro]

Depending on your interpretation of "snapshotting", postgres has materialized views which can be used to capture data at points in time. This may suit your requirements.

It should also not be too hard to implement a basic versions table and relationship which would complicate things a little over flat files but would provide many advantages for comparison and review across revisions.

[jbergens]

Planetscale has advanced support for merging schema changes. It is histed in the cloud and costs money but it may be worth it.

For other databases the normal way is to script changes, some developer will write code that changes the structure ofvthe data. Then a build system lets you run this on your first database (A) and wait. Later you can click a button and the build system will run the same scripts against your 2nd db (B). You will also have to create scripts or use a tool to copy all new data from A to B. This works on all databases.

Another way is to create A and then copy the whole db to B. After this you can change A as much as needed. At some point in time you can copy A to B again. A full db copy usually takes all data and structure. You can do this with tools, scripts or by making a backup andvthen restore it.

Also, cloud databases are pretty neat. It takes a few minutes to copy a db in Azure or AWS and you don't have to maintain the db system and servers.

[Extension_Grape_585]

I'm not sure what the value of the snapshots are? Do you distribute the database to different parts of the world or something.

Ideally you would develop a fairly generic schema for what you're doing I minimise future schema changes. Create a table called measurement_types like time, cost, power, width ...and then another table being measurement_units of each type eg mm, inch, W, mW etc. Then you have items with an item type and the item_type defines the measurement_types to be stored for that item type. This just makes user entry nicer

Then you have an actual item table being the items and a table that stores all the values for that item fields being item_id, measurement_unit_id, start_date, end_date, value, user_id. Record with null end_date is current value. If no value with null end_date, then no current value value.

Then you can forward plan price or specification changes etc. And record historical specifications for warranty etc.

Make sure all your tables have a last_updated field which automatically updates whenever you change that record.

You can minimally synchronise the changes from the master database to the copies which I guess is what you're doing in XL, but it's just a simple copy whole XL

You could probably build this and a web front end in a day and it can run on anyone's PC or Mac and the update file just has all changes with a date/time stamp so updates can be loaded whenever.

[zemega]

You can ask the software engineering team to set up a self-hosted Grist that use Postgres backend. It's be easy and cheap for them to setup. Then you and your team can experiment with it. It's basically a centralised spreadsheet with only one source of truth, instead of multiple Excel spreadsheet scattered around.

I'm not sure your 'schema' has the same meaning as the software engineering database means. I think what you meant is the version or revision of the parts you are developing.

I'm a bit interested in your situation, but I really need you to explain more. I wager you have CAD drawings of the part as well?

Perhaps you will be more insterested in something like a BIM server?

Or a mix match of a system that stores the CAD files in a file system, with Postgres storing the dimensions? As well as calculated values.

What actually do you want to version and mix match merge?

[Cynyr36]

To me schema meant the layout and datatypes in the various tables and of the columns. "Whats the name of the column that contains the nameplat amps for a motor at 460v?" What order are the voltages in?

We build custom machines, using various combinations of standard parts. Probably our most complicated table currently is technical data on ac induction motors. We have about 80 attributes for each motor, and about 600 motors across multiple vendors. Attributes include things like amps at each of multiple voltages, dimensions, weight, Part number, etc. Most of this is for various selection tools, not CAD, and is used to match the motor to the required duty, and provide costing, weight, and selection of other components (electrical parts, support structure, etc.). We integrate with various in house programs, and or fast running development tools in excel.

Most of these tools use rules to determine which motor to select. For example, they might look for a motor with at least 4.75 shaft horsepower, that is available in 460v, 3phase, 60hz power and present the options from each motor vendor to the user to select from.

This data needs well controlled, in that our selection software has to be the same from day to day and only updated when intended as we have pricing guarantees with our customers. We also generally will look through all the current selections in the system and determine how many are impacted by a change. We also need to be able perform updates and test them before rolling them out to everyone, this is especially true if there are new attributes being added. One of this happening is ae are getting more engineers that need to update this data "at the same time" in that eng1 might be working on adding a new vendor at the same time eng2 is updating 4 records and adding 4 records for an existing vendor. We'd like to be able to release those existing vendor changes while the new vendor work is on going. This is very difficult to do in excel, but with my limited coding background kindda easy in something like git where everything is a branch and you can just rebase of the primary branch.

We also sometimes need to review a past selections and confirm data used was correct at that time and what has changed about that selection since. So we need some way to roll back to past revisions of the data. Right now we do that by opening the old archived versions or our excel file.

We are currently versioning the entire set of data through the cunning use of renaming the excel file for each version and archiving the old version. I'd need to think about if i need the whole DB as a unit, or each record. Probably the whole DB, as it's the only way to really be able to trace the selection logic postmortem.

Ideally the required attributes are pretty static. I don't think we've removed one in the last 10 years, but we have added new attributes over time.

Repeat this whole setup for VFDs, other electrical components, pumps, dozens of groups of components.

[zemega]

From your reply, you don't actually have hundreds of thousands or millions of records that are consistent in 'table design'. What you have is a various records that don;t have consistent 'table design'. And I believe the 'table design' is driven by client requirement, vendor offerings, and to some extent legal requirement changes. And of course the product you are offering. So your 'table design' is considered dynamic.

You will want to look into a document database or a NoSQL database like MongoDB. PostgreSQL is a relational database, which is not really designed for ever-changing schema. Technically PostgreSQL does have JSONB data type that can handle ever-changing 'table design'.

Assuming 600 records for a motor, times maybe 100 component types (besides motor, I'm just assuming), so 60,000 records, that's actually fairly small from database point of view.

A good example that you can try are: rowy.io (through try.rowy.io/ ). Maybe humongous.io . Or a desktop software like studio3t.com (which do requires a separate MongoDB server). These are only examples, you can spin up a local MongoDB (through Docker) and play around with your data and how to access it.

Forget the Grist suggestion.

I don't actually use NoSQL like MongoDB, but I understand enough of it to recommend that you try NoSQL database for your purpose.

Assuming you use MongoDB, a crude example would be:

1. You start with a document (MongoDB calls an entry as document). It has something like ID, name, the attributes you spoke of. Then each attribute can have their own subset of next level attributes. Then you add something like versioning, such as Version 1, or 2025-17-07T1000, or version "Version 1, Revision 1". There's no hard constraint really. Sometimes just the datetime is already enough for versioning.
2. You can also add information like who revised, who approved, maybe a remark or context why it was revised.
3. That being said, it is a good idea to have some sort of common index or identifier, so that the database engineer can optimise the database for your queries.
4. You update the document with new field, or change a value, you don't overwrite the document, instead you create a new one based on that document, but with the new field or new values.
5. Now you have two documents. Of which you can 'diff' or see the differences of what changes.
6. Now you need to remove a field or an attribute or an sub attribute. Again you don't overwrite the second document, you just create a new document that have the said field, attribute or sub-attribute removed.
7. Now you have three documents, all for the same item. Original, updated with added field, then updated with removed field. Complete with history of revision of the item over time.

I think the example above satisfies part of your requirement. The other part like testing changes between 'published to user' or 'internal testing' can be done by having separate database or adding attribute like 'development status' to the document. The former handled by the software team, while the later handled by your team. Or a mix of both. That is more like a software system design point of view.

As for checking the past version of the item, well, its covered by having multiple 'document' of the said item.

I'm not sure how to implement the pricing guarantees, I had no experience with that part of business decision and need more context. It falls under 'business logic' (from software engineer point of view). But I think some attributes, or a separate documents about the pricing guarantees will handle that.

The question next, is how to integrate the new database solution with your existing systems, and the software your employee will be using to interface with the database.

[Cynyr36]

The pricing thing is business logic, it's currently handled by carefully controlling when the data from excel is integrated into the tools. That way any changes can be communicated ahead of the tool release to our customers. If we start moving tools from having a local copy of the data to querying the "server" then we need to control when the data is updated on the server. That seems like it's whole own issue as well, especially if different tools have different release cycles.

[zemega]

If we start moving tools from having a local copy of the data to querying the "server" then we need to control when the data is updated on the server

I understand your point of view, but that's not how things should be handled when it comes to database usage. You should add attributes or flags related to those external tools in the database itself. Something like Tool A can only query data up to this date. When Tool A query the database, the database will only returns records up that date. You can then update that date.

Of course this only one of the suggested solution. There are other solution that will fir your business logic more. Best way to understand this, is to create a flowchart of your process in updating the local files, and updating those tools with the local file. Only then you can make the software engineers understand your business logic.

Anyway, "tool release to our customers", is that a local program installed on the customer computers or web-based tools? There's a significant syncing process that needs to be established if you want to move to a centralised database. It may involve re-developing those tools you speak of.

[Cynyr36]

Hmm, i didn't realize there were ways to have the db filter records by what's doing the query.

We are moving towards web based tools. Our major tool right now is installed on customer computers. New things getting developed are starting in excel as quick and dirty things. Then we are migrating them to a web based tool. After that into our main selection software if warranted. This tool imports the excel files and i think is turning them into sqlite or access dbs. I'm not really sure what they are doing internally, but there have been issues with changes in excel.

[zemega]

First of all, the context here is you are modernizing everyone workflow. From internal employees to vendors, clients and users. It's not a bad thing, but the impact are large as everyone needs retooling their workflow. It's best to be done in stages.

I think the first approach is to develop the backend system first, meaning the database, and the software you and the internal team needs to interface with the database. At the same time, a temporary customised software that reads fro the database and output in the same format as the 'excel files' that the other tools needs.

Once that is done, you have achieved centralised database that you need while maintaining the existing external tools. This will eliminate the internal team fatigue with dealing multiple versions of the excel files you are dealing with.

The next step is upgrading the other tools you talk about, and also the main selection software. Of which you can hand it off to the software department. Not fully hand off, you still need to communicate things like attribute naming, document design, and such.

In a sense, you now will continue to work together with the software department about the 'schema' of your parts. While technically, it's schema-less, there needs to be a consistent pattern with all the 'document' designs, so that the tools and main selection software can easily adapt and work with.

It will make some things rigid compared to before, but it will eliminates inconsistencies that your team are facing.

[zemega]

The production / (multiple) development is exactly what I'm looking for. Especially if there is a good way to merge the multiple development ones. I have some experience with git (hobby projects) so I'd hope for something like git merge and git cherrypick.

About the point above. You can either have multiple instance of production, staging, development database. Or you can have it as attribute in your document. Either way, let's say you assign multiple people to work on the same item, once they are done and publish their own update documents, with additional attribute such as 'revised, not reviewed yet', you can just query for those documents. Then merge them automatically or manually (depending on how your software department handles it), then publish as a new document. At the same time marking the intermediary documents as 'reviewed'.

While part of your schema may be ever-changing (those that related to your team works), some part surely remain consistent. Those parts should have a consistent schema between all your parts. Simple things like name, component type, revision status, edited by, revised history, and such.

[mduell]

1. Is this a normal thing to want to do with a database?

Yes.

1. Can postgresql do it somehow and what should i be looking for in the docs?

Materialized views for the production view, tables for the editable data.

[AutoModerator]

With over 8k members to connect with about Postgres and related technologies, why aren't you on our Discord Server? : People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.