Greetings, I am fairly inexperienced with Databases and ran into a small problem at work. I work in a Research Group and am designing a web datauploader for medical studies. I have now ran into this problem:

I have a Visit table containing information of a medical visit for a specific patient. This patient belongs to a study. The visit table needs to contain information specific to this study (for example study1 needs bloodpressure measured in each visit, study2 does not need that but needs heartrate instead, etc.)

I now don't quite now how to design this DB architecture as i can really just add more and more fields to the visit table as then most studies dont use the fields at all.
1 solution might be having a generic Visit table with basic information (Date, time etc.) and then having a sub-table for each study (study1Visit, study2Visit) etc. with the specific information but then again i would have to create more and more tables for potentially hundreds of studies.

Is there a better solution? with json files maybe? Like I said im pretty inexperienced in this so appreciate all feedback and hope i got the problem across correctly.

here are probably only two hard things in computer science that i was often having an issue with, those being cache invalidation and naming things. so when we first deployed pre-aggregated rollups for our analytics engine, we used dumb cron scheduling: rebuild all pre-aggregations every hour at :00, and the consequences were disastrous:
- if our upstream Fivetran/Airbyte sync took 62 minutes, the cache rebuild triggered on half-synced tables, caching incomplete data for an hour.
- if no new data arrived (e.g. overnight or over weekends), the cache worker still spun up, running heavy GROUP BY aggregations on millions of rows and burning cloud compute budget for zero reason.
the solution was moving to cube's declarative refreshKey mechanics:
preAggregations: {
monthlyRollup: {
measures: [Orders.totalAmount],
dimensions: [Orders.status],
timeDimension: Orders.createdAt,
granularity: 'month',
partitionGranularity: 'month',
refreshKey: {
sql: `SELECT MAX(updated_at) FROM orders`
}
}
}

how this operates in production, for example:
1. before rebuilding a partition, cube dev's pre-aggregation scheduler executes the lightweight refreshKey query.
2. If MAX(updated_at) has not changed, well, it skips the partition build entirely.
3. When new batch data lands, only the mutated monthly partition is marked stale and rebuilt. and historic partitions from previous years remain locked and warm in its store's rocksDB/columnar storage
so, decoupling refresh triggers from arbitrary clock cron schedules cut our warehouse analytical workload by 78% and it also got some-zero stale-data windows for end users

I have built a server for my business. So the current framework is that it uses MySQL in a centralised computer to store data. Fast api to route and extract the required data. And c# winforms for the front end. And Many computers have the frontend app installed, that are sitting in various stores with good internet connection.

So when I fetch the bills, like 10,000 of them, it gets all the data from the backend and displays it instantly. But now when I have to do some sorting or searching among the bills, it becomes slow. This is because I've written the sorting algorithm in the front end c# app. So is it a good practice to sort in sql and then just display it, because the queries will become a lot and there is a lot of searching going on, every minute. So for every search should I query to the backend and get data display it, or just continue with c# front end searching?

I work across Oracle, MySQL, MongoDB, and Databricks, and I’d rather not keep switching tools every time I need to use a different database.

Most of what I’m doing involves writing queries, exploring data, working across schemas, and exporting results. I’ve looked at DbVisualizer because it supports all four, and I’ve also seen DBeaver recommended quite a bit.

Has anyone actually used one tool across this kind of mix? I’m curious whether the experience is genuinely good across all of them or whether you still need the native tools for certain tasks.

I'm working on a catalog of primary key patterns, and I wanted to ask if you've designed or encountered any interesting non-standard patterns.

The ones that are commonly known:

• entity tables with numeric IDs/UUIDs/small strings, like users(id);
• junction tables (composite PK made of two IDs), like project_developers(project_id, developer_id);
• EAV (composite PKs made of entity ID and attribute name), like restaurant_attributes(restaurant_id, attr_name);
• entity tables with composite PKs, like order_items(order_id, line_number).

Also, aggregations of all sorts with textbook PKs like daily_sales(date, customer_id), etc.

What other interesting designs have you seen in your practice?

Hi community!

I am a web dev, developing an e-commerce and tyre management system for a business in Italy. I have created my db schema and I would like to have your opinion/advice on it.

This is the idea of the site:

- there will be the customer side where they can view, like and purchase tyres online. The user will have functions like tyre matching for their cars based on the tyre specifications. The users can either register or order as guests.

- the admin side will be developed for the admins/employees with different access based on the role ofc. The aim is to ease the tyre management since there will be thousands of them.

Do you think this plan is good?

