Questions tagged [database]

This tag is for general database questions; if you question is specific to SQL use that tag instead.

Filter by
Sorted by
Tagged with
0
votes
0answers
16 views

Multitenant setup with master account

I would like to make a SaaS which would be a multitenant system. Development is done in Laravel, which is less important. What I would like to know is how would you handle having a master admin ...
0
votes
1answer
78 views

Is my database schema going to work? Tracking git issues

My boss wants an automated weekly report on how many git issues have been moved to and from various "lanes" (pipelines) on a virtual kanban board. I wrote a quick python script to get that data using ...
0
votes
0answers
44 views

Effective database design for time card tracking

I'm building a timecard system for a company of ~50 employees. My problem is designing an efficient way of storing this data. Currently, their timecards are done on a table like the one shown below. ...
-1
votes
1answer
51 views

What is the best design for this use case regarding bidirectional relationship? [closed]

For this question let's consider a system in which the users can match with each other (let's say for simplicity exactly like the dating apps). User1 matches User2 (and the other way around) only in ...
1
vote
1answer
75 views

Architecture/algorithm for unusual recommendation system

There are thousands (or tens of thousands) of possible movie titles. New user enters my website and selects hundreds of titles that he likes. The only single goal of my website is to output the list ...
0
votes
1answer
29 views

Update items based on a read operations on same items is a bad practice?

