Questions tagged [relational-database]
A relational database is a digital database based on the relational model of data. This model organizes data into one or more tables (or "relations") of columns and rows
377
questions
0
votes
3
answers
103
views
Optimizing writes over the same entity in relational DB
I am developing a micro service that saves votes to a relational database. I have a use case with an entity that represents an aggregation of votes. I receive events from voters saying that they vote ...
- 146
0
votes
0
answers
94
views
How to design entities to keep up with the depth of JSON objects?
To better context see Rich Authorization Requests for OAuth2
As u can read here there are some common data field types like actions, locations etc.
So I have created C# entity for EF as below:
public ...
- 109
0
votes
3
answers
149
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?
- 119
1
vote
1
answer
100
views
Representing database tables as objects
I'm building an address book API, where a user can input multiple contact names, phone numbers, and addresses.
My database structure would be as follows:
create table user (
id int not null primary ...
- 19
0
votes
0
answers
40
views
How to handle multi-region writes for a joint user registration?
UserA and UserB are signing up for some joint service together. They both have to independently agree to T&Cs before sign-up is complete. Once complete they both receive an email to sign-in.
A 1 ...
9
votes
10
answers
4k
views
Is there any benefit to a separate table that is one-to-one with the primary data table?
I've inherited a system with an Oracle relational database with a couple of tables modeled like I've sketched below, where there's an entire child table that only stores a single status code in a one-...
- 346
1
vote
0
answers
74
views
Efficient way to implement hierarchical inheritance in SQL?
I am working in a database that has a hierarchy of Companies (From location-level all the way up to Top-level parent company). I am designing a system within that to provide a "subscription" ...
- 11
2
votes
3
answers
180
views
User's comment field on a row in relational databases
Suppose that we have a SQL relational database for, let's say, asset management system. It uses a table of assets (1 row per one real-world object). There can be various metadata etc. To allow the ...
- 191
1
vote
1
answer
217
views
Foreign key vs string
I have to create a table to map some strings that are my input, to the result value that is a value that I can choose, like an id (or string).
So, to make an example, I have an excel file made of a ...
- 459
-1
votes
1
answer
42
views
Picking data store for Location Tracking Based Application
I have one design problem for one of my projects based on location tracking. The scenario is I have multiple devices(for some business) all over the city and have GPS installed inside, the device is ...
0
votes
1
answer
50
views
I need to choose records from a database to feed to a model for training, but would like to weigh records beforehand. What's a good strategy for this?
I am currently responsible for feeding data to an STT model. My backend is written in Python/Django and is primarily responsible for managing a Postgress database. The database has a table that saves ...
- 109
0
votes
1
answer
209
views
Form builder app database design adding versioning
i'm making a custom form builder app similar to google forms or wufoo and i've completed the basic database design. However there is one thing i can't figure out. I'd like to keep track of version of ...
- 109
1
vote
1
answer
162
views
How to represent NoSQL entities
I've mainly studied SQL structures, and I know that the representation of the entities can look something like this:
However, I'm building this app with MongoDB, so NoSQL DB. I'm aware that MongoDB ...
- 13
0
votes
1
answer
150
views
How to create financial reports with detailed information
I'm working on an application where it has financial control for the incomes and expenses of the company. These informations can be controled by accounts (bank accounts - can be 1 or more), categories ...
- 275
0
votes
1
answer
179
views
Database design for a reccomendation system
I am working on a project which will have a recommendation system and I am in the database design phase, I have a good foundation in programming but databases is a new topic for me, I have designed a ...
- 9
1
vote
0
answers
57
views
Backend Design: System requires both ElasticSearch and MySQL. In which scenario we fetch data from MySQL or ElasticSearch?
So, one of the backend system, having 5 resource types (tables) and some foreign keys as well, requires full text support for two of the tables.
Also, for some use-cases, we require inner-joins for ...
- 121
0
votes
0
answers
162
views
Using NoSQL (vs SQL) Database for Metadata in a highly scalable System like Facebook
If you are required to build a highly scalable system like Facebook would you prefer to use NoSQL database or a SQL database for the metadata.
I would use a SQL database for metadata i.e User Info, ...
-3
votes
1
answer
87
views
Where should linking tables be stored? [closed]
I have a system where properties can be stored, and linked with multiple other entities (each with their own schema). Let's say that Documents can be linked with Users and Assets. What is the best ...
- 161
-1
votes
2
answers
93
views
Difficulty understanding how denormalization results in more storage necessary
I am having difficulty understanding how denormalization results in more storage necessary.
For example, let's say that in a normalized relation, there's Table 1 and Table 2. In order to join Table 1 ...
- 153
1
vote
3
answers
294
views
Storing count of child rows in parent table
I have two tables, parent and child. Parents can "have" multiple children—in our case they are related through a third table, parent_child_mapping rather than the child storing its parent id ...
- 425
0
votes
1
answer
100
views
Designing a model for a small business which provides multiple services which generate documents [closed]
I am designing a system for a company which provides multiple services to users, all of which generate a document. The documents all have a validity period. Some of the services can be grouped, and ...
- 61
1
vote
1
answer
139
views
Database Design, Associative Entities
I'm learning databases and I'm designing an ERD based on simple banking processes. I'm not sure if my design of the entity relationship diagram is correct.
Thought Process
A customer can open 1 : N ...
- 27
1
vote
0
answers
232
views
Design MySQL database to store user workout data for Workout App
I am building a fitness app and I'm trying to figure out the optimal way to store the data is a MySQL database.
I would like to have specific programs (for example, 'get jacked'), I would like a users ...
- 21
1
vote
0
answers
55
views
How to handle storing of arbitrary data in an application enablement platform
I'm working on an AEP in the IoT space, where we've created a few domain-specific entities to allow users to model their data. Each entity has both a type and instances of that entity. These are:
...
- 259
-1
votes
2
answers
111
views
Database responsibility segregation
I have the following architecture: Database (Postgres), Batch(java app - long running operations that delete, update, insert lots of data in the database), Rest API(java app - provides rest services ...
- 107
2
votes
3
answers
462
views
Enforce correspondence between enum and lookup table
I have seen a common design pattern where there will be a "lookup table" in the database like this:
Color
ColorId
ColorName
1
Blue
2
Red
3
Green
with a foreign key constraint on other ...
- 6,965
0
votes
1
answer
124
views
Approach for store multiple relationships POO objects in relational database
I'm developing a system in which a ClassA object can have multiple ClassB and ClassC objects.
And for store these objects in a relational DB model I create the following tables:
This is a simplified ...
- 177
-1
votes
1
answer
45
views
CRDTs and merge conflicts in DB design
I face this problem with software development quite often, but I don't really know the name of this issue. It sounds quite common, though.
Two nodes (a computer and a mobile phone, for example) share ...
- 157
0
votes
1
answer
222
views
How to do "initial sync" of data for new microservice that depends on data from another microservice?
Hypothetical scenario:
Let's say I have a Customer service. It has 1 million customers in its database. I then create a new service that depends on a specific projection of the customer entity.
I want ...
- 115
-1
votes
2
answers
161
views
Is it normal and good practice to enlarge the amount of columns when adding new features
There is a table named is A with 40+ columns, among which around 10 are columns of hierarchical levels, in a data analysis platform maintenance and development ( legacy ) project. Due to development ...
- 1,519
2
votes
3
answers
448
views
Do NoSQL and non-relational refer to the exact same concept?
Question as the title says. Of course, I already tried searching online, but did not find a definite answer yet. (The sources I checked imply that the terms are interchangeable, but do not state this ...
- 477
-2
votes
2
answers
168
views
Avoid redundant field using JSON type in MySQL [closed]
I have experienc with designing relational dbs but first time attempting to store JSON in a mysql table. I am using mysql 5.7.8 which has the ability to store json data type as a column.
When I create ...
- 139
1
vote
3
answers
269
views
What makes a data pipeline scalable? Best practices for scalable design?
I have been searching about this topic for a few days and have not yet found anything on books, courses or tutorials.
What is a way to make data pipelines more scalable, that doesn't involve NoSql or ...
- 99
0
votes
3
answers
162
views
Fetch the most recently updated records in a database across multiple tables in a performant manner
I have an Angular + Django web application in which Django functions as my backend.
In this web application, I have 12 different tables about articles, each table representing a different kind of ...
- 163
3
votes
3
answers
268
views
Is there a normal form for avoiding exactly identical tables?
Consider this schema in a relational database:
There are two tables A, B, both having two columns X, Y. The constraints are:
X in both tables are the primary keys. (The values are unique in each ...
- 149
51
votes
6
answers
10k
views
Is denormalising a database for speed an anti-pattern?
I have a database with a 1:m relationship.
I have to display a list of parents to the user rapidly on a home screen at startup. The parent shows a single piece of information that is a sum of a ...
- 621
1
vote
1
answer
126
views
Feature Store Design for input data like JSON, CSV and Excel
I need a design approach for a feature store which accepts input data in the forms of CSV, EXCEL, JSON etc.
The backend stack we support is Java, Spring boot and oracle DB.
Note :
Each file ...
- 113
0
votes
1
answer
95
views
How to prevent mutual dependencies when implementing database relationships
To explain the problem imagine you have two entities User and Group. The OO implementation has two classes UserModel and GroupModel.
UserModel should have a method getGroups() (returning instances of ...
- 101
4
votes
3
answers
821
views
What benefit does a junction table provide over adding an additional field with a foreign key?
Consider this simple example:
We have the apartments table below:
ID
Address
0
555 Maple St
1
123 Oak St
2
999 Pine St
We also have the tenants table below:
ID
Name
0
Bob Foo
1
Jane Bar
If I ...
- 261
0
votes
1
answer
67
views
Database design: use a single "Author" associative entity related to different entities
My application deals with multiple entities (Articles, Files, Images, Documents, ...) and multiple Users (another entity). Every entity must have one or more "Author" User(s) associated to ...
1
vote
0
answers
32
views
Best way to store webshop item data as a snapshot when an order is created
I'm designing the architecture of my application and I'm running into an issue. For the webshop every Order entity has a relationship with a Store and many StoreItem's. This allows for quick fetching ...
- 19
2
votes
2
answers
531
views
How should I structure my database entities with invites and recipients for e-signing web app?
I am interested in implementing the following requirements for my e-signature web application.
A user can create a new signing contract. That contract can include multiple users to sign. The contract ...
0
votes
1
answer
536
views
Database design to model companies and many roles
I am building an application for employee management. To start discussion, I have 3 roles: admin, manager and employee
admin does everything
manager owns a company and manages its employees.
...
-2
votes
1
answer
92
views
Merge migrations from two different servers
I'm working on a project that has two separate versions of the API, v1, and v2 because they wanted to migrate from Javascript to Typescript. Both versions use the same database.
Recently I'm having ...
- 5
-1
votes
2
answers
743
views
How to have dependency between two different microservices?
I am implementing quora like application in which I have Post(question, answers, comments, likes/dislikes) as one microservice and User details as another microservice. I need user details in the post ...
-1
votes
1
answer
62
views
Solution design leveraging Graph and Relational database types
Is it viable to leverage both a graph db and a relational db at the same time for the same data sets? Does it even make sense to approach the solution this way?
Thinking is i use nodes and ...
- 227
0
votes
2
answers
62
views
Delete filesystem data associated with database record by delete hook, or internally in service logic?
Let's have many rows in the SQL database where every record has an image property that holds the path to file in the filesystem. Many database libraries have the ability to set-up hooks that are ...
- 145
-1
votes
1
answer
126
views
As a client; how to present database needs to perspective designers?
[I am preparing to solicit proposals for database/project management software design. I know that the quality of the final product will depend on how clearly and completely I can organize and explain ...
- 27
-1
votes
4
answers
275
views
How to avoid the accident that an item price is updated right after a user clicks purchase?
This is not a real scenario. This is just my assumption that such an accident may happen if the the following events really occur in this chronological order
First, a user clicked "puchase" ...
- 113
1
vote
1
answer
320
views
Synchronizing Multiple Local SQL databases with Server
I'm trying to understand what general mechanisms and/or concepts are available in SQL database last to synchronize local. Here are my inputs and requirements.
Multiple clients have a full copy of the ...
- 379