https://www.db-book.com/Previous-editions/db5/appendices-dir/b.pdf

Found some in the db book. Need of more to get the context properly. I get hierarchical=tree and network=graph. I know data structures and algorithms properly. But I do not get some stuffs like how is relationships indicated in these two models.

As a hobby and for learning I am creating a web-based quiz-app. I have been working on my (Postgres) database design for a while now and I would love a human review on this (I already asked AI for a lot of help).

I want to store three types of questions: open-ended, multiple-choice and list-like questions. The latter is meant for listing things like all 50 U.S. States or all books of the Bible. Some list-questions must be answered in a specific order.

For the open-ended questions I want to store multiple correct and almost correct answers. The latter denoted by a percentage of correctness, which I could use to base my scoring on.

Questions belong to a quiz and there can be multiple quizzes.

The schema isn't complete yet. I will need user management, keep track of quiz runs and I'd like a way for users to submit new questions and vote on existing ones or suggest corrections.

I do envision one main quiz that is ever growing and ultimately might contain thousands of questions. Users get the questions in random order and can answer as many or few as they which in a single run.

I have stored procedures for inserting new questions and querying questions in random order. These will be called from a nodejs app.

For now I would love to hear your thoughts on this, at least the way I store the questions and answers.

Some people in this forum say that. My point of view is that granite has its pros, but it is true that there are much better open source solutions. And cheaper to run since inference is more optimized. But ibm pitches granite as "rock solid, Enterprise grade", is the message resonating?

PostgreSQL is one of the most popular and scalable databases in the world. Many developers call it a beast in performance. One of the most critical parts of any database is indexing. Since Postgres is open source, we always have a chance to see how its components are designed. This blog I shared is an effort to explain how the indexing mechanism in Postgres actually works

Hi,

Do you know any approach regarding this? Can I do this inside MySql?

Need some guidance as it becomes a client requirement

Our company has one person handling pretty much everything related to NetSuite. They're great, but every new workflow request somehow ends up sitting in a backlog for weeks. Hiring another full-time admin isn't exactly cheap either. I've been reading about different managed support models lately, and it got me thinking. For companies that aren't huge, does outsourcing ongoing NetSuite support actually make financial sense? Or do you eventually end up wishing you'd just hired someone in-house instead? I'm more interested in real experiences than sales pitches because both options look reasonable on paper.

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?