DynamoDB is the database solution, I'm using to store my items. It's a NoSQL/Document database and I got a real problem on real-time updates of items. First, the saved format is like { expiredTime:...
0
votes
2answers
147 views

Is it a software engineer's responsibility to structure data as opposed to a DBA?

As a backend engineer at a small company, we're running into a question of whether we need a DBA or another backend engineer (SWE2). The reason why this is a question stems from a difficulty in ...
0
votes
1answer
33 views

Strategies for Rotating MYSQL Encryption Keys

I have a web app that connects to a MYSQL database which has some encrypted data in it. The encrypted data is encrypted using the the mysql AES_ENCRYPT function and a key. Now, this key has been ...
-1
votes
1answer
101 views

I have a data set of over a million addresses and I want to display the closest N locations to a given address or current location

I am a student working on a personal project which is essentially a location finder that will be on a website. I have a data set of over a million addresses and I want to display the closest N ...
-1
votes
2answers
113 views

When self-referencing in a table, do you prefer `parent_id = null` or `parent_id = id` for a row referencing to itself?

For the discussion, I will provide my example. But I am interested in the broad guidelines. I save in my postgres DB legislation (law texts) composed of a single header (H), paragraphs (P), sub-...
0
votes
1answer
51 views

Data backend for a 'Nearby' places application

Context I am building an application for searching places 'Nearby' akin to Yelp/Google. Objective for this question Recommend a strategy to do paginated searches based on location and ask for ...
0
votes
1answer
115 views

Database Design Structure

I am building a WebApp which has a list of Assets. I have a peculiar requirement and I am unsure of the best way to store the data in SQL. Requirement: My Asset Headers currently have a relation to ...
0
votes
1answer
37 views

Bridge/Join Table for a Many to Many Relationship between One Table and Multiple Others

Background I have a relational database an I want to have a table that contains flags. I want multiple tables in my database to have a many to many relationship with the flag table. Options I ...
0
votes
1answer
174 views

Is it good practice to create a static class for database tables calculations?

I need to implement some system (using python pandas dataframe in my case) that parses raw data, then adds on calculated data, and then validates that calculated data (boolean output on some columns) ...
0
votes
0answers
18 views

Updating an entity and many relationships: database vs application layer

We are working on a transformation project It has records in table entityA and entityB, which has a relation entityAB. We need to delete records in table entityA and recreate them with some other ...
0
votes
1answer
51 views

How to store data which can be a foreign key or free text

I am not sure how to explain my problem, but basically I have an input field which lets you enter member's name or select one from a dropdown list. If the user has selected the person from the list ...
0
votes
1answer
72 views

Deciding which microservice should write to the database

Let's assume the following architecture where EventCreatorApp creates an Event which is logged to DB and sent to WorkerApp for processing WorkerApp processes the event, handles failures, etc. ...
2
votes
1answer
71 views

Can I reduce locking for this scenario using Hibernate and H2

My Java application uses Hibernate and H2. It process music files within folders. Typically for a folder it would read the files in the folder and store each file as a Song class in database, then ...
0
votes
1answer
40 views

Temporary users to restrict db access of query?

I am new to database managment, and I am wondering if it is a good idea to create temporary users to restrict access to only a single table. Like having an user named _logs can only (read?) and ...
1
vote
1answer
95 views

Multiple applications with single database for IoT

I'm using a raspberry pi 2B with raspbian lite. I have made a program that communicates with a LoRa module (RFM95, sx1276 chip) and collects data from a sensor. The data it collects, are stored in a ...
2
votes
1answer
32 views

Setup pipeline to analyze data stored in web app DB

Background: So there is a (Ruby) web app with a production Postgres DB (hosted in the cloud) I would like to run some machine learning algorithms in a Python setting on the production data and (...
4
votes
2answers
216 views

How to test database dependent functionalities?

I have written this function which checks if a user already exists in the application: async function ValidateUserExists(username, email){ if(!username || !email) throw new Error('Invalid number ...
0
votes
1answer
83 views

ER Diagram in Barker Notation

I engineered an ERD diagram using Barker's notation. The problem was to make a database for system of water pipes where one node is connected to another using a section (pipe), each node can have ...
4
votes
2answers
111 views

Database structure for word co-occurrence frequencies in a large corpus

I would like to store the frequencies with which words co-occur with each other over a variety of contexts in a large (> 1 billion tokens) text corpus. I need to store the word pair, the type of co-...
2
votes
1answer
69 views

Allow client side direct access to the database [duplicate]

I am asking advice from the SQL graybeards. Because my following proposition goes against everything I have practiced. In this instance Let's assume we are developing a phone app. A social network ...
0
votes
0answers
27 views

ACL entity and microservices database relations

I would like to build a microservices architecture to handle the following scenario: I have a building. The building has some kinds of devices. And the devices can be used by the users. In the system, ...
1
vote
0answers
116 views

How to store relational data belonging to different client customers?

My users will be businesses with a small number of accounts each: e.g. Business #1 with 3 users, Business #2 with 5 users, etc. I am trying to determine the best way to organise the relation (on ...
1
vote
8answers
656 views

Is there a way to prevent developers leaking data while they are developing?

I am working with a startup company for a while now. They have been affected by some reckless developers in the past. One area they want a real and immediate improvement is when they assign a task to ...
1
vote
1answer
77 views

About databases in docker containers

I'm trying to understand containerized apps and databases and I'm trying to understand the microservice architecture using kubernetes. One thing that I couldn't get my mind convinced is the database ...
1
vote
1answer
81 views

Approach to handle network breakdowns in application dealing with database?

I have an application that writes data to database. In case of network breakdowns, the connection to database gets lost & my db queries freezes the whole application. So, in order to make it ...
2
votes
1answer
150 views

Unit conversion of data

My team is developing a web application, where we have in the requirements to display the data in multiple unit which is set by the end user. Where should we write down the unit conversion logic, on ...
0
votes
1answer
41 views

How do database storage engines handle new columns?

I really don't know which StackExchange site this belongs on. It didn't seem to match the "on-topic" list of DBA.SE or SO.SE ... so please let me know if I should move it. I also couldn't find a ...
0
votes
1answer
147 views

When is the point where a JSON text file storage is not enough?

I guess it was Robert C. Martin who campaigned against using SQL in one of his apps, where he was just fine without DB and SQL initially. Later, thanks to the abstraction a DB implementation could be ...
0
votes
0answers
27 views

Object Data must be built on SQL side or in Code Side?

I am thinking of a very flexible application that will build a single entity object from a lot of tables joins. example for an object "Car" with a code something like this SELECT e.id, a1.attr_value ...
2
votes
1answer
125 views

Who (and what) should have direct access to a database, and why?

I am developing a chat application as my the first project. I have never had experience of establishing a connection to the remote database server before so I don't really understand who should have ...
1
vote
1answer
81 views

Does a microservice's individual database need to have complete data?

Say you have a Users and Orders service and they have their own database. Since microservices need to stand on their own even if another service is unavailable, does the Orders DB need to have all ...
1
vote
1answer
48 views

Database structure for an app used to capture data in warehouses

I am in the process of creating an android app for our clients that would replace the paper-trail in their facilities with electronic inspections. Although I have coding experience, I have little ...
0
votes
1answer
71 views

Database schema for products and their packages

The product is water purifier, and we are selling packages included support for changing filters. Serial is a unique number identifying a single produced item and it's only for water purifiers not ...
2
votes
1answer
242 views

How to handle database connection password?

I have a question about using databases in my application. I have the User table which stores passwords for my users. I know how to handle user authentication, but how to handle the password for the ...
0
votes
0answers
26 views

How to store message content in a webservice

I am building a service responsible for sending email/sms communications. The service receives a request that contains information necessary to build an email/sms template and the recipient ...
0
votes
4answers
121 views

Could you please recommend an approach on converting non-unique field to a unique field?

On our prod db we have two tables: companies invoices Relationship: A company has many invoices An invoice belong to a company The invoices table contains duplicate invoice_number for each company. ...
1
vote
2answers
140 views

What is the best model to catch DB side error?

We have a web application that all update, insert and DB operation procedure handle via SQL Server DB 2017. What is the best way to handle and get error (in Web-app or SQL server error handler?). for ...
0
votes
0answers
47 views

Designing database relationships for a voting application

So I designed database relationships for a voting/polling application. I want a user to only be able to pick 1 option on a poll. I want multiple options per poll. So what I have here is this. User ...
0
votes
1answer
62 views

Multiple stored procedures or 1 with parameter

I need some advice... I started working at a financial business world recently. Looks like the DB architect uses auto generate function to create stored procs. At the end, we have way too many procs ...
-2
votes
1answer
229 views

Mock db out or not during testing?

I am writing an app that mainly consists of just creating, editing and reading records in database. I was persuaded that any app has to be covered with tests. I was told that it's a good practice to ...
15
votes
4answers
2k views

Why not use SQL instead of GraphQL?

Recently I learned about GraphQL which claims to be superior to RESTful. However, I started wondering why don't we simply put SQL statements into an HTTP GET request. For example, in GraphQL I would ...
-2
votes
1answer
75 views

Generate HTTP RESTful server from database schema

Today the job of most Java-based servers is to translate HTTP requests into SQL commands/queries and send a JSON-ed response back to the client. That's a pretty generic (and honestly boring) job. I ...
6
votes
4answers
597 views

Am I right in writing our own database migration/versioning software?

I have started work on writing a tool to version the database schemas we use at work and enable migrations under version control. My first research into existing tools turned out to be too cursory due ...
1
vote
2answers
141 views

Small databases online

This is to ask whether accdb databases are suitable for this application, and how to connect the user with the database (and the phone network with the database if anyone has a comment on this). I ...
0
votes
0answers
52 views

How to design database tables for complex build of materials as product models?

I need to redesign a 10 year old Product Management Portal and underlying Data-model. There are build of materials as products in the system. Product managers update pricing frequently for products. ...