Questions tagged [database-design]
For questions about structuring the data within a database. How to lay out tables, whether to use a relational DB or not, etc.
1,257
questions
0
votes
2
answers
61
views
Microservices - create post for logged in user
I am working on a small project with microservices architecture in Spring Boot. As to not make unnecessary calls to users-microservice, I have duplicated some necessary User data (id, name, summary, ...
- 1
-3
votes
0
answers
55
views
How to design a system that imports data into the system from a large amount of files
A lot of our clients migrate from their existing system to our web application. they bring in approximately 150,000 excel files which I have to first load in my database and then perform some ...
0
votes
1
answer
98
views
How to model a ternary relationship in a graph-based paradigm?
Consider a context where we have Users saving Questions and adding personal Tags to them.
In a graph-based paradigm, a first approach could be something like:
(User)-[SAVES {tags}]->(Question)
...
- 121
1
vote
1
answer
109
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 ...
- 11
-1
votes
0
answers
48
views
Storing history of a hierarchy changes in database
I have a class that uses a hierarchy structure:
Class
{
string Id;
string ParentId;
//... other properties around 30
Date ModifiedDate;
Date CreationDate;
...
- 99
1
vote
0
answers
31
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
1
answer
40
views
Store UUID Reference vs. storing string in Mongodb
I am trying to store user preference for notifications.
One way to do is store types of notification in separate collection and store UUID of that notification type in user table.
e.g. notification-...
0
votes
0
answers
31
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, ...
-1
votes
1
answer
30
views
Application design to handle dynamically pivoted data
I have an application that has different granularity for each record type.
Assume that inventory type A is planned quarterly, so it has 4 values, and inventory type B is planned monthly, so it has 12 ...
- 99
0
votes
0
answers
31
views
Optimizing database lookup with No SQL MongoDB
My restraints are that I have to use:
Django and MongoDB
I am trying to build a website that will read some JSON input and then parse all the properties and objects in the JSON and display results. ...
- 1
-3
votes
1
answer
75
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 ...
- 151
-1
votes
2
answers
84
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
0
votes
1
answer
41
views
User data when building a custom system on top of a CMS
I'm building a site using a CMS that already holds user information such as userId, Username, Firstname, Lastname.
I want to build a system that holds additional user information such as the user's ...
- 13
1
vote
3
answers
97
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 ...
- 331
0
votes
1
answer
66
views
Best way to calculate profit/loss with historical data
Problem
I need to build a profit/loss graph for a mobile app. The api from which I can get the historical price data only allows me to retrieve 1 item at a time. If the user has 500 items that would ...
- 101
0
votes
1
answer
92
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
12
votes
5
answers
5k
views
What is the best practice about microservice architecture for consuming many stored procedures in the same database?
I have to build a micro service for my company, the backend is an Oracle database, but the micro service must consume five (5) stored procedures that already exist in the database (as shown in the ...
- 139
1
vote
1
answer
88
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
-2
votes
1
answer
52
views
Should cached data be kept separate from primary tables?
I have a table in an SQL database for storing information about some business object that looks something like this (details changed so as not to give away what company I work for):
CREATE TABLE ...
- 318
1
vote
0
answers
134
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
33
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
0
votes
2
answers
95
views
How do I structure the db effectively to query historic data like "Who was the HOD of the department at a given point in time"?
First of all I am not sure if this question should be here or in stackoverflow. I decided to post it here because it is not a coding but architectural challenge.
Introduction
I have a data base design ...
- 119
0
votes
0
answers
27
views
Django Model Design
I am in the design phase of a project and I am having some trouble finding a solution that can perform queries efficiently with PostgreSQL backend
Say, there exists an item and each item has fields ...
-3
votes
1
answer
120
views
Most efficient way to get average data for thousands of records from the database [closed]
I have a database that keeps a temperature readouts, which are being added - let's say - every minute:
+----+---------------------+-------------+-----------+
| id | timestamp | temperature | ...
- 149
0
votes
0
answers
27
views
How to structure my Google Firestore DB for a crypto signal application
I'm brainstorming how to structure my Firestore DB and would love others input as I'm not a backend developer and generally new to software dev.
Backend Stack
Firebase
Firestore
Cloud Functions
...
0
votes
1
answer
98
views
Time consideration in multiplicity of classes
In the following image, regarding a CD shop that rents its music CDs.
My question is regarding the correct multiplicity of X. I can think of several possibilities for X:
X= 1..1 as each copy of ...
- 99
0
votes
1
answer
94
views
SQL Foreign Key Relationships and External Data
I work for a company that wants an external system built outside of their core system. I can't share an SQL database, and can't store data in their database. So I am forced to maintain my own SQL ...
- 117
-3
votes
1
answer
39
views
Creating a Django web-app with Sage 200 Database
I have been asked to create a Django/Python web app that creates web-based, .pdf and excel reports from a Sage Evolution database. While the sage front-end is still being used. My client essentially ...
- 1
-1
votes
2
answers
156
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,467
0
votes
1
answer
95
views
Syncing and matching local records to external database that behaves like a black hole
Black hole problem
I am working on matching and daily syncing of our internal data records to an external database that behaves like a black hole, meaning, we cannot view nor extract any data from it ...
- 9
1
vote
2
answers
27
views
Data Model for abstracted transactions table that combines more granular transactions
A fast food restaurant has an existing sql table of transactions:
old_transactions:
transaction_id
item_description
quantity
price
1
burger patty
1
5
2
bun
1
1
3
ketchup
1
0.25
4
packaging
1
0.50
...
- 57
-1
votes
1
answer
79
views
Should I use a database as gateway to other services? [closed]
The context
I have an IoT project where the sensors are sending data to my Postgres database. The sensors are manages by a stand-alone service which provides a REST API to query various information ...
1
vote
3
answers
158
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
1
answer
137
views
Developing a database: How to keep track of log files
I am building a database, and have implemented a transaction log system to ensure that writes are atomic, idempotent & consistent.
When a transaction is 'committed' it is appended to a log file ...
- 3
0
votes
3
answers
79
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
0
votes
2
answers
47
views
Where to sync Database and Search-Database in MVC pattern
I have a website-sideproject (Application Server: Django, Database: sqlite) with "search" functionality and I've recently stumbled over this problem.
When you have a full-text-search-...
- 163
0
votes
0
answers
22
views
How should I structure my collection and documents to create a chat app, using Googe Cloud Firestore and react native?
I am writing a chat app. Each user has a profile, so every user can message to another user (one to one).
My idea so far: Once a user message to another user I call createChat function:
const ...
- 111
0
votes
1
answer
248
views
Recording history - All changes in one table, or three tables?
We're starting to record history in an about 5 y/o internet system built with ASP.NET Core and SQL Server.
Our current requirement is to save all records from a specific table, plus two fields from ...
- 260
-2
votes
1
answer
167
views
Software architecture for a dynamic reporting system
I have to create a reporting system based on hundreds of different tables in a database (which means a huge volume) and I'd like to know the best practices and/or your wild ideas about it.
Here are ...
- 17
1
vote
1
answer
119
views
Dealing with queries on versioned data
I have a dataset stored in ElasticSearch, only for searching purposes (the source of truth is external).
Each index has an associated schema and there are associated queries.
From time to time, the ...
- 127
0
votes
1
answer
102
views
Relationship problem between entities
I'm not sure how I should think regarding my database design. I have made this database schema below. And I'm trying to think about the relationship between the entities. I have read through online ...
user390016
-2
votes
2
answers
124
views
How can we implement an incremental naming system?
Just like how Mac or Windows gives incremental names to new folders, how can we implement such a system in a database?
Let's say I have currently 3 folders (default names) i.e., "untitled folder&...
user16595023
2
votes
1
answer
81
views
What is a good approach to handle editing product images?
You have the following situation:
You have an SQL database containing two tables product and product_images
You would like the user to be able to add, remove, or move around existing images
They can ...
- 131
0
votes
0
answers
23
views
Database architecture for Email Marketing which can be used as a micro service
I am working on a very challenging email marketing database design and i am not able to figure out the best way to build the database, where functionality can be split so it can be used as a ...
- 101
0
votes
1
answer
130
views
Architecture for alerts system based on API - would this be too slow a design?
(Hi I’m new here - I believe this question suits here from the guide saying “ architecture, and design” - however if there is a better place, just pop me a comment. As there’s no actual code it doesn’...
- 101
4
votes
2
answers
924
views
Thoughts on database denormalization via short-circuit key
In the simplified database schema depicted below, I'm considering denormalizing by adding a short-circuit key (EventId, relation shown in red) on Appointment.
One advantage is not needing a join to ...
- 238
0
votes
1
answer
50
views
What are the benefits of having database in separate instance to main application?
Positive:
Allow for horizontal scaling of app by replication of instances - without replicating the DB
Drawback:
latency introduced between 2 machines
Is this something necessary for small systems?...
- 675
0
votes
0
answers
27
views
Best way to see changes to non-monitored linked tables
I am hoping that I can get feedback on my solutions, and other ideas as well.
We have a table in a database being monitored by a NodeJS app, using the MYSQL binary logs, E.G.:
ORDERS:
orderID, ...
- 1
1
vote
1
answer
81
views
Showing posts by their time-decayed score
I want to show a number of posts, ordered by their score, limited to some number. The score is the product of the number of likes they have (from another table) and the recipricol of their normalized (...
- 452
0
votes
1
answer
114
views
Best practice or patterns to couple complex custom objects to custom logic
In many project that grow more complex at a certain moment business rule editors, workflow editors, custom taxonomies and such enter the arena.
In all of these a coupling is made between a complex ...
- 564