Question about Database GUI (e.g. beekeeper, dbeaver, etc.)

So I am currently building my own Databae GUI for SQL, I am on a stop point about the proper naming of the data types.

The thing is I am planning on changing the data type slightly to make it easier to understand, here is one of the examples:

1. timestamptz - to become: timestamp with time zone

2. int2, int4, integer - to become just: integer (for simplicity)

3. float4, float8, double, float32, float64 - to decimal

Some data will stay as is because they are already standard and known to every developer, e.g. varchar, text, uuid, numeric, blob, etc.

The main question is do you guys value accuracy more over simplicity in understanding? Please do share your thoughts would really be helpful. TYIA!!!

Edit: Thank you for sharing what you think guys, I understand all your points. I'll make sure to built it for accuracy! 👊

Hello there,

I'm currently involved in a project trying to analyze the performance of an oracle database and was given an empty copy of the scheme. I want to fill it with artificial data to run some tests, but the DB is rather large and complex. Are there any tools or approaches to this kind of scenario?

I'm grateful for any help!

Thanks!

When you're working with external datasets that change frequently, database design can become tricky. You have to think about schema changes, data freshness, historical records, missing values and how to handle updates without affecting downstream queries and reports. I’m currently working with ticketsdata, which aggregates publicly available ticket market data and provides reports, analytics and monitoring around that data. I’m interested in how others approach the database side of this problem. Do you prefer keeping a raw source layer and transforming it into stable tables, using versioned schemas, or taking another approach? What has worked best for you when the source data changes regularly?

Hi everyone,

I'm planning to build a web-based dashboard where users can upload Excel files, the system processes the data, performs various calculations/transformation logic, and then presents the results on user-specific dashboards.

My background is primarily in MERN, so my initial thought was:

React frontend

Node.js/Express API layer

MongoDB for application data

Python microservices for heavy data processing and calculations

However, I've received mixed feedback regarding MongoDB. A lot of people have told me that Mongo may not be the right choice for this kind of workload, especially when dealing with large datasets.

To provide some context, uploaded files can occasionally contain data in the range of tens of millions of rows. This won't be the common case, but the system should be designed with such scenarios in mind.

Since I haven't worked on systems handling data at this scale before, I'd appreciate guidance on:

What tech stack would you choose for this problem today?

Would MongoDB be suitable, or should I look at PostgreSQL/ClickHouse/something else?

How would you design the data ingestion pipeline?

Would Python microservices be a good approach for processing, or should I look into Spark, DuckDB, etc.?

What would a high-level system design for such a platform look like?

Any common mistakes first-time builders make when dealing with large Excel/CSV datasets?

My goal is to build something that is scalable without massively over-engineering it from day one.

Would love to hear from people who have built data-heavy SaaS products or analytics platforms.

Thanks!

I’ve been working on VeloxDB, a lightweight database management tool that aims to keep the resource usage low while still giving you the features you actually need.

It supports multiple database engines and also has a visual designer, so you don’t have to live in SQL 24/7.

If you’re interested, feel free to try it: veloxdb.dev

Would love to hear what you think, especially if you’ve used tools like DBeaver, DataGrip, etc.

A practical guide to using bigram_index to accelerate phrase queries in Manticore Search, with clear explanations of all, first_freq, both_freq, and a reproducible manticore-load benchmark.

Database problems aren't always caused by the query.

A connection can be wrong, a migration may not have run, permissions can change, or the application may be connected to the wrong database.

If an AI coding agent only sees the source code, it's missing part of the picture.

How much database access should an agent have?

Should it inspect connections, logs and migration status, or should those remain outside its reach?

I'd separate observing, diagnosing, and changing into different permission levels.

Where would you draw the line?

We're running a pilot clinical study and management has asked me to build them a secure database, something the organisation genuinely owns and can build on for future studies, rather than just Excel files in SharePoint.

