6,816 questions
Advice
1
vote
5
replies
45
views
What exactly is a "connection trap"?
In Codd's A Relational Model of Data for Large Shared Data Banks the following paragraph seems to be one of his justifications for the relational model:
A lack of understanding of relational ...
- 685
-1
votes
0
answers
44
views
Best way to model optional foreign keys when only one should be set [duplicate]
I’m designing an Entity-Relationship diagram as the basis for a relational database.
A timetable planner for a comprehensive school needs to decide, when creating a new lesson, whether the lesson is a ...
- 379
2
votes
2
answers
70
views
Circular Dependency error in NestJS TypeORM Relations with Entity Decorator
Why do I get this circular dependency error in NestJS TypeORM Relations with Entity Decorator when setting up a OneToMany and ManyToOne relation between User and Item?
cannot access User before ...
- 23
0
votes
0
answers
107
views
How to define self-referencing relations in Python SQLModel ORM
I'm developing a FastAPI application, a file manager system, and I'm using PostgreSQL as the database. To define database entity models in my code, I previously used SQLAlchemy, but now I switched to ...
- 106
0
votes
1
answer
44
views
What HTTP status code for Patch that may create and/or delete entries from nested table [closed]
Let's have a data structured like this:
users: [{
"name": "username1",
"garages": [{
"name": "my one garage even though I may have several",
...
- 13
0
votes
2
answers
108
views
Same Table value selection from same clients
I need 1 record per Id and to achieve it I tried with self join, case statement as well tried using COALESCE function but I am still getting multiple records for same client.
Below approach is mostly ...
- 13
0
votes
1
answer
190
views
Does "tuples are not necessarily distinct" imply they are equal? How do I show whether this multivalued dependency holds in the example table?
From the book Fundamentals of Database Systems (7th edition) by Elmasri et al., pages 475-476:
A multivalued dependency [MVD] X ↠ Y specified on relation schema R, where X and Y are both subsets of R,...
- 365
0
votes
0
answers
78
views
How to use pivotData with enum column in a Filament 3 many-to-many relationship?
I have a many-to-many relationship between roles and permissions in my application. I already have the pivot table with columns role_id, permission_id, and a new column access_type with an enum that ...
1
vote
1
answer
99
views
How can I update a large table slowly?
I am using PostgresSQL 15 hosted by AWS Aurora serverless v2. I have a table includes 200 millions rows and I need to update a value on a column for all those rows. This table has primary key id which ...
- 43.3k
-1
votes
2
answers
104
views
Difference between nested transactions and savepoints
Some relational databases only support savepoints and not nested transactions directly. So, what is the difference between both? Is one superset of other?
I understand savepoints are not new ...
- 1
-2
votes
1
answer
71
views
Meaning of hashtag in a relational schema
What does the # mean when placed before an attribute in a relational database schema?
Umbrella(ID,Lido, Cost, Type)
Lido(ID, Name, #Spots, Manager)
Manager(FiscalCode, Name, Surname)
Client(#Card, ...
- 21
2
votes
1
answer
51
views
How do I format plain query to hierarchy json object?
I want to format plain query to hierarchy json to display on front-end but I don't know how
I may have relational database that has Table called "Todo" which has following column
ID
Name
...
- 29
1
vote
2
answers
70
views
In SQL, how to search for rows by binary data prefix?
For example, I want to search for all rows that starts with \x00\xff\xaa in a binary data column. I am writing this pseudo-code in C# Entity Framework:
IEnumerable<KeyValue> ...
- 412
2
votes
1
answer
62
views
Android Room: How to extract data from relational tables?
I have 2 tables as below and I have a relational table.
I want to access the details of an order. Which products were selected in the order and what is the quantity of the products. What should I ...
- 125
0
votes
1
answer
173
views
issues when trying to rename a column and modify the datatype
I am having issues when trying to rename a column and modify the datatype. I can do it separately, but when I try to put them together it can't find either 'Year' or 'ReleaseYear'. It doesn't matter ...
- 103
-1
votes
1
answer
83
views
model multiple relationships of an entity with multiple others in relational database
I am attempting to create a relational database to model fight sports information--contests between two opponents, each with three judges and a referee. I have tables that store information on each ...
- 19
-1
votes
1
answer
75
views
get unprocessed rows that have no association in processed table
How do I get unprocessed rows that have no association in Processed_Table with the least number of queries (SQLite 3)?
Nested queries will work fine.
Main_Table columns: Id
Processed_Table columns: ...
- 81
0
votes
1
answer
69
views
stock management - determine opened state automatically
I have a database for animal rescues which has a medication section. I'd like to have the stock automatically deduct when it is used. The challenge is the opened bottles. For something like an ...
- 35
0
votes
1
answer
52
views
Django IntegrityError: NOT NULL constraint failed
I want to create model Serializers and all the necessary code. I want to solve a Coursera project assesment of API course.
Error: django.db.utils.IntegrityError: NOT NULL constraint failed: ...
- 59
0
votes
0
answers
117
views
Clarifications on BCNF decomposition and dependency-preserving
Given a relation schema, R = {A, B, C, D, E, F, G, H, I, J, K, L, M, N, O} with functional dependencies:
FD1: {A,B} -> {C,D}
FD2: {E} -> {F,G,J,K}
FD3: {F, G, I, K, M} -> {H}
FD4: {K} -> {...
- 109
1
vote
0
answers
78
views
Which is better in a database: one large table with columns that are often NULL, or many different tables?
Context: I work for a company that stores and analyzes data from inertial measuring units (IMU).
My teammates and I are considering creating and maintaining a DB for all the data we gather for ...
- 11
0
votes
1
answer
52
views
How to model exclusive relation in relational database?
Let there be 3 tables: A, B, C.
A row in A may be associated to a row in B, or it may be associated to a row in C, but it must not be associated to both B and C.
In other words, the association is an ...
- 3,377
1
vote
0
answers
44
views
How to merge data from different tables into one entity with Kotlin and JPA?
I'm new to the Kotlin world and I've been trying to figure out what would be the best way to represent the data fetched from 3 different tables in the database (2 out of 3 at a time), ideally as an ...
- 11
1
vote
2
answers
90
views
How does "OR if Y is a prime attribute" eliminate redundancy for 3NF?
Assuming the schema R(A,B,C,D,E) with the functional depencies AB -> ABCDE (AB is the superkey) and C -> B (B is a part of the key AB, so the schema is in 3NF for all dependencies).
What does it ...
- 21
1
vote
0
answers
47
views
Converted nested Json array into relational csv file
Develop a utility that takes any JSON input and converts it into a flattened tabular format with rows and columns. The utility should handle nested arrays and objects, transforming them into a ...
- 21
-1
votes
1
answer
62
views
Any way to add a falsey `ORDER BY`?
I often add in a falsey predicate for convenience in debugging, like this:
SELECT ...
WHERE 1=1
AND/OR predicate1
AND/OR predicate2
...etc
This way I can comment out one or more lines and ...
- 112k
0
votes
1
answer
49
views
Select Rows Based On Foreign Key In One-To-Many Relationships In SQL
I have a posts table and users table. And posts table has a column for author_id (foreign key). This is clearly an example of a One-To-Many relationship. I want to get all posts written by a specific ...
- 325
0
votes
1
answer
44
views
Is it a one-to-many or many-to-many relationship?
I was wondering whether the following would be either one-to-many or many-to-many relationship
The Users and Services tables are defined as follows:
USERS SERVICES
+---+------------+...
- 19
0
votes
1
answer
64
views
Why is PostgreSQL sorting a seemingly already sorted result set?
I'm trying to optimize the following query for a school assignment:
SELECT
DATE(b.book_date),
SUM(b.total_amount) revenue,
COUNT(DISTINCT(t.passenger_id)) count_passengers
FROM bookings b
...
- 45
0
votes
1
answer
91
views
Better way of handling many-to-many relations with EF db-first approach in .NET6
I'm having a Request table (EF generated):
public class Request
{
public int Id { get; set; }
public string Name {get; set; }
public virtual ICollection<AttendeeRequest> ...
- 309
-1
votes
1
answer
63
views
Representing a plant with definite genus but unknown species
I want to represent plants in a database along with their genus/species. This was the schema that came to mind:
CREATE TABLE genuses (
id INTEGER PRIMARY KEY,
name TEXT UNIQUE NOT NULL);
...
- 2,659
2
votes
3
answers
347
views
how to use index when searched column is not indexed but has the same ordering as the indexed primary key
I have a huge table (kind of audit log) with these columns:
ID, TS, DATA
ID is the primary key, and it is a number from a sequence.
TS is a timestamp and it is the current timestamp of the insert.
...
- 1,839
0
votes
0
answers
38
views
How do I efficiently manage relationships in a multi-tenant application
I am developing a multi-tenant application that manages information about suppliers and clients, using Entity Framework for user logins and roles. Suppliers provide products to clients, and clients ...
- 1,277
1
vote
2
answers
74
views
Getting java.lang.StackOverflowError when two users follow each other
I am working on a Java application using Spring Boot, and I have a UserEntity class where users can follow each other. However, when two users follow each other, I encounter a java.lang....
- 31
0
votes
1
answer
48
views
Remove a entry from hibernate collection without fetching entire collection
Using Hibernate, I have bi-directional association between person/address. Person entity can have too many addresses and I want to add and remove to address collection without loading entire ...
-4
votes
2
answers
140
views
Why should the operands for UNION be SELECT statements and not tables?
In SQL, given tables T and S that have the same schema, say,
CREATE TABLE "T" ("ID" INTEGER, "Salary" REAL);
CREATE TABLE "S" ("ID" INTEGER, "...
- 15
0
votes
1
answer
104
views
Enterprise data model design for small business
I work at a small company that buys inventory and resells either the raw inventory or packages it up into bundles for resale. We have an in-house software and are redoing our data model for an ...
0
votes
0
answers
29
views
How to handle this SQL query that involves multiple tables
Some employees at my company want an internal tool with a way to approve new purchases before we expense them to the proposed departments. So someone will submit a purchase order for approval, and ...
- 11
0
votes
0
answers
61
views
Handling events in a relational data warehouse that could be updated in the source
I have to design a table in a data warehouse to store event like 'box access'. The source system works as follows:
An access event is registered by an employee, creating a new record with a unique ...
0
votes
0
answers
42
views
Create a snapshot table of other tables that have nested FK
Assume I have the following tables:
Article (id[PK], home, category, userId[FK], locationId[FK])
User (id[PK], name)
Location (id[PK], address, name)
Now I want to create a new ArticleArchive, that ...
- 1,199
1
vote
0
answers
58
views
DataGrip problem with connection on Linux
I'm working on my college project from Relational Databases subject. I'm using Linux. At college we are using Oracle databases.
I'm facing problem: "the network adapter could not establish the ...
- 11
0
votes
0
answers
30
views
Flask SQL, alembic and SQlite: how to avoid delete object when is referenced (Foreign Key) [duplicate]
Given 2 tables: transactions and categories.
Transactions contains a FK category_id with the ID of assigned category.
I want to not allow delete categories referenced by transactions. I mean: if there ...
- 1,347
1
vote
1
answer
157
views
How to maintain user-modifiable order of items in SQL
My application handles many-to-one relationships between packages and documents. A package contains multiple documents. They are currently stored in PostgreSQL database using tables package and ...
- 1,474
0
votes
1
answer
61
views
Dependency Cycle in Database with Update Approvals
I'm struggling with a dependency cycle in my SQL database schema for a system with update approvals. Here's the situation:
I have tables for documents , transactions, payment_methods, and ...
0
votes
0
answers
29
views
How an appointment (which includes start_datetime and end_datetime) can be stored in a database?
In a project of mine, I want to create a calendar where a teacher can create appointments for his/her free time and then students can book an appointment with the teacher to teach him/her. This is how ...
- 55
0
votes
1
answer
43
views
Is the statement correct with respect to the circuit depicted in the ER diagram?
An employee can work in multiple offices.
Please evaluate this expression as applied to this ER diagram. It seems wrong to me.
The relationship between Office and Company is one-to-many. This implies ...
0
votes
0
answers
131
views
Simplest way to send data from a google form to a MySQL database?
I am looking to create a MySQL database which stores information which is coming from a google form.
I was thinking of having the google form results stored in google sheets and then using JDBC to ...
0
votes
1
answer
40
views
Laravel relations not returning nested relations
I have an Order model in my Laravel application, the Order model can have many Order items.
public function items()
{
return $this->hasMany(OrderItem::class);
}
My Order Item model relates to ...
- 7,048
0
votes
1
answer
296
views
Handling relationships with React Query
Are there any pre-existing libraries that help with handling relational data with react query? For example, say I have a backend model called 'Person'
type Person = {
id: string;
age: number;
...
- 93
1
vote
1
answer
513
views
Entity Core Framework - what is the difference between HasColumnType and HasPrecision?
I've recently started working on a project with Entity Core Framework in C#, and my question is related to 2 configuration methods for the fields of an entity. As you probably already know each entity ...
- 531