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.

Filter by
Sorted by
Tagged with
-2
votes
1answer
49 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 ...
0
votes
0answers
92 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 ...
1
vote
0answers
30 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: ...
0
votes
2answers
90 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 ...
0
votes
0answers
26 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
1answer
113 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 | ...
0
votes
0answers
24 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
1answer
90 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 ...
0
votes
1answer
86 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 ...
-3
votes
1answer
34 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
votes
2answers
153 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
vote
1answer
72 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 ...
1
vote
2answers
26 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 ...
-1
votes
1answer
77 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
3answers
114 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 ...
0
votes
1answer
133 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 ...
0
votes
3answers
76 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 ...
0
votes
2answers
45 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-...
0
votes
0answers
19 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 ...
0
votes
1answer
205 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 ...
-2
votes
1answer
116 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 ...
1
vote
1answer
65 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 have an associated schema and there are associated queries. From time to time, the ...
0
votes
1answer
100 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 ...
-2
votes
2answers
112 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&...
2
votes
1answer
80 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 ...
0
votes
0answers
22 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 ...
0
votes
1answer
96 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’...
4
votes
2answers
908 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 ...
0
votes
1answer
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?...
0
votes
0answers
26 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
vote
1answer
78 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 (...
0
votes
1answer
94 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 ...
3
votes
1answer
91 views

Is it 3NF to use a compound key, where parts of the key are also FKs

For example: PostCode PK PostCode Address PK HouseNumber PK PostCode FK Seems reasonable. But can we carry on indefinitely? Room PK HouseNumber FK1 PK PostCode FK1 PK ...
-1
votes
2answers
90 views

Contemporary Design and One-to-One Relationships: code-first vs data-base first approaches

After reading, Does it ever make sense NOT to condense one to one relationships?, I wanted to take the question further with regards to contemporary design of entities, front-end code and controllers. ...
0
votes
2answers
53 views

Structure database for timeline with different objects

I need to work on a project where we have a "case". Within this case we need to attach different things, in a timeline. Those things can be: Status change (Open, On hold, Closed) Messages (...
0
votes
2answers
72 views

How should design my entities when I sometimes need only a subset of fields?

I have a class that is mapped using Entity Framework. This class has dozens of fields, and in the database there are millions of rows for this class. The class looks like this public class MyEntity {...
0
votes
1answer
58 views

Is there a difference between these two data models?

Let us say I want to store on a database a userID, their nationality and if they are from the UK their UK passport number. I could model this by having one table with userID as primary key, ...
50
votes
6answers
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 ...
-2
votes
2answers
85 views

Database Structure for The Best Efficiency

I want a database table that can hold all of the group chat messages from all of the group chats. The max amount of users per group chat would be 20. The max amount of group chats per user would 25-...
-1
votes
1answer
88 views

Design pattern to sync local databases with the cloud [closed]

I would like to find the best way to solve the following problem: I need a database for a software. The software needs to use the data to populate the UI and allow the user to interact with them (edit,...
0
votes
0answers
15 views

Abstracting resource permissions in noSQL

I'm building a booking system and my team is currently constructing a web application for the system where a staff member should be able to manage all the bookings related to a user with permission to ...
0
votes
0answers
24 views

Storing common data across tenants in NoSQL databases like mongoDB

We are designing a multi-tenant architecture. In our multi-tenant architecture, there are different email templates that are shared across all the tenants. Email template looks like subject: {...
0
votes
2answers
80 views

Extend the ApplicationUser class or use a Person class?

I am making a web system using Asp.Net Core with individual user accounts (Identity), and I was thinking about how I should store the users' personal information in the database. The system will have ...
1
vote
3answers
135 views

How do I make sure a task run only once in a dockerized environment?

I have a task that I need to make sure it only runs once in my dockerized environment (managed by k8s), running the post-upgrade script is one example. Because each dockerized app can run the task and ...
1
vote
1answer
81 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 ...
0
votes
1answer
31 views

Database schema for two different types of elements on map

I wrote a software for a customer that shows clients on a map (Google Maps). I store the clients on a table on the MySQL database (table clients) and show them on the map using markers. Each clients ...
1
vote
0answers
37 views

Looking for the right design (pattern?) for my webapp and external APIs intgerations

I'm working on a simple CRM (Customer Relationship Management) webapp (Flask and Peewee ORM) and I found myself in a situation where I believe that I should have used a design pattern that I may not ...
2
votes
0answers
29 views

Populating form options in dynamic web form

I am looking for advice on database design for a web app I am building. In the web app (Angular, REST API, Oracle SQL database), I am constructing a web form dynamically. I chose dynamic form ...
0
votes
1answer
64 views

Displaying various subsets of content stored in a database

I am looking for advice on database design for a web app I am building. Part of the website needs to display content from a SQL database dynamically depending on the component's context. To be more ...
3
votes
3answers
282 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 ...

1
2 3 4 5
25