Questions tagged [database]
This tag is for general database questions. If you question is specific to SQL or NoSQL, use the corresponding tags instead.
2,099
questions
10
votes
5
answers
6k
views
Is it an anti-pattern to create ORM entities based on existing database schema?
I maintain a large legacy app working with SQL database. The app uses raw SQL queries to query the database. Together with app rewrite I plan to introduce ORM to work with the database.
The simplest ...
- 255
2
votes
3
answers
228
views
Dynamically transform EAV data into standard SQL table
I use MariaDB to store data using EAV model. I need EAV, because each attribute of each entity can have its own validity (validFrom, validTo).
For example, I have entity person and I track changes ...
- 255
0
votes
1
answer
55
views
Design a sequential processing of records
About 10 to 15 records are processed per day with the time interval of 5 minutes between each record.
System A inserts a record in DB and sends id of that record to active mq.
System B Listener ...
- 119
2
votes
2
answers
162
views
Pattern to transform and cache data from a DB?
We have a server that receives queries. We use the command pattern for this, e.g.
q := NewQuery(database)
q.Execute(request1)
q.Execute(request2)
The queries read a "model" from the ...
12
votes
4
answers
6k
views
Using three different database engines in the same application
Currently, I'm working on a graduation project about designing a Document Management System (DMS) that incorporates some functionalities as follows:
It won't use folders to organize documents; ...
- 129
0
votes
2
answers
127
views
API - What are some good strategies to limit hitting database on query-based searches?
I'm building an API that allows people to search for books (just for simplicity). There is an endpoint that takes a query string and returns the top 10 matched results. For example:
/api/v1/book/find?...
15
votes
4
answers
4k
views
Better solutions than joining table for Many to Many?
Lets say I have students and classes which are 2 entities. A student can take many classes and a class can have many students. This would be a many to many relationship.
To solve this with an RDBMS my ...
- 327
1
vote
2
answers
121
views
Handle data layer when using DDD
Since March I switched jobs and became the Tech Lead of two teams that work on a shared codebase and a separate codebase for "region" specific implementations.
This team works using NestJS, ...
- 33
1
vote
2
answers
196
views
Restricting access to sensitive data in monorepo
Imagine that you have a large monorepo code base running as a monolith application. This application is backed by a database. Some of the data in the database is sensitive, so you want to restrict ...
- 19
0
votes
2
answers
109
views
In data engineering, why is data integrity checked on the DW rather than on the data sources?
I'm a software developer and new to data engineering, so this may be a newbie question, but I'm wondering why data integrity checks (for instance, dbt tests) are ran on the data warehouse, rather than ...
- 243
7
votes
3
answers
1k
views
Prevent analytics from crashing the production database
At the core of my business (say, an online store) is a Postgres database that stores products and transactions. During day-to-day operations, the load on the database is not heavy.
However, there is ...
3
votes
1
answer
125
views
Should I use transactions in this scenario?
I am writing a API endpoint in NodeJs, The code is roughly like this:
function myApi(myUserId, userIdToDelete){
if ( checkIfIAmAdmin(myUserId) ) {
deleteUser(userIdToDelete);
}
}
Now,...
- 39
0
votes
1
answer
143
views
How can I trace back the original table of a column?
New to a software engeneer job and I am kinda blocked on the way to go with my new project
To explain this in a scheme.
I have a source of data in a custom app that is for most part of the time the ...
- 111
1
vote
2
answers
49
views
Table referential integrity varying according to application logic
I've come across a scenario where data in one table is related to another table but referential integrity doesn't seem to apply as I would usually expect. This is because table x uses data from ...
- 19
1
vote
0
answers
62
views
Designing an API adapter with multiple authentication types
I'm building an HTTP API database adapter that has an authentication component. Users can authenticate using password, federated login such as OAUTH, and JWT.
My initial design is something like the ...
- 19
4
votes
1
answer
217
views
Using a single Postgres server process to manage multiple databases in a microservice application
One of the main guidelines I keep seeing for successfully building a microservice application is to use a separate database for each microservice. In diagrams these are usually depicted as physically ...
- 223
0
votes
1
answer
195
views
How do you handle multiple database connections on an multitenancy applications
In a multitenancy application where you create a new postgres database for each tenant, what's the best way to handle database connections on runtime?
Do you open and close connection on every request ...
- 103
0
votes
1
answer
72
views
Creating Controllers and Repositories without corresponding Models
I have to create a system to interview people on a few topics where they have to answer both objective and discursive questions, where questions may change between interviews. I structured the ...
- 111
1
vote
3
answers
198
views
How do you test whether schema changes will break services?
I maintain a relational database of sorts, and occasionally a feature request requires a schema change (eg. add a new column, etc...).
However, a number of "services" consume this data. It's ...
- 343
0
votes
0
answers
74
views
Data modeling - strategy game - over-normalization?
I am trying to build a travian-like web browser game (static strategy game). I am currently working on my tables and data model. I don't have much experience with relational databases and I am ...
- 155
0
votes
1
answer
507
views
How do Repository and Data Mapper patterns work together?
I've seen a variety of discussions talking primarily about the question of the difference or not between the Repository and Data Mapper persistence system design patterns; but I think I have a decent ...
- 467
0
votes
4
answers
299
views
How to safely store application data on shutdown?
I am writing a Windows Service using the Windows Background Worker Service template. Some data that I track needs to persist between application instances (i.e. after shutdown and restart).
Initially, ...
- 1
2
votes
1
answer
149
views
two diffrent database in unit of work
I will explain my problem in the form of an example.
Suppose we want to use both databases in a transaction.
Data is edited in database 1 (for example, Postgres) and then added to database 2. Finally, ...
- 29
1
vote
2
answers
213
views
Using multiple databases in a bounded context
Sometimes we have to use multiple databases in one project
with the intentions of:
Denormalizing data or read models
Using the advantages of that database (technology)
There has been a lot of talk ...
- 137
1
vote
4
answers
409
views
Is it okay for API endpoints services to communicate directly with the database?
I've started work on a web application personal project that is more a learning opportunity more than anything and I'm brainstorming the stack. My number one priority is to not overcomplicate things ...
- 151
0
votes
2
answers
241
views
Keeping user provided passwords to 3rd party services safe
I have a SaaS application in which users can connect their RDBMS (postgres, mysql etc) and query data from it. I'm wondering what's the best practice to keep their connection details safe. Currently, ...
- 125
10
votes
3
answers
2k
views
How do distributed databases follow unique constraints?
Lets say i have an application where user can register, and the username has to be unqiue value.
Now lets say i have N partitions and for each partition i have M replicas with multiple leaders.
Now i ...
- 397
2
votes
1
answer
129
views
Encoding hierarchy information in objects
I am writing code that will assign entities to transaction. Each transaction has a counterparty entity.
Requirements
The entities should be (de-)serialisable (from pydantic models into JSON and vice ...
- 131
3
votes
2
answers
255
views
Best approach to microservice shared databse architecture
I have two microservices, one Flask (python) and one Spring (java), they currently share a database. The Flask microservice handles processing json files (~40mb) for each user (could be 100's or 1000'...
- 31
26
votes
8
answers
9k
views
Is it okay to hard-code table and column names in queries?
I've got a backend running on Node that executes queries on a PostgreSQL database. For these queries, table and column names are imported from a .env file, for example:
const ID = process.env.ID_COL;
...
- 371
1
vote
2
answers
77
views
Phantom read race condition keeping RDBMS in sync with external storage
Here's an interesting scenario, consider a cache with many buckets, and resources that can be shared between buckets:
Bucket
Highest to lowest priority
Foo
A, B, C, D
Bar
B, C, D
Baz
A
In the ...
- 111
1
vote
2
answers
84
views
Should a mobile app keep an 'outdated' version of the data and refresh it from database or always fetch from database?
I'm building a mobile, in which the user is able to create,modify and delete entries in a database. There are multiple screen where some of the entries are displayed: (here are some examples, not ...
0
votes
3
answers
99
views
How to avoid data corruption with dual parent/child foreign keys
Imagine the following:
Persons table: (Id, FirstName, LastName)
PersonEmails table: (Id, PersonId, Address) (to allow a person to have multiple emails)
Contacts table: (Id, PersonId, UnsubscribeAll) (...
- 155
0
votes
1
answer
61
views
Secondary indexes vs Using elastic search
When does it make sense to put data in elastic search vs creating secondary indexing on Primary datastore?
Elastic search with another primary store
Pros:
Primary datastore can be optimised for read ...
- 111
-2
votes
1
answer
196
views
Adding new column(s) to DB Table breaks existing queries [closed]
Dilemma: I am working in microservices (MS) architecture for a product with shared (PostgreSQL) DB between MSes and DB Views exposed as Data Access API between SW Components, written and maintained by ...
- 107
1
vote
1
answer
543
views
How can I design a data access layer that connects to two different databases?
I have a C# application that needs to connect to either an Oracle database or a SQLite database. The databases can be considered "identical" - same schema - but users have the ability to &...
- 113
0
votes
1
answer
2k
views
SQL or NoSQL Database for a chat application?
currently I am working on a kind of "Chat" Application.
The app consists of "threads". Each of this threads consist of "subthreads" in wich a user can send a message (...
- 55
3
votes
2
answers
245
views
Preventing POST requests from being lost when database connection is lost
I am working on a project to stand up a central database to replace a current massive file-sharing/syncing system of physical log files and text configurations for the primary application I work on.
...
0
votes
1
answer
72
views
How to design a database wherein multiple tags(string) are to be associated with an id per user?
I have a similar situation to the user here How to design a database wherein multiple tags(string) are to be associated with an id?
However I'm designing a system where each association (between ...
- 1,033
-1
votes
3
answers
269
views
Is it better to override methods in classes or make methods general?
I am creating the backend of a microservice that will serve as a tool to see in real time how the company's employees are distributed by projects and what days they have assigned to each one. The ...
- 1
2
votes
1
answer
235
views
What is the best way to design a heavy lookup web app?
I am facing a bit of a conundrum with how I can structure my application, as I try to balance speed, cost, and efficiency.
I currently have a website, algfinder.com, which solves states of rubik's ...
- 131
-1
votes
3
answers
289
views
Is it ok to save all fields of an entity as a string if it should not be changed?
I have a complex object that has several fields can be updated until it is locked.
Is it proper to save this locked entity as another entity which has no object child, only string data.
complex object
...
- 109
2
votes
2
answers
281
views
How to store queryable 10-100MB BLOBs?
I have read several discussions about storing BLOBs in the database vs in an object storage. What I need in addition though is a functionality for querying these BLOBs. The BLOBs will be immutable ...
- 129
0
votes
2
answers
144
views
Testing an assertion across all test cases
I have recently developed a small module that performs queries against a database. At the end of each test that contains code performing modifying queries against the database, I added an assert that ...
- 167
0
votes
4
answers
154
views
Microservice arcitecture - seperation of services
The Microservice arcitecture is still a new thing for me to work with, so it gives me some unanswered questions.
In my case each MicroService has its own database.
I need a UserService to handle CRUD ...
- 29
-2
votes
1
answer
302
views
Ho do I implement Database per Service pattern in Kubernetes?
Assuming I wanted to implement the Database per Service pattern or one of its related patterns like Private-Tables-, Schema- or Database-server-per-service (https://microservices.io/patterns/data/...
Domino
0
votes
2
answers
689
views
Is there a better way to trigger API calls from an on Prem SQL Server without using a job scheduler?
I'm not sure if this is the correct area to ask this question, quite honestly, I'm not sure how to phrase the question because I don't know if what I'm thinking is possible.
I'm trying to figure out a ...
- 1
1
vote
1
answer
184
views
What is an apporpriate design pattern when dealing with Pandas and databases?
We're dealing with a lot of "data analysis", basically different sorts of data mangling, aggregations and calculations using Pandas. Usually, the data is time series data.
All underlying ...
- 13
1
vote
3
answers
308
views
How is double-spending technically prevented in traditional banking? [closed]
In contrast to bitcoin:
How is double-spending technically prevented in traditional banking? Do database transactions have to conform to the ACID property in order to prevent double-spending?
- 129
-4
votes
1
answer
108
views
Why is that (all) database authentication is still done with usernames and passwords? [closed]
This is something I have been thinking about a lot recently - why do we still authenticate / connect to databases with usernames and passwords?
Hear me out.
Lets talk about production databases. I ...
- 93