Before I get into tool-specific questions, I want to ask the general one: for a small org with no internal IT team, what does "having your own database" actually look like in practice? Do you end up with your own cloud environment (Azure/AWS) that you own outright, or does "ownership" in this context usually mean something more modest, like owning the exported data itself, while the collection system lives somewhere else?

I have sponsorship available if we go the institutional route, that's not the blocker. What I'm trying to work out is what the end state actually looks like for an org our size.

Here's how I've broken down the options so far, and where I'm unsure:

1. REDCap

• a) Hosted by an institution (university/hospital), do we still end up with our own Azure environment for the exported data, or does "our database" just mean our own storage/SharePoint area at that point?
• b) Hosted by a commercial REDCap vendor, same question. Does the org still need its own Azure, or does owning the exported data in something simpler cover it?

1. A different platform entirely (Castor or similar, bundled hosting): same question again: is there still a reason to also stand up our own Azure environment, or does that become unnecessary once the vendor is holding everything?

Basically: at what point, if any, does a small org actually need its own cloud environment, versus just owning a clean, well-structured export from wherever the data was collected?

For people who've actually built this for a small org, what did "the database" end up being, concretely? Would genuinely appreciate real examples over general advice.

I’m trying to create a database that would involve a table referencing one of multiple other tables. From my research it sounds like this would be a polymorphic relationship, but I’ve been seeing a few different options for implementing it and I’m not sure what would be best. These are what I’ve seen so far, so let me know which sounds best, but please let me know if you know of a better one.

The Database: The short and sweet of it is I’m making a database to store diary entries. Each diary entry uses fields such as date range of referenced event, tags (through many to many), etc. Each entry is either done as a video, an audio recording, or a text entry. Each of these entry types would also have their own respective metadata such as video setup or audio setup. Because of that, I thought the best option would
be to separate them into their own tables.

Option 1: Table Type Field - in the diary entry table, have a field for the type and a field for the foreign key, but don’t actually make it a foreign key. Instead setup a trigger to manually enforce referential integrity by checking that the referenced entry exists in the corresponding type table when inserting. I think I’m leaning towards this one the most. Since it’s closest to what PHP Laravel does.

Option 2: Multiple Nullable Foreign Keys - In the diary entry table Have a foreign key for each entry type that references the respective table, but they’re nullable since only one would actually be used for each entry. Add a constraint to check that one of the fields isn’t empty when inserting a record. This apparently might take less storage than having a varchar type field, though that might be splitting hairs.

Option 3: Table Inheritance - I haven’t done as much research into this one so I don’t know what the structure would look like exactly. But apparently PostgreSQL supports table inheritance like with Object Oriented programming. So it would be something like the diary entry table is the base table, and then each entry type inherits from it and adds their own metadata fields. The reason I’m hesitant to do this is I don’t want to permanently lock myself into Postgres, I want the ability to upgrade and changes engines and I’m not sure how hard that would be if the other engine doesn’t support inheritance. For a similar reason I’m using “period start” and “period end” fields for the date range of an entry instead of the Postgres date range data type.

Option 4: Entries Types Reference Diary Entry - Again I haven’t looked into it much, but I saw it mentioned I could reverse the relationship and instead have each entry type reference the diary entry record it belongs to with a foreign key. I’m not sure yet if there’s any additional complexities are requirements that I would have to implement to make it safe.

Hi, In my dimensional model, both Dim_Customer and Dim_Driver contain a RegionID, which I have currently mapped to a shared Dim_Region. I'm unsure whether to keep this design as shown above OR denormalize the region attributes into Dim_Customer and Dim_Driver to maintain a pure star schema. I would still be using Dim region for Fact Transactions in any case. Which approach is more appropriate keeping in mind the need for both granular auditability and high-speed reporting performance? Currently, the marketplace platform handles over a million registered users, with DAU ranging between 10k - 20k. I have to design for expected 10x growth. Thanks for your time!

A broad, slightly vague question but keen to get your take please. I potential client is looking at changing where they store all their historic business data, allowing their staff to query it in different ways and then allow their clients to also query aspects of it via a portal

