24,210 questions
-1
votes
2
answers
56
views
how to have a calculated field viewable within a form in real time?
How do I have a database field that provides a calculation in real time, i.e when the user is entering data into the form?
I tried to run an afterUpdate event from the field in the form labeled ...
- 11
-1
votes
1
answer
66
views
Storing Database Password
If I save the password to the database as a hash in the configuration file of the application or in the code for security reasons, how does the application connect to the database if it does not ...
- 1
0
votes
1
answer
57
views
Maintain the current count of buildings in a different table
I have two tables:
Table apartments:
house_no
house_street
1
Pomona
2
Pomona
1
Dubai
2
Dubai
Table streets:
street_name
total_buildings
Dubai
NULL
Pomona
NULL
I would love the column streets....
1
vote
2
answers
429
views
Store number of likes of a post in MySQL
I am creating an application which will enable users to create posts and also like posts. I am using MySQL to store all the data.
I have the tables users and posts. I want to prevent the situation ...
0
votes
1
answer
177
views
Many-to-many relationship database design [duplicate]
I am setting up new MySQL database schema for a small company. We sell products which are collections of raw units of inventory. In the database, I would like to link products with their raw inventory ...
0
votes
1
answer
89
views
Issue with Displaying Tenant Statistics in Citus Multi-Tenant Database
I am experiencing an issue with my Citus multi-tenant database setup, where I am unable to retrieve expected tenant statistics. I am currently running Citus 12.1-1 on an Ubuntu server with a master ...
0
votes
1
answer
62
views
Relational Database - How do you design SQL many to many relationship
What's the correct way to do (what I think is) a many to many association in a relational database?
Consider something such as (I'm using sqllite to play around):
# Store details about each teacher...
...
- 237
-1
votes
1
answer
121
views
Is it really better to respect the normal forms and create an additional table for one simple field? [closed]
We organize runs, and we want to save run results and the number of kilometers that runners did run in their career. We have one table with every runners data (name, age, gender, etc. including an Id)....
0
votes
0
answers
86
views
Firestore database structure - Users and Profiles
Relatively new to NoSQL databases and am curious on what the best practice is for the following. I've also been reading https://firebase.google.com/docs/database/android/structure-data#...
- 228
0
votes
1
answer
111
views
What would Google-Docs-like version history look like using Postgres?
After some initial research, I'm trying to determine a scalable solution for implementing Google-Docs-like version history for Postgres tables. The difficulty is that these tables have one-to-many and ...
- 430
0
votes
1
answer
62
views
How can i make a primary key chose the table to link to between two tables (specifically where the primary key exists)? [duplicate]
I have three tables of concern to my problem: students, staff, and issued_books. The issued_books table has book_id column (reference to the books table) and user_id column (reference to either ...
0
votes
1
answer
172
views
Correct data update architecture
The process
I update contents by several crons. Some crons update metadatas from different sources, some update prices from different sources, some push the contents to things like marketplaces.
Until ...
0
votes
1
answer
43
views
Slow SUM for 77k rows table with 1 big column
Consider following Table Structure ( 77k rows and 237MB dump size, column cart_data_json as of right now can be as big as 0.01k to 50k characters )
CREATE TABLE `commerce_sales_test` (
`...
- 23
0
votes
2
answers
355
views
One-to-many and one-to-one in Elixir Ecto at the same time
I have 2 tables/schemas with relationship one-to-many, let's assume there is a Person that can have multiple Animal. Then each animal has person foreign key to implement this relationship. I use ...
- 2,463
-1
votes
2
answers
44
views
In user_db design which design would be more manageable?
I am trying to design the user database that has an administrator, manager, user. I thought of two ways.
The first one is the user has a role column and will make relation between user-manager with an ...
- 67
0
votes
1
answer
276
views
Timescaledb design for existing tables / dedicated table(s)
I'm building a software that is indexing and analyzing a large amount of blockchain data and I'm considering using TimescaleDB for better analytics performance as I'm running into performance issues ...
- 2,807
-1
votes
2
answers
75
views
Which one is better: in database?
I am working on a system using JPA where Entity 'Events' are associated with Entity 'Groups' and each Group has fields (Group1, Group2...) indicating their attendance status for an event.
How to ...
user23202946
-2
votes
1
answer
127
views
Implement sharing in a privacy-first document management system [closed]
I'm trying to design a privacy-first document management system, where the user's content should not even be readable by our team.
The user should, however, be able to share the documents with a ...
- 1,123
-1
votes
1
answer
220
views
Understanding User Relationships and Follow Functionality in Prisma Data Model
I'm working on a social media project and implementing a 'follow' functionality using Prisma. While working on this, I encountered the code given below, where the relations in the User model seem ...
0
votes
1
answer
111
views
Flat datastructure vs multiple databases - NoSQL
Firebase documentation article Structure Your Database section Best practices for data structure says "Avoid nesting data".
What are the benefits for performance and maintenance to have ...
- 2,149
-2
votes
1
answer
46
views
designing a real time compute/velocity counter engine
I aim to create a low-latency computing engine tailored for online transaction data. I will extract information such as email, phone number, and customer device from the transaction payload, then ...
- 1
0
votes
1
answer
294
views
Define/design these related Postgres tables in my Prisma schema
My application using Prisma has users who can create many projects. These projects are akin to text documents, but with some additional attributes that assist with organization.
These projects will ...
- 430
-1
votes
1
answer
127
views
Postgres Query to find similar strings
I have a 10 million+ PostgreSQL (v16) table that stores address details for a person. I have to find the addresses that are similar to the input address and exceed a threshold.
We have tried module ...
- 132
1
vote
1
answer
104
views
Is there something like func.group_concat() in SQLALchemy Core that will return a list of objects, rather than a string?
Is there a convention/is it possible to return an array/list of nested objects within a column, e.g. from joined many-to-many tables?
For example, say I have three normalised tables: "movies"...
- 21
1
vote
2
answers
1k
views
Postgres hide some columns from user in SELECT *
I want to give a group of users selective access only to particular columns of an Application due a privacy concern. So, when user types '''SELECT *''' on restricted table, he could only see allowed ...
- 401
-1
votes
2
answers
550
views
How to make a "custom" auto incrementing ID [duplicate]
I have an app with invoices, I set the invoice id to auto-increment.
my client wants the ID to include the year and restart at 1 when a year passes. for instance ids could look like this.
2023-344
...
- 377
3
votes
1
answer
2k
views
NOT NULL constraint violation at UPDATE ON CONFLICT query
I have a table player with the following DDL. I only changed the column names and the order:
create table if not exists player (
id varchar primary key,
col1 boolean not null default false,
...
- 185
0
votes
0
answers
25
views
represent things that share some basic characteristics and have custom data in a database [duplicate]
I'm working on an application that needs to keep track of physical places in the world that are hierarchically related - berth, terminal, port, country. They all share some basic characteristics, like ...
- 97
-1
votes
1
answer
68
views
How many entity tables should I have? User table attribute role or three tables for the roles? [closed]
Roles are handler, sitter, and client in the Pet Sitting Tracking Service.
Should 3 separate tables be made for each role or should one role table linked to user will be sufficient?
CREATE DATABASE ...
0
votes
1
answer
74
views
Is it good practice to duplicate the connections to the grandparent in the parent and son in SQL? [closed]
I've seen different database designs in different projects. Some of them claim that from son to grandparent one should go through the parent (intuitively correct); in others there is a lot of ...
0
votes
1
answer
268
views
Explicit many-to-many relation Prisma schema syntax for MongoDB
The following is my Prisma Schema for 3 models --> User, Cart and Item.
model User {
id String @id @default(auto()) @map("_id") @db.ObjectId
name String
email ...
1
vote
1
answer
228
views
Should I use a UUID as PK + index by (user_id, UUID), or a single composite (user_id, UUID) PK?
A relative noob building a Postgres SQL database to track data by user, where every row is guaranteed to have a uuid as well as user_id assigned, and with most queries being by user_id and optional ...
2
votes
0
answers
473
views
Multiple feature branches that need to introduce their own migrations - Alembic
Our team is using Python's Alembic in conjunction with SQLAlchemy as our DB migration tool. Say there are two developers and two feature branches are created from a parent branch called feature1 and ...
- 165
-1
votes
1
answer
355
views
Composite Sort Keys - duplicating attributes separately
From my understanding of composite sort keys on DynamoDB it provides greater flexibility on querying and can be lower cost than creating global secondary indexes.
Suppose I'm creating a blog with time-...
- 37
-1
votes
3
answers
177
views
How to generically model many to many entity relationships in SQL? [duplicate]
I have a SQL database for maintaining D&D games which has tables for things like Characters, Items, and Locations. I want to start modelling the relationships between all these different entity ...
- 67
-1
votes
1
answer
121
views
How to model a complex relationship involving a Weak Entity and entities related to its parents?
I'm working on a database schema involving four tables: A, B, C, and D. The relationships among these tables are as follows:
Table A can have one or many records from Table B.
Table C can have one or ...
- 56
0
votes
1
answer
39
views
Best way to handle page numbers in MySQL bibliography?
I’m trying to work out the structure for a bibliography database with three basic entities: text (essays, articles, etc.), publication (the books, journals, etc. that contain those texts), and maker (...
- 51
0
votes
2
answers
714
views
Mapping EER to Relational model. Mandatory participation of superclass with (Mandatory; Disjoint) subclasses
I have a Superclass A that has a 1:* (one-to-many) relationship with another entity (X) and the participation of A is mandatory. A has two subclasses with their own distinct attributes, B and C. The ...
- 23
0
votes
0
answers
54
views
relationships for storage of working hours in a database divided into multiple points of sale
I'm creating a management system for the company in Laravel. I'm doing the part that concerns the management of employee schedules and our various sales points.
I have a user table, a front offices ...
- 11
0
votes
1
answer
94
views
Normalize a table with a non-prime attribute and a multivalued dependency within the candidate key to 4NF
I will extend the 4NF example from Wikipedia with a Cost attribute.
Define the relation from Wikipedia as R(Restaurant, Pizza, Area). By the original definition of R, there are multivalued ...
-1
votes
2
answers
1k
views
Best Practice for DynamoDB sort key
As I focus on designing DynamoDB with a query-centric approach, I find that the sort key is becoming quite lengthy.
For instance, I plan to use PK as like USER#{UUID} and structure SK as like ...
- 77
-3
votes
1
answer
49
views
Database Scheme for User made Online Election
I'm currently working on a web project "Online Election" where users can create Election as they need.
I can not think of any idea how the Database should be, For the database, every time ...
- 1
-1
votes
2
answers
67
views
How to fix my DB model to remedy unique violations?
I know that primary key values cannot be repeated. However, I want to make it so that games can be played on more than one platform. For example "Dark Souls" on PC and PS4. How do I modify ...
1
vote
1
answer
66
views
Delete unreferenced row automatically in many-to-many relationships
Suppose I have a table artists and a table tracks, with a many-to-many relationship between the two.
When I delete an artist, I also want to delete all the tracks that have no corresponding artists ...
- 5,383
-1
votes
1
answer
63
views
MS Access (w/ linked SQL tables) form where rows are transformed to columns for data entry
I have a MS Access w/ linked SQL tables. There is a table for recording the different projects and the people working on them. The table is of this form
ProjectID
PersonID
PersonTitle
12
123
VP
12
124
...
- 3
1
vote
0
answers
60
views
Best Practices for Efficiently Writing Time Series Data to MongoDB Collections with Dynamic Variables
I am currently working on a project involving MongoDB with three collections: datasources, variables, and a time series collection values. The values collection stores time-stamped data for various ...
1
vote
0
answers
28
views
How do I go about replicating the "opened/unopened" system of your phones "mail" app in a NodeJS + MongoDB application?
The app we are dealing with:
Imagine a NodeJS e-commerce app where you can purchase items, and user orders are stored in MongoDB.
Imagine the app has an admin dashboard where all orders are listed in ...
- 620
0
votes
0
answers
54
views
Synchronization of tables in the database
I am in a scenario where I have 2 applications (Rest API), and each of them has its own database. Application A with database A, and application B with database B. Database A has a table X that I need ...
- 23
-1
votes
1
answer
61
views
SQL: help add secondary filter to chart query [closed]
Need query that returns (x, y1, y2) for JFreeChart JDBC via HSQLDB. This is what I have so far:
SELECT "X", "Y" AS "**Y1**" FROM TABLE WHERE A=1,B=11,in1='chocolate',(ABS(...
- 3
-1
votes
1
answer
306
views
Sharding multiple tables with no common column
I wanted to understand sharding in case of multiple tables which might be used for QnA websites like Quora/SO. Let's assume that users can ask questions, give answers and comment on both questions and ...
- 1