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
-5 votes
0 answers
33 views

For Instagram, why is a wide column database used to store the list of user photos? [closed]

I'm reviewing common system designs for Instagram. Many popular solutions, such as this one say that you should use a wide-column store to track the list of the user's photos. The UserId would be the ...
user avatar
-1 votes
1 answer
28 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 ...
user avatar
0 votes
1 answer
34 views

Mixing static referred and dynamic data in database

I’m making an inventory for a game using nosql (mongo). I made some ItemType records (contains common data like icon, name, weight) and some ItemInstance records (representing specific items, ...
user avatar
0 votes
5 answers
118 views

Metadata persisted or created at runtime?

Let us say I have an entity House that I want to store on a database. The House has an attribute yearBuild. If the house was build before 1990 its status should be 'ToBeSelled'. Now I have two ways to ...
user avatar
0 votes
1 answer
52 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 ...
user avatar
  • 109
18 votes
3 answers
7k views

Are surrogate keys a known anti-pattern?

My workplace's database has a pattern that I've not seen before. Every column that is intended to be a key, whether primary or foreign, ends in _SK. This is shorthand for "surrogate key". It ...
user avatar
  • 355
0 votes
2 answers
102 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, ...
user avatar
  • 1
-1 votes
1 answer
168 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) ...
user avatar
  • 69
0 votes
1 answer
121 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 ...
user avatar
  • 9
1 vote
0 answers
33 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 ...
user avatar
0 votes
1 answer
54 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-...
user avatar
0 votes
0 answers
46 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, ...
user avatar
-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 ...
user avatar
  • 99
0 votes
0 answers
34 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. ...
user avatar
  • 1
-3 votes
1 answer
77 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 ...
user avatar
  • 151
-1 votes
2 answers
85 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 ...
user avatar
  • 153
0 votes
1 answer
52 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 ...
user avatar
  • 13
1 vote
3 answers
107 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 ...
user avatar
0 votes
1 answer
70 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 ...
user avatar
  • 101
0 votes
1 answer
93 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 ...
user avatar
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 ...
user avatar
1 vote
1 answer
107 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 ...
user avatar
  • 27
-2 votes
1 answer
56 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 ...
user avatar
1 vote
0 answers
147 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 ...
user avatar
  • 21
1 vote
0 answers
34 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: ...
user avatar
  • 259
0 votes
2 answers
96 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 ...
user avatar
0 votes
0 answers
29 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 ...
user avatar
-3 votes
1 answer
122 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 | ...
user avatar
  • 149
0 votes
0 answers
28 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 ...
user avatar
0 votes
1 answer
99 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 ...
user avatar
0 votes
1 answer
103 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 ...
user avatar
-3 votes
1 answer
41 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 ...
user avatar
  • 1
-1 votes
2 answers
157 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 ...
user avatar
  • 1,487
0 votes
1 answer
99 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 ...
user avatar
1 vote
2 answers
29 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 ...
user avatar
  • 57
-1 votes
1 answer
85 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 ...
user avatar
1 vote
3 answers
173 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 ...
user avatar
0 votes
1 answer
143 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 ...
user avatar
  • 3
0 votes
3 answers
81 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 ...
user avatar
0 votes
2 answers
49 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-...
user avatar
0 votes
0 answers
27 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 ...
user avatar
0 votes
1 answer
265 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 ...
user avatar
  • 260
-2 votes
1 answer
181 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
-2 votes
2 answers
128 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&...
user avatar
2 votes
1 answer
82 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 ...
user avatar
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 ...
user avatar
0 votes
1 answer
144 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’...
user avatar
  • 101
4 votes
2 answers
930 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 ...
user avatar
  • 238

1
2 3 4 5
26