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
2 answers
222 views

How to store queryable 10-100MB BLOBs?

I have read several discussions about storing BLOBs in the database vs in an object storage. What I need in addition though is a functionality for querying these BLOBs. The BLOBs will be immutable ...
-1 votes
1 answer
88 views

Ho do I implement Database per Service pattern in Kubernetes?

Assuming I wanted to implement the Database per Service pattern or one of its related patterns like Private-Tables-, Schema- or Database-server-per-service (https://microservices.io/patterns/data/...
user avatar
1 vote
0 answers
19 views

Optimised MongoDB design for access control at project/organisation level

I'm looking for the optimised approach of designing MongoDB database collections where there are three stakeholders- A User (who accesses a portal). An Organization (which can have multiple Projects)....
1 vote
3 answers
100 views

Triggers and network performance

There are many questions and responses on database triggers, however I have not found any that touch on network performance specifically, in the matter of benefits (or otherwise) of triggers. I have ...
2 votes
2 answers
90 views

Is it a good idea to design a database based on the API response data types?

I am trying to design my database, where i have to save some response data given from an external api as a webhook. This api will return me as well “referenceId” as an identifier, which i will use ...
user avatar
1 vote
1 answer
60 views

SQL - store "like" counts as separate column or infer from a query?

Say I have a feature in a web app where users can create a post and like it. In the frontend the user should see the number of likes a post has. I could store the data two ways: 1. Option 1: A small ...
1 vote
1 answer
82 views

Modeling properties of related entities

I have the following entity relationship. A {state: ON | OFF} => B {state: ON | OFF} => C {state: ON | OFF} So, in this case, B is a child of A, and C is a child of B. Moreover, they all share ...
0 votes
3 answers
127 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
10 votes
4 answers
4k views

How to store a fixed length array in a database

I have a mysql database in which I have drafts, each of which contains exactly 24 players the order of which matters. I am conflicted between having a drafts table with 24 extra columns for each ...
  • 121
1 vote
1 answer
51 views

Database schema advice : multiple tables with the shared columns in postgresql

Let's pretend I'm making a basic twitter clone. We could imagine our database is set up as the following: 1. Table 1 - contains user info {username, password} 2. Table 2 - contains session info {...
1 vote
1 answer
98 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 ...
1 vote
2 answers
268 views

When to use an array of objects or an object of objects in MongoDB Design?

I have a question about the general guidance of how to properly mongodb design: Scenario: I have many collection of objects, all uniquely identified by their contract_address, and i want to be able to ...
  • 13
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 ...
0 votes
2 answers
72 views

Database many to many insert pattern

Suppose I have a DB schema with two tables, a Student table and a Subject table. The relationship between these is many-to-many, so I also have a StudentSubject join table. Now suppose that, ...
1 vote
1 answer
81 views

Is DynamoDB optimistic locking with one version attribute for each field in an item a valid design pattern?

It's common to implement optimistic concurrency control in DynamoDB by giving each item in the database a top-level "version" attribute and only allowing an update of an item to succeed if ...
  • 111
-1 votes
1 answer
43 views

How to merge a lot of smaller tables with single-column differences into a one bigger table?

I have a multiple same tables (14 for now), with a only foreignKey column difference. Some of them are: DatasetGraphTable +-------------------------+----------------------+--------------------+ | ...
  • 19
-1 votes
1 answer
31 views

How to model multiple requirement scenario

I want to store and analyze university course requirements. Lets say, students to register for ABC123 course following requirements should satisfy. Must pass CBA321 Should be taken with XYZ123 Must ...
9 votes
10 answers
3k 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
71 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" ...
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
75 views

Aggregating embedded documents in MongoDB

I'm designing a mongo database and I have a dilemma if I should go for normalization. Let's say that there is a database with images and descriptions. Many alternative descriptions can belong to one ...
0 votes
2 answers
122 views

REST API vs upserts and "updeletes"

I am building an API that (I am deliberately simplifying the schema below to only focus on what is questionable): I have a table that roughly looks like this: CREATE TABLE IF NOT EXISTS some_table ( ...
0 votes
1 answer
64 views

Should the intentional absence of a value rather be represented by value-convention or through explicit flag?

Following context: in a set of metadata that contains a lot of possible fields there exists at least one field (possibly more in the future) that can be empty by intention but realistically will be ...
  • 103
1 vote
1 answer
178 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 ...
-1 votes
1 answer
41 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
49 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, ...
0 votes
5 answers
128 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 ...
0 votes
1 answer
179 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
18 votes
3 answers
8k 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 ...
  • 761
0 votes
2 answers
253 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
-1 votes
1 answer
212 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) ...
  • 71
0 votes
1 answer
161 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
56 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 ...
0 votes
1 answer
219 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
156 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
31 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
40 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
86 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
92 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
91 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
252 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 ...
0 votes
1 answer
80 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
98 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 ...
12 votes
5 answers
6k 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 ...
1 vote
1 answer
138 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
65 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 ...
1 vote
0 answers
220 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
47 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
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 ...
-3 votes
1 answer
128 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

1
2 3 4 5
26