Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
Join us in building a kind, collaborative learning community via our updated Code of Conduct.

The tag has no usage guidance.

1
vote
0answers
31 views

Giving search on customizable fields

I have some user defined custom fields corresponding to a ticket (A ticket is an entity which has subject, status and custom fields). I need to search tickets based on its custom field values. A ...
0
votes
0answers
19 views

Edit List attribute in JPA when null?

I am using EclipseLink as my JPA implementation and Postgresql as my DB. JPA: @Struct(name="label,.....") @Entity @Table(name="\"XYZ\"", schema="\"XYZ\"") @NamedQuery(name="...
0
votes
1answer
39 views

Partitioning table by aggregate id for efficient eventsourcing in RDBMS

My events are stored in MySQL and I'm reaching a point where I have tens of millions of rows and things are starting to get sluggish when pulling an aggregate I have read about "table partitioning" ...
1
vote
1answer
70 views

In the context of nested database relations (A->B->C), what are the considerations for having an additional “shortcut” foreign key (A->C)?

I have a normalized relational database. I often find I need traverse multiple tables to get the related object of a related object and so forth. So by that I mean let's say I have related tables A->...
0
votes
2answers
86 views

Generate UUID in Application or Database level?

I created a new application and I am thinking where is the best place to generate a UUID. Generate a UUID in application level and persist it Generate a UUID in Database level I have a feeling that ...
0
votes
2answers
183 views

JSON in Database Schema Design

I'm working on a web application with constantly changing requirements. The relationships between entities in the database have mostly remained the same but the attributes of each entity type changes ...
0
votes
0answers
38 views

Strategies to migrate a SAP ADS database to postgres

I am looking for a good strategy to migrate an old and big SAP ADS (formerly sybase) database system to a modern RDBMS (most likely postgres). The first and foremost problem I am facing is, that the ...
1
vote
2answers
188 views

Ways to store objects, inherited from one superclass in a database

For my personal project I need to store 2D shapes in a Postgres database. For example Circle, Pentagon, Rectangle and so on. At first I've done it like so: all shapes are inherited from an abstract ...
14
votes
6answers
3k views

What are the best practices around retiring obsolete database columns? [closed]

I'm designing an application which will in an early stage collect data A, B, and C from clients, but later on will instead collect data A, B, and D. A, B, C, and D are very related and right now ...
1
vote
1answer
68 views

Database Schema design of server running status log

I am working with 4 AWS EC2 instances (servers). Each Instance has Instance ID and Instance Name. Instance ID is unique. Each of them having multiple application servers, like on of them is running ...
1
vote
2answers
62 views

unique varchar(NNN) with an index in database and its overhead

I've seen the url like this domain.com/some-article-title-which-can-be-very-long many times. There's no ID embedded in it. Therefore, in a db it must be defined as "unique article_url varchar(NNN)" ...
1
vote
2answers
52 views

Proper way to track ranking evolution of some db records

I will have a database of products where each record will have a number associated with it, let's say, the number of "sales". I will periodically update the database so each "sales" field for each ...
1
vote
3answers
177 views

Django web app: how to save thousands of variables per user

There is a website driven by Django where the user logs in and can answer thousands of questions. I want to persistently save whether a given user has answered a given question or not. My approach so ...
0
votes
1answer
95 views

Can a database wrapper be implemented in Scala using Traits? Is there a better way?

I've decided to implement a database wrapper to provide data operations of a web service using postgresql dbms. Models in my database are somelike complex and frameworks like slick do provide more ...
0
votes
0answers
369 views

Building up a JSON object in a stored procedure

My apologies if this isn't the right forum to ask this question. If it is, please let me know and I'll move it over to stack exchange. After discovering an unfortunate limitation of our PostgreSQL ...
1
vote
0answers
1k views

Using import.sql vs persisting JPA entities

In Hibernate ORM & Spring Boot, during the SessionFactory creation, SQL DML statements can be executed to create tables and/or insert data via files such as import.sql defined in the javax....
0
votes
1answer
199 views

Normalisation Vs Join , trade-off

In RDBMS, goals of Normalization: Free the database of modification anomalies Minimize re-design when extending Avoid bias toward any particular access pattern First step involves avoiding ...
0
votes
1answer
86 views

Automatically push content to CDN

Here's the thing: we have a website which has some i18n. Translations are maintained in DB and manually exported into JSON files which are consumed by our SPA one time on load. Now I have the ...
0
votes
1answer
129 views

Is it possible to store and query combinations of boolean-like information in an integer?

With one boolean, it is simply 0 and 1, and it's straight-forward to query. With two booleans, it becomes necessary to specify what each number means, ex: 0 F-F 1 F-T 2 T-F 3 T-T Here it's easy to ...
0
votes
0answers
44 views

PostgreSQL tables or JSONB for permissions

I'm currently in the process of adding permissions functionality to my app. I'm trying to decide which route to take between implementing this functionality with JSONB or tables. As a side note the ...
1
vote
1answer
159 views

Scaling resolution of time series data

I built an app that display time series data. The display is very similar to a yahoo finance chart (ie https://finance.yahoo.com/quote/AMZN/). I am using Postgres to store the data because that was ...
-1
votes
2answers
1k views

How to store product and pricing history in a product database?

What is the best way to store historical information about products and pricing in a database? Our suppliers provide us with catalogues in various formats that usually contain manufacturer's ID of ...
3
votes
3answers
534 views

Role based Database Schema

I am designing a database for a document management app for my office. I want to get the backend right so I don’t have issues in the future. Basically all users will be categorized into roles e.g, ...
0
votes
1answer
42 views

design implementation for a postgres database in a not connected environment

I have several postgres DB, on distant sites, two in China, 1 in India , one in Korea, one in Germany, one in France and one in Mexico. All dbs, regardless of their site, have one table that is part ...
2
votes
1answer
71 views

Apply math operations over column of table

This question is about finding approaches to solve a specific problem. I have a table which has several metrics with values. [metric1] [metric2] [metric3] [metric...N] Those columns are fixed. I ...
0
votes
2answers
138 views

Compile analytics from millions of rows in PostgreSQL

How do we compile analytics from millions of rows in a PostgreSQL table? We pull order data from multiple CRM's and need to compile the data for reporting and each CRM has it's own orders table. We ...
0
votes
1answer
906 views

Database Architecture for SaaS application [closed]

I am designing a SaaS application where thousands of users will be using this application. This application will do lots of data crunching and analytics. I am considering creating a main database ...
7
votes
1answer
100 views

Database Model description using constants

I want to create a class with static constants which are used whenever a part of an application want to access the database in any way. This way I want to remove all magic numbers and string inside my ...
4
votes
3answers
270 views

What are the different approaches to filtering on aggregated data?

I have a feature I'm building that aggregates data across about a dozen or so tables. This data is aggregated from a heavily joined query. This page then has a lot of filtering, sorting, counting, and ...
0
votes
0answers
37 views

Should I implement an intermediate Profile table now, or wait until if/when I need it?

Traditionally the schema is very simple: User <- Post I have written the code for a Profile model to be an intermediate: User <- ProfileUser -> Profile <- Post When registering for the ...
0
votes
1answer
28 views

How should I balance memory with network latency on a paginated table?

A web application I'm working on has a tool that queries a database and returns up to several thousand rows. When considering pagination, at what point does it make more sense to get the entire result ...
4
votes
0answers
389 views

Specifying a bi-directional unique constraint on a join table in Postgres

Problem description I have a situation where I want to enforce that a relationship can only be defined once and I want to solve it on database (Postgres) level. The following situation I consider ...
-3
votes
1answer
158 views

Is SQL inefficient since it has to parse through everything?

As you know SQL doesn't have arrays. So if you make a table "article" and wish to have comments on your articles I presume you would make another table "comment". Inside "comment" table you have the ...
1
vote
1answer
68 views

How perform automated tests over code that changes a DBMS (throught DML or DDL)?

I'm written a software as an exercise to improve my skills and I ask my self about how to test code that changes a database. Let the code in my github: https://github.com/daniloanp/Ensaios/blob/...
0
votes
1answer
726 views

Most elegant way to execute a AWS lambda function with preloaded parameters on a specific time

I am looking for the following workflow: Add an entry to a table of my Postgres database with a end_date and state (with the following values: active or done) columns. Execute a lambda at the exact ...
2
votes
1answer
124 views

How to associate similar data with different tables in a database?

My application has users and ads. A user has a unique email. An ad belongs to a user (so, the ads table has a user_id FK, that is simple). Every ad can also have multiple emails and phone numbers ...
1
vote
0answers
34 views

Split long running work amongst multiple workers

I have a large number of rows in a database each containing a user specific token. For each token I need to maintain an open websocket connection. To scale this up I would like to run multiple ...
6
votes
3answers
2k views

Storing hours a merchant is open in postgres

In postgres, I need to store the hours a merchant is open each week. Essentially, the information you'd see posted on a sign in the merchant's door: Mondays: Closed Tuesday-Friday: 9am-1pm, 2pm-6pm ...
3
votes
3answers
1k views

Relational vs Graph Database for (initially) moderately-sized network

We're developing an application whose data domain (or at least 90% of it) can be modeled effectively using a relational database. We've been using PostgreSQL since the beginning and have had no ...
3
votes
1answer
624 views

Postgres roles for website users

I am setting up a PostgreSQL database with a very small number of tables for a simple website. I am looking at ways to make it as solid and auditable as possible. Is there any particular reason why ...
0
votes
1answer
132 views

Is it advisable to create a single API to access the database? [closed]

Got an organization that its core work is acquiring various sources of data, validating it and integrating it to existing mostly financial data sets and then availing that data to many different types ...
4
votes
1answer
850 views

Elasticsearch and relational database combination

Imagine I have a marketplace application - where users can search for products (we concentrate on clothes). Every product has an ID, name (text), description (text), price (numeric), size (numeric), ...
4
votes
2answers
238 views

Database constraints for a recursive folders structure

In an application, I have a recursive folder structure (like folders in OS X or Windows file system). Each folder can contain three kind of things: Other folders (hence the recursive structure) ...
1
vote
0answers
936 views

Django and the new Postgresql Array field

I recently got a chance to create a new Django app with the current stable version which exposes some Postgresql specific data types for models (docs) and got excited to have an Array. According the ...
2
votes
0answers
252 views

Global Accounts in Multi-Tenant Authorization System

Our system is fundamentally a multi-tenant setup, where each client maintains a separate database with its own data and users. The authentication system on login issues an authentication token which ...
1
vote
2answers
295 views

Compiling vs pre-built binaries on production machine?

I referred Compiling vs using pre-built binaries performance?. The post talks about performance and opportunities for optimization. My question is about reliability. I built both python and ...
7
votes
3answers
481 views

Do common relational database systems work 'out of the box'? [closed]

I'm quite new to software development and whenever I come across small projects which involve storing (relational) data I always ask myself if something like a micro-blog (or any other project with ...
7
votes
2answers
29k views

How to get real time notifications, when a database change (insert, update, delete) occurs?

I'm creating a dashboard which should monitor a database table. I have only database access (no application layer). The table is rather large (10 million rows), however not changing rapidly (100 ...
1
vote
0answers
567 views

how to import large amounts of data without locking sqlite database

Background Information I have a web app that provides the user with the usual CRUD functionality for "widgets" let's say. But I also have give them the option of creating widgets in bulk via ...
1
vote
2answers
73 views

SQL - for some attributes specific to different clients' users, how to handle schema?

I have clients, each of whom have an app with a bunch of users. Their user data could be pretty different, but there is also a lot of overlap. Ex: all their users have "gender" and "age" and plenty ...