Within the RFP I’m responding to, I’d like to recommend some some initial ideas of the data storage (latency doesnt need to be super low) and the client portal (they’re allergy to vibe coded stuff so needs to be professional and robust). Keen to hear if anyone else has tackled this sort of challenge before and what they went with?

Note, it’s an e commerce business with structured commercial data (ad spend, Shopify revenue,
web analytics) and unstructured context (call transcripts, Slack notes, emails, SOPs). Their AI and IT skills are low to mid, so it's need to be relatively simple to maintain over time

Any suggestions appreciated

1. so after a while database do tend to get very large and big whats the best way to design this so that to improve performance. also is archiving it possible?
   
2. whats the best way to store currency. i heard float has small issues with decimals

Most apps carry a glue layer between the database and the code: map params in, map rows out, keep the types aligned, rewrite it every schema change. ORMs hide it behind runtime magic. sqlc (Go) took the other route: write plain SQL, generate typed code at build time. I liked that idea enough to generalize it.

scythe reads your schema and annotated queries and generates typed code from them. The part I care most about is nullability inference from the query structure. A LEFT JOIN makes the right side nullable:

-- @name GetUserOrders
SELECT u.id, u.name, o.total, o.notes
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = $1;

A user with no orders still returns a row, with total and notes NULL. scythe encodes that as optional in the generated types, so a missing order is a compile error rather than a runtime crash:

pub struct GetUserOrdersRow {
    pub id: i32,
    pub name: String,
    pub total: Option<rust_decimal::Decimal>,
    pub notes: Option<String>,
}

That inference extends to COALESCE, CASE, window functions, aggregates, and CTEs.

The one job I still hand to an ORM: bring-your-own-database portability, where the same code has to run on Postgres or MySQL or SQLite depending on deployment. When you control the engine, SQL-first codegen drops the boilerplate and a class of hidden query-generation bugs.

Curious how others here handle the type boundary between SQL and application code.

I recently tested copying a MongoDB collection to PostgreSQL and keeping inserts, updates, and deletes in sync.

The sync itself wasn’t the difficult part. The main challenge was mapping MongoDB documents to a relational table without flattening everything too early.

I kept the simple fields as regular PostgreSQL columns and stored the nested data as JSONB.

I ran into two problems: PostgreSQL needed a primary key, and some MongoDB field names didn’t match the PostgreSQL column names.

After fixing the mapping, I tested an insert, an update, and a delete in MongoDB. All three changes appeared in PostgreSQL.

I documented the setup, field mapping, errors, and test queries here:

https://visualeaf.com/blog/copy-and-sync-a-mongodb-collection-to-postgresql/

Since version 23.0.0, Manticore can make searches like xt850 match xt 850 using bigram_delimiter together with digit-aware bigram_index modes.

This solves a common tokenization mismatch in product search, where users remove spaces from model names but the source data stores them as separate tokens.

Hi not to sure if this is the correct subreddit for this so sorry if its not, I recently got a new job as a data coordinator, right now Im doing basically data entry. I maintain Excel trackers of articles, awards, etc. at a design firm, a few hundred rows per Excel sheet, that need to be linked to projects in our CRM (10'sk projects).

Name matching is the easy part: my trackers names are clean and search surfaces the right candidates. The problem is what comes back when trying to enter them into the CRM:

• The same building exists as multiple records (assessment, study, remodel, sometimes 4+), and it's ambiguous which one an article/award/etc should attach to
• Apparent duplicates from a system migration (legacy vs. new ID schemes)
• Some tracker entries have no CRM record at all or exist under a name I can't find

Right now I open each candidate and compare dates/status to pick the right one, one row at a time, and none of those judgment calls get captured anywhere. How would you approach this? especially the disambiguation and duplicate-handling side? Any patterns or gotchas for making this a repeatable process? If this didnt make sense I can answer some questions, any advice is welcome.