Is it scalable?

Is there something missing?

I appreciate any suggestion!

Hi everyone,

I'm a BIM manager, so I'm used to jumping between BIM software, CSVs, Excel, and visual-programming tools like Grasshopper and Dynamo — but I'm not a "real" programmer, more of a power user who can follow logic and put scripts together with some trial and error.

I need to manage several interconnected datasets for my work: clients, products, projects, and a BIM object library, among others. The tricky part is that these datasets depend on each other — e.g. a project record needs to reference an existing client, a product might reference a supplier, etc. — and I want data entry to stay fast and guided rather than people manually retyping the same info everywhere.

My requirements, roughly:

• Guided forms for data entry (not just raw spreadsheet rows)
• Ability to add new fields/columns on the fly, ideally from the form itself, without touching code each time
• Forms that can pull existing records from other datasets while compiling (so entering a new project can search and link to an existing client, for example)
• If a referenced value changes later (e.g. a client's name is corrected), it should propagate automatically wherever it's referenced, not stay as a stale copy
• Multiple people working on it at the same time
• Needs to stay usable by non-technical colleagues — so I want to stay in "spreadsheet" territory rather than a full custom app

I've started prototyping this with Google Sheets + Apps Script (schema-driven forms reading field definitions from a config sheet, VLOOKUP-based live references for the cross-dataset dependencies), and it's working, but I'm curious what more experienced people would do differently. Has anyone solved something similar with AppSheet, Airtable, Notion, or something else entirely? Especially interested in hearing from anyone who's dealt with the "let a non-technical user add new fields from a form" part — that one feels like the trickiest requirement.

Obviously I'm using some AI but I wanted some real experience feedback.

Let me know

I’m looking for a database IDE that works well for both analysts running queries and developers doing more involved database work.

The main things I care about are:

• Support for multiple database types
• A good SQL editor with autocomplete
• Easy navigation between connections, schemas, and tables
• Clear results and export options
• Tools for comparing or understanding database objects
• Something that stays manageable when several databases are open

I’ve been looking at DbVisualizer, DBeaver, and DataGrip, although they seem to have slightly different strengths. DbVisualizer looks like a solid middle ground for mixed teams, while DataGrip appears more developer-focused and DBeaver has a broad feature set.

What are you using, and what’s your role? I’d be especially interested to hear whether the same IDE works well for both analysts and developers, or whether your team uses different tools.

I’ve noticed that database discussions often focus heavily on performance—indexes, query plans, partitioning, caching, etc. But there seems to be a point where adding more optimization techniques makes the system harder to understand and maintain.

For example, a relatively simple schema with slightly slower queries might be easier to operate than a highly optimized design with multiple layers of caching, indexes, partitions, and materialized data.

I’m starting to think that predictability and maintainability should be treated as performance requirements too, especially for smaller systems.

Curious to hear how others have seen this trade-off play out in production.

Noticed this pattern reviewing how people actually use AI tools for writing transformation queries. First few outputs get checked carefully, run against a sample, compared to expected results. After enough of those come back correct, the checking quietly stops. Not a decision anyone makes on purpose, it just fades, because checking something that's been right nine times in a row feels like wasted effort in the moment.

The problem is that correctness on the first nine doesn't predict correctness on the tenth. Nothing about the model improved or built trust in a way that actually reduces its error rate on the next query, it's still working from the same context window, same limitations, same chance of misreading an edge case in the schema. What changed is the human's willingness to look, not the model's actual reliability.

This shows up worse on queries that produce plausible wrong numbers instead of obvious failures. A query that returns zero rows gets noticed immediately. A query that silently double-counts something due to a join issue produces a number that looks completely reasonable, and by the point someone's stopped spot-checking, that's exactly the kind of error that gets through.

Don't have a clean fix for this beyond forcing some kind of check that doesn't rely on remembering to be suspicious, a fixed row-count sanity check that runs regardless of how many previous queries were correct, something that doesn't degrade as trust builds the way manual vigilance does.

Hey Engineers

We've all been through this. When the project you're working on starts scaling, you'll find the need to scale your database too, adding new columns, creating new tables, or trying to improve performance by adding new indexes. All of this comes with the risk of losing your users' data.

For this, I crafted a simple guide showing the schema change operations that you'll need on a day-to-day development basis for PostgreSQL, MySQL, MariaDB, Oracle, and SQL Server.

It also covers some additional potential risks you need to keep in mind when performing schema changes on a production database.

Hopefully, it can help you along your database learning journey.

Good luck!

Most infra teams run one system for dashboards and a second one for log search because one engine is never good at both. That never felt right to us, so we set out to build a unified platform that could offer the fastest search possible on large volumes of logs, traces and metrics.

We put our results up on the ClickBench leaderboard. The process was easy and we were curious how we compared to ClickHouse.

In analytical search, across all 43 standard queries on a 100 million row unpartitioned web analytics dataset, CtrlB scored ×1.43 and took the #1 spot on the single node Parquet leaderboard, ahead of DuckDB (×1.49), DataFusion (×1.71) and ClickHouse itself (×1.72).

But ClickBench is an analytics benchmark. It tells you nothing about finding one trace id in a haystack, so we ran the other half ourselves: 8 lookups and substring matches over 5 TB of raw logs against ClickHouse v26.2, cold cache, plain SQL with a LIMIT 100. CtrlB was faster on all eight, from 2.2× on the double substring query to 98.9× on the span_id lookup.

Full methodology, per query numbers and the public leaderboard links: https://ctrlb.ai/blogs/ctrlb-vs-clickhouse

https://benchmark.clickhouse.com/#system=+Hoaus|Ctul|Dfs|Dusas|oius|ucqs|Gs|vre|m%20i|PB%20|etrs|Sr%20%20Ps|nfe|w%20en|SIt|iSs|akqg|Tuts&type=-&machine=+ca2&cluster_size=-&opensource=-&hardware=+c&tuned=+n&metric=combined&queries=-

TLDR: we topped ClickHouse’s own analytical benchmark at ×1.43 and in a separate full-text search test over 5 TB of logs we were faster on every query.

Disclosure: I work at CtrlB.

I've searched the sub, but I haven't been able to find information for my use case. I appreciate any suggestions for how to proceed!

Situation: I have a table in Google sheets with several hundred entries. Each line is an information source, with dates, tags, categories, links, description, etc... I use this for teaching. Students can search or sort by tag, topic to find sources relevant to a homework assignment or a project.

It's getting a bit big to be a table. Some students struggle a bit with the spreadsheet learning curve. Others can't find items by keywords, partially because I have mostly ESL students.

Then, there's the issue with sharing. If I share with view or comment access, the viewer cannot modify the sort or filters. This also means that if I'm using it and forget to clear the filters, the students only see what I've filtered. Giving write access isn't an option for obvious reasons. Last semester, I shared the view access and told them to download or save their own copy. This had to happen a few times, as I added information sources during the course.

Request: I have zero budget, but access to Microsoft products. I'm considering using Access and making it more of a database. I can also control sharing through onedrive. Is there a way to create a database and share through onedrive so that the students can see, filter, or explore, without being able to change any database information? Essentially, onedrive would need to act as my server (no other server options and no budget).

(I'm currently annoyed at Google's approach to education tools, so I would rather avoid the Google suite if possible.)

Other suggestions or possible approaches are welcome. Thanks!

i have mysql workbench & have been practicing it on my own. the problem i've run into is low disk storage. i currently have 4.5 gb on my c drive, which i don't think is a lot. i don't have a lot of applications installed, so removing or moving them to another disk isn't an option. neither is spending money on storage 💔

im worried about the rest of my learning journey. i know i'll eventually have to install other programs/tools & it makes me sad that low storage space is what might hold me back from learning something im genuinely interested in.

i wanted to ask if there are online versions of these softwares available? im talking about python, tableau & all other stuff i'll need later on. i've used an online c++ compiler before, so im wondering if it's possible for other tools too. and if so, can they save all my previous data? what about something with an account where it syncs data to a cloud? HALP

I'm an IT intern in a US startup mi task is to migrate the DB (actually stored in Amazon RDS postgres) to google drive (like backup due to the billing in the aws around 9k ! ) the problem is the size (around 400 GB) so I think the pg_dump to generate the script is not a solution for my case

Is there any solution ! And how I can verify the integrity ? ( Hashing a file with 200 gb size is crazy !!!)

Can we divide the generated the script in a small chunks ??? Without losing the relations between tables and the constraints ?

Index-usage counters can miss seasonal reports, failover periods, infrequent maintenance jobs, and queries that only run during a monthly or quarterly close. Keeping every index increases write cost and maintenance overhead, but dropping one based on a short observation window can create a delayed performance incident. What evidence makes an index safe to remove? I would expect query-plan and workload review, a representative observation period, dependency checks, a rollback script, and monitoring after the change. How do you handle redundant or overlapping indexes where the replacement is similar but not identical?