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,278
questions
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 ...
- 129
-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/...
Domino
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)....
- 111
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 ...
- 119
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 ...
Lackeeee
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 ...
- 153
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 ...
- 173
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 {...
- 153
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 ...
- 19
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, ...
- 109
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" ...
- 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
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 ...
- 127
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
(
...
- 135
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 ...
- 459
-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, ...
- 111
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 ...
- 991
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 ...
- 121
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 ...
- 425
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 ...
- 61
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 ...
- 139
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 ...
- 318
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 ...
- 119
-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