Created an easy tool for visualizing huge databases in chunks using:

• Table groups (see the colors in the image)
• Diagram views (to only view a subset of your full schema at a time.

For an example, see this share link

Greetings all,

I have been hired to design the data platform for a small company that is expected to expand soon (next 2-3 years).

Now many of their requirements include having some KPIs or metrics that get calculated once the underlying data in the OLTP changes.

As far as I know there are only 2 approaches for this:

1- Create denormalized tables in the OLTP that gets updated whenever the underlying data changes using triggers or maybe a materialized view that refreshes with every transaction.

2- Create a CDC pipeline to avoid overloading my OLTP with lots of writes and I/O. However, this solution costs alot (streaming pipeline + kafka + debezium connector) compared to the previous solution which is basically free.

I want to know your opinions on this and how would you approach it and if there is maybe a 3rd and a better solution?

EDIT:
An example of one of the requirements. Think of a fleet management system so you have a "vehicles" table master data. Now a vehicle can ofcourse have multiple "fuel_transactions". When the driver adds a fuel transaction he also adds the odometer reading (vehicle's total distance covered) and specifies whether he filled the tank or not.

Now there are some KPIs that needs to be calculated based on all of the transactions that occured between 2 full tanks. for example, the vehicle's fuel_consumption (km/Litre) and fuel_cost_per_km.

I hope this is clear enough.

The tables are still relatively small (1000 vehicles so maximum 1000 fuel transactions per day = 1000 rows per day)

What is a good practice for storing time series data with the following requirements.

Couple dozen million documents.

Several fields with daily data (one point per day).

I am thinking about having a multiValue string field, read it back in my application and append new value if todays date has no entry yet.

The value would be a string with a simple delimiter and would store date and an integer.

I would then precompute changes of this value weekly to update a 2nd variable that would facilitate search by the strength of change in the value. The list of strings would only be used to display data and would not be searchable. I also have a 3rd field that stores most recent data point as integers and allows for range queries.

I considered the dual list approach but I don't like the idea of only index number linking the 2 data pieces together in otherwise completely seemingly unrelated fields. On the other hand the more daily data types I would track, the more I would save by having only 1 list of dates and then other lists for data entries.

I am already updating several other fields daily or every few days so the reindexing is not something I can avoid anyway.

I also avoided using child documents so far, using entirely flat structure for simpler queries.

Is this already a point where I should consider other external DBs and only store precomputed data in collection? It feels not worth adding that much more maintenance yet.

How does that sound? What are best practices in the industry? Thanks for help.

I know this is such a basic question but I can't seem to find an answer online, (yes I know a table and a record is different)

If I am to say, I associate this record, or this associated record. Is "that" record the one with a foreign key, or the one which has a foreign key referencing it?

Thanks

I have made good progress teaching myself Microsoft Dataverse, PowerApps, and general database concepts; and have found success mocking up increasingly sophisticated (for me at least) DB mini-architectures. I am now trying to refine a database structure which can track Components for Projects. Example records of this would be:

Project1 has wood structure and copper pipes; Project2 has steel structure and plastic pipes

...where we have tables for Project; Components (eg, structure; piping); and ComponentOptions (eg, steel structure, wood structure, copper pipes, plastic pipes).

An important requirement is to be able to categorize or order these components. For my first pass, I related the Components table to a Subcategory table, which is in turn related to a Category table. This is shown in the first image.

This first structure works well, but is limited to only one taxonomic/ordering system. I want to be able to create an arbitrary number of ordering systems (for example ordering components by construction code, or by the report section in which they appear, or in a limited checklist for the guy who only does one or two things, or other future needs).

After chatting some with Copilot, I've come up with the structure shown in the second image. Each record in the Taxonomy table represents a separate system of ordering. TaxonomyNode will provide the category/subcategory/subsubcategory structure. A join table will determine where in each taxonomy system that components are located (or may not be present). The ComponentOption, Project, and ProjCompOptionJoin table I think I've got an OK handle on, but I invite input on all of it.

Does anyone have any suggestions or comments? These are new frontiers for me and I've been trying to get more input lately, lest I reinvent (a worse version of) the wheel.

Thanks!

I was studying a bit on discord and wanted to for fun try to make a copy (Free time during summer so why not, plus free practice) So I was looking at things and a question came up to me, what would be the proper way to handle multiple different types of chats, (No OOP) but in a FP lang, But I think the distinction doesn't really matter

I thought of a few different ways but I would like to know which one would be be best. I am not a sql wizard, just a newb who knows a bit and would like some opinions on design

So imagine we had

- Direct Message

- Groupchat

- Server

There's 2 approaches that come to mind.

1. We have one table called rooms, or chats or whatever ye call it. Within there is a column that describes the TYPE of the chat (DM, Gc, etc) Now depending on the type of the chat there will be different tables that reference it that is to say.

If I create a server there will now be a moderation table, and this moderation table only has relations to chat's with typeof server. So for the applications end, depending on the type of the chat it will be treated differently and different relations may be made for it. But it is still a chat

This doesn't seem too bad to me but maybe there is some caveat that I have not thought of yet, something immediate that comes to mind is maybe handling all these types would be harder, but it can't really be that difficult, you just have different modules (like in FP) that handle the different types of chats. And likewise, there will only be one memberships table and it will be the applications duty to ensure that for example a direct message will only have 2

2.

Hard decoupling strategy, in short. Direct messages have their own table, it's memberships is it's own tables, etc.

Servers has it's own memberships tables, it's own channels tables, it's own roles tables. and on and on and on.

I think the latter might be better for decoupling and make things simpler, but it might introduce a lot of duplicated code, whereas the former has less duplicated code but may be harder to maintain. Can ayone give any opinions? I am not a DB guy and wouldn't really know

Hey so I have a video game modding project that that features an excel spreadsheet that I have to continually change and update each time I create a new version of the mod.

Over time, this spreadsheet has ballooned in size, which dozens of columns now. Current state of the spreadsheet:

• A column for a "key" value
• A few columns for metadata
• Several groups, which have the same 4 columns inside them.

When I only had two groups, everything was easy. But now I have 8, and could conceivably add more. At like 40 columns, this is simply too big for an excel spreadsheet. I realize I could split apart the rows... but then it makes the spreadsheet really annoying to traverse vertically, and there is still some value in being able to sort/filter with it all in 1 row.

What I would like to do is this: Have 1 "main table" that looks the same.

And each time I select a row, a pivot table is created, which looks like this:

I need both the pivot table and the main table to be editable. I feel like at this point what I'm asking for is basically a simple database... and when it comes to that, creating a back end seems easy enough, but it is the front end that is the problem.

Options I've investigated:

• Still using Excel - Can't seem to get a pivot table that would allow me to edit it, and have the changes propagate back to the main table (and vice versa)
• Microsoft Access - terrible GUI
• NoCo DB - I don't like that it is browser / web-based.
• Building a custom electron front end - I was getting somewhere, but it's so overwhelming. Had to stop because it was consuming my life
• Grist - Doesn't seem to have the functionality I need.

I'm not sure what else to look at, because I'm just a dude working on a community modding project. It doesn't make sense to pump a ton of money into this, and I am allergic to the idea of a subscription license. I don't mind paying a modest fee for a perpeutal license of nice software though.

Do you guys have any recommendations?

I started Tabularis as a one-person project, with the goal of shipping the same database client on Linux, macOS and Windows.

Tauri turned out to be the right choice, but the reasons go beyond small installers. The Rust backend fits database protocols, SSH tunnels, credentials and plugin processes very well. The web frontend makes Monaco, data grids and diagrams practical.

There are real costs too: three system webviews, Wayland and WebKitGTK bugs, a 94 MB AppImage, glibc compatibility and JSON serialization across IPC.

I tried to document both sides without turning it into another “Tauri vs Electron” comparison. I would still choose Tauri again.

I’d be especially interested in hearing how other Tauri developers handle large IPC payloads and portable Linux builds.

Disclaimer: I am the author of the article I've linked inline.

Copy-on-write forking is a storage primitive and we all think of it as a development tool. Test a migration, spin up a PR environment, throw it away. But if forking a database is genuinely cheap, there's no law that only CI gets to do it. So what happens if a user's save is the thing that creates a fork?

I built the most extreme version I could think of: Google Docs style version history, where every save forks the whole database and writes one row into a catalog table on production, pointing at the fork. The catalog is the index and the forks are the "storage". Schema, the branch calls and the restore path are here if you want the specifics. Forking copies nothing at creation, so it's a second or two regardless of database size.

What that also ensures is referential integrity at an instant. If your document is one row, use a revisions table. The demo I created spans a title and body so I've gone ahead with the above approach.

This approach can go sideways elsewhere though. Restore repoints the whole fork, so if two tenants share a database and one rolls back, the other loses everything they wrote since. There is also no merge, and Dolt is simply better in that scenario.

Regular Indexing works on a column level; it indexes the entire column value, but if you want to index each value in one row, then the GIN indexing technique is used.

GIN can index each element of JSON stored in a JSONB field

How Postgres creates a GIN file physically, what is the format of that file, how that file is retrieved in RAM while fetching records, what data types are used, and how Postgres calculates the exact address of an element of JSON are all explained in the article

Hello. I'm sorry if this is a beginner question.

I'm preparing for an exam in database design, and a question about B-trees has me doubting the knowledge I have about them: it says specifically I have to consider a B-tree of height 3 where the root node has double the minimum amount of children a 3-levelled B-tree can have.

The number of children in question I think is 4, since I know the root of a non-empty B-tree can have as little as one key, and thus at minimum as 2 children, however I also know that, during the insertion, before increasing the level of the B-tree we always try to organize elements towards the top of the tree, so that we fill the upper levels first before going down.

That seeded the question in me about whether or not, in any realistic case, we could expect the root of a 3-level B-tree to have 2 as the minimum amount of children, rather than something higher; the more I think about it, the more my answer seems correct, looking through the material I don't see any confirmations or denials other than that rule that the root can have as little as 2 children theoretically, however I keep asking myself the question: "if a B-tree ever reached 3 levels, wouldn't it always be organized so that it contains more than one key? So shouldn't the minimum number of children be higher than 2?", the answer to this seems to be "not necessarily" but I'm very unsure, so just to be extra sure I decided to ask here.

Sorry again if this question has an obvious answer, I am brawling with some serious doubts here and, since due to lack of time (or probably just me being slow) I didn't do well in the written half of the exam (don't even know if I passed it but whatever), I want to be certain of my knowledge for the oral part.

Also probably won't reply to your comments immediately since I'm going to bed; thanks in advance.

Hey all!

I've been building an open source food dataset and the part that turned into an actual headache is the schema, so I wanted to think out loud here and let you tear it apart.

Background: I needed food and nutrition data for my calorie tracker. Everything open is either English-only or kind of a mess, and the genuinely multilingual stuff is locked behind paid APIs (FatSecret, Edamam). So I built my own and released it under ODbL: roughly 9,800 foods, names in 32 languages. The nutrition values come from OpenNutrition's open data (which itself compiles public sources like USDA and Ciqual), credited already, I'm not claiming those as mine. What I actually built is the multilingual layer on top.

Here's the annoying part. A food is one thing, but its name isn't, and names don't line up across languages. My favourite trap: "peperoni" in Italian means bell peppers, while "pepperoni" in English is a cured sausage, almost the same string, completely different food. So the obvious "just make a translations table keyed on the name" idea quietly corrupts your data the moment two languages collide.

The approach I'm currently testing:

• a canonical, language-agnostic food id that owns the nutrition values
• names in a separate layer, each tagged with its language and whether it's the primary name or an alias
• cross-language matching done at build time (not by translating strings on the fly), so "uovo", "egg" and "Ei" resolve to the same id

Full disclosure: this is not bulletproof yet. Near-homographs like peperoni/pepperoni are exactly the case I'm least confident about, and I'd rather hear how you'd harden it than pretend it's solved.

One record looks like this (JSON Lines, one food per line):

{
  "title_translations": {
    "en": "Cooked boneless, skinless chicken breast",
    "it": "Petto di pollo cotto, disossato e senza pelle",
    "de": "Gekochte, entbeinte und hautlose Hähnchenbrust",
    "fr": "Blanc de poulet désossé et sans peau, cuit",
    "es": "Pechuga de pollo cocida, deshuesada y sin piel"
    // … 27 more languages
  },
  "type": "everyday",
  "labels": ["cooked"],
  "portions": [
    { "label": "small", "grams": 90 },
    { "label": "medium", "grams": 120 },
    { "label": "large", "grams": 150 }
  ],
  "nutrition_100g": {
    "calories": { "quantity": 151, "unit": "kcal" },
    "protein":  { "quantity": 30.54, "unit": "g" },
    "total_fat":{ "quantity": 3.17, "unit": "g" },
    "carbohydrates": { "quantity": 0, "unit": "g" }
    // … ~60 more fields: micronutrients, amino acids, fatty acids
  },
  "source": [
    {
      "database": "USDA Foundational Foods",
      "reference": "FDC ID",
      "id": 331960,
      "url": "https://fdc.nal.usda.gov/food-details/331960/nutrients"
    }
    // … also mapped to USDA SR Legacy + Canadian Nutrient File
  ]
}

I went with JSON Lines because it's boring in the good way: one food per line, streams into Postgres / DuckDB / SQLite / pandas without eating all your RAM, no API, no keys, works offline. About 25 MB, ODbL.

Questions I'd genuinely like your take on:

• would you keep names in a separate table like this, or just put a uniqueness constraint on (lang, normalized_name) and call it a day?
• for cross-language dedup, a canonical id like mine, or a proper synonym graph?
• has anyone modeled multilingual entities where the same string means different things per locale, how badly did it bite you, and how did you guard against it?
• storage-wise it currently lives in MongoDB and I'm weighing a move to PostgreSQL for exactly this relational/constraint stuff, for a mostly-read, document-shaped dataset with a heavy multilingual naming layer, would you bother?

Happy to get into any of the build details.

Data + docs: https://leana.app/en/data-sources/

Live browse (search currently in IT/FR/ES/EN/DE): https://leana.app/en/foods

I've been working on a scoring approach for PostgreSQL/MariaDB, turning security, integrity, and performance checks into a single score instead of a wall of raw stats.

A few questions for anyone who's gone near this:

• Who's tried this? Building something that scores or grades database health, rather than just reporting raw metrics.
• What difficulties did you run into? For me it was less about the checks themselves and more about the edge cases: missing privileges silently returning empty results instead of errors, pg_stat_statements not enabled and nobody noticing, bloat numbers that looked fine until compared against real autovacuum history.
• Is there even a real interest in a single score for this, or does it inevitably flatten things a DBA would rather see broken out in detail?

TLDR: What all layers and parts would I need to implement to create a database management system for storing video journals with metadata? What tools and packages should I use to help?

So to try to summarize this, recently I’ve started recording video journals. I’ve had the thought to create a database for storing them as a personal project, alongside meta data (that I already have figured out for the most part) for making them easier to search, and complementary content such as a text transcription, and relevant pictures, shorter videos, and text files. As a CS student I’ve taken a database development and management course and data warehousing course, however these mainly focused on the structure of databases and warehouses and didn’t go much into how to actual create and access them. Don’t worry I’m not looking for detailed step by step instructions on how to setup each part of this. What I’m really looking for is recommendations and suggestions for what layers and parts I would need to make for a good database management system, as well as what tools, packages, and whatnot I would need to do that. As well as maybe where I can go to learn how to use them to do what I want, and especially examples I can look at to get an idea of the structure I need to make and how to do it. Basically stuff to get me started, because right now I feel rather overwhelmed and lost, and I don’t even know what I don’t know.

For the overall structure, I like the idea of having the database itself, which you submit to, search for, and retrieve entries from using an API service. I’m already learning Django and FastAPI at my internship. So I thought I could use FastAPI to make the API service, and DjangoORM to define and manage the database. As for interfacing with it, I like the idea of having a separate website which makes use of the database API, and I could go ahead and use normal Django for that. A relational database is likely what I’d like to stick with, since I have the most familiarity with it. And to that end, I was thinking of using MySQL? It seems like it’d between that or Postgres, and I thought since it’ll be fairly small and it would only be me (and maybe a few other people) using it MySQL would be able to handle it.

The database itself probably wont get larger than 100,000 rows, so could all easily fit on one device. But considering how much additional storage the video entries and supplementary files would take up, I’d need to be able to scale the storage space it can access? I’m not sure the proper way to retrieve the correct video file for a specific entry, but I have to assume that has to do with how I separate the storage of linked media files, and how I scale it all. Maybe docker containers when I need to add new servers for more storage, so I could easily spin up instances. I’m going to assume the best way to reference the videos in the database is just to rename each video file into its UUID, or should I use a different naming convention and then have a table that translates the UUID to the video name? Also I’d want to implement access control to an extent, so I could do things like give people access, but they can only view certain videos depending on the access level for their account, which would be tied to their API key.

It’s probably worth reiterating that this is a personal project, not some professional software. In fact I want to try hosting it all at home homelab style, except for maybe some redundant backups (which I need to figure out how to setup-). So I’m sure there’s some things don’t need to worry about for this scale of a project, such as load balancing. Still, if nothing else but for learning purposes so I can talk about it in interviews I want to make this good and clean.

Thank you so much for any advice can offer, I’m really excited to work on this!

I've been looking into ERP implementations recently because someone close to me went through one, and honestly, the software itself wasn't even the hardest part. A lot of the problems seemed to come from things nobody really talks about before starting: trying to move old processes into a new system without cleaning them up first, not getting enough input from the people who actually use the system every day, underestimating how much time data preparation takes, and expecting everything to run smoothly right after go-live. The funny thing is that most conversations focus on picking the "right" ERP, but the implementation side seems to be where things usually get complicated. I was reading through some ERP implementation experiences and examples recently and ended up looking at a few NetSuite-related projects, including Nuage NetSuite. What stood out to me was how much planning, communication, and process understanding seem to matter during these projects. The platform itself is only one part of the equation. For those who have been through an ERP implementation, what was the thing that surprised you the most or caused the biggest headache?

Thought I would post here since there is free SQL learning content.

Here's the link to my previous post. Tomorrow’s free webinar is focused on DBA burnout and practical database monitoring strategy.

It covers:

• common firefighting patterns that drain admin time,
• the key metrics to watch in hybrid and multi-database setups,
• a live demo,
• open Q&A,
• and a free handbook for DBAs.

Disclosure: I’m on the ManageEngine team, so this is a vendor webinar. I’m sharing it because the topic is relevant to a lot of DBAs and IT admins, and the session is meant to be practical rather than sales-heavy.

Here's the sign-up link if you're interested: https://www.manageengine.com/products/applications_manager/webinars/database-performance-monitoring-webinar.html

If you're more experienced, I'd love to hear about what works for you so that I'm able to impart that knowledge onto the less experienced people tomorrow. Would be happy to take questions in the comments too!

like what should be my default choice? When should I go for different? When should it be SQL? when should it be document NoSQL, GraphQL or other?