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

Dependency of program code on specific database values

I have two approaches to solving a problem but I don't know which one is better. I will give a simplified example. First approach The database will look like this: color | -------- FFFFFF | 0000FF ...
0
votes
1answer
59 views

Mapping of time based models to database

Currently I'm developing a movie reservation system for diving into Spring Framework. I'm using Spring Data JPA for database modeling. I have several model classes but I am stuck with time-based logic ...
-2
votes
1answer
56 views

Design a database with multiple relations and tables

I'm struggling to find how to correctly design a database in order to store and retrieve data for my software. Basically, I'm designing an application for a gym (C++, MySQL with PhpMyAdmin): each ...
0
votes
0answers
79 views

Creating group/applicaton/user permissions around mongodb documents

I'm trying to find a good way to manage permissions for a high number of mongo documents. What I want to do: Apply group/user/and or application permissions to mongo documents. Idea 1: Use mysql ...
1
vote
1answer
2k views

SQL Database schema for Catering/Menu management

Background I'm building a Private Chef booking service where you can book a Chef to cook you a custom Menu. I'm having trouble creating a SQL db schema that accurately represents the domain while ...
-3
votes
1answer
55 views

Saving large files like Credit Reports in Data Store - SQL/NoSQL/ObjectStore

I am working on a project to save external credit bureau reports in database. These reports are typically big and could go from 0.5MB to 5.0 MB. Number of files will grow exponentially over the time ...
-2
votes
1answer
267 views

Adding unstructured data to a relational database (PostgreSQL)

I'm building a project and I only know the MERN stack. Everywhere I see the general consensus is that Mongo is not good and should only be used in very specific use cases. More importantly, I also ...
1
vote
1answer
93 views

Should I perform some minor denormalization to save myself several queries in the future?

I'm in charge of designing the entire backend for the REST API of an application that works more or less like an online browser game (think OGAME, Travian, and the likes). In this game, players are ...
-1
votes
1answer
155 views

Facebook sharding by user_id or by photo_id?

I'm reading a design suggestion for facebook/instagram social network in grokking the system interview (closed content :( so I'll describe the relevant part in my question). They are speculating over ...
0
votes
1answer
60 views

what extra benefit do I get from a caching layer over the caching layer of the database?

I'm looking at the design of dropbox's edgestore. It manages several thousands of MySql instances, where all dropbox's metadata resides (users, filenames, etc.). I understand why sharding is ...
1
vote
1answer
43 views

Data integrity on polymorphic association to two different table

Let's say I have a very large student list want to filter if each of these students is satisfying some specific classroom school and classroom limitations. So, in my data model, I have School and ...
1
vote
0answers
34 views

Partial database synchronisation across multiple databases

Currently we are looking for a solution to sync databases across multiple locations. We a location hierarchy such as Country -> State -> District -> Center. To increase speed and reliability of the ...
-2
votes
1answer
76 views

What is the best way to store secure static data which can be easily updated by clients?

I'm currently building an education website which contains a lot of content such as questions, videos and notes which all need to be securely locked away and exposed to users with relevant permissions....
1
vote
2answers
87 views

What's the best convention to store "other" option?

Say I have a Person object. I need to ask the user to choose from a list, which laptop they have. They can also choose the option "My product isn't listed here". Now the Person object will look ...
1
vote
1answer
86 views

Moving a module out of monolithic database using microservice patterns - challenges

We have a large monolithic database. As part of a new requirement, I am proposing to move our financial transactional system into a new separate database via microservice-esque services. These ...
2
votes
2answers
994 views

Storing unchangable data in database vs in code

Hi I am trying to create a role based dynamic route authorization system on my NodeJS ExpressJS powered API server. Scenario: There will be some roles like Admin, StandardUser etc. and they can't be ...
1
vote
3answers
424 views

Comparing whether two very large text contents are different or not efficiently

I have a MySQL database with a column Body MEDIUMTEXT. Until now I used to only store the contents into it. There was no update option for the users of the application. Now, I wanted to add an update ...
2
votes
3answers
313 views

Is it good or bad practice to use the user email in two separate columns of the user SQL table?

I'm fairly new to web development and I have a bit of a weird question. I'm currently working on a personal project that may or may not eventually become a real, commercialized product. In my ...
1
vote
1answer
94 views

Denormalized history (ledger) table with tax - yes or no?

We are designing a system, in which we need to store amount of SalesTax applied as well as Tax percentage value. We decided that we will keep tax value in separate table (simplified example): Taxes ...
3
votes
2answers
218 views

Operate on data that doesn't fit into JVM

We have a service where we have billions of key-value data stored in some storage. Before actually querying the data, we query the bloom filter to determine if the key may exist or definitely does not ...
-1
votes
2answers
72 views

Implement Sign Out for Specific Device in REST API

I'm currently developing a REST API which is supposed to be consumed by my App and desktop website. The user should not need to login every single time. Currently I implemented the following: First ...
-1
votes
1answer
245 views

How to store nested data in efficient way in DB

We have daily 200-300 live projects which receive online traffic from our vendors across all our live projects. We store this data in two different DB (I know this sounds bad, but seemed good at the ...
1
vote
2answers
99 views

Database design of a web-app which trains kids to read

The question is about my database design. Is it OK? I feel uncomfortable about having 2 separate WordTraining and SyllableTraining tables and about too simple design (not much normalized). Maybe ...
0
votes
1answer
49 views

data model design of a store net sales and earnings

I've designed my app so it stores the data of each sale in the following way: each order contains different products so for each product in an order a table stores the quantity(of the product) , net ...
4
votes
2answers
159 views

How should a data warehouse be maintained for a quickly changing schema

I am currently in a process of maintaining a data warehouse for a quickly growing start up company. There is a lot of reporting demands from the clients, and this is usually handled by a data ...
1
vote
2answers
459 views

Separate database in microservice architecture

I'm trying to create project siteWeb and mobile app with clean Architecture and Microservices. create identity service with separate database A and make another api service with other database B. for ...
0
votes
1answer
43 views

Ensure that Room No. of a Course's Section is within the permitted Rooms for that Course with some Constraint

I am facing difficulties establishing constraints in the following situation. This is a hypothetical scenario for practicing DB designing. Imagine, a rule in a School states that the Courses there ...
1
vote
1answer
32 views

Storage and partitioning in Cosmos DB

I'm working on designing a system for reporting/storing information on hardware devices (IoT-like), and trying to figure out how that would best be structured in Azure's Cosmos DB. Every device will ...
4
votes
3answers
491 views

optimistic locking vs pessimistic locking

In the context of a multiuser database desktop application, the concurrency problem has to be considered. Many articles focus on two models: optimistic and pessimistic locking. In pessimistic locking ...
1
vote
1answer
228 views

Using the primary key as a foreign key

I have data spread across two tables shipments and shipment_data. Currently I have the standard auto increment primary key id for shipments and a manually assigned unique primary key id for ...
2
votes
1answer
1k views

From Oracle to Apache Parquet : how to handle eventual consistency?

I have an existing production Oracle Database. However, there are performance issues for certain kind of operations, because of the volume of the data, or the complexity of queries. That's why I ...
9
votes
2answers
530 views

How to design microservices with large number of joint entities outside of the domain border?

I'm working on breaking down a monolith application in smaller applications or microservices. Like always, sometimes it's easy and sometimes it's harder to identify domains and split those into ...
1
vote
1answer
48 views

Structuring a db to allow multiple products to hit a select component list

I am designing my first ever database architecture, no real framework to build up on. So I am working on my own! Terrifying. A huge question I have is for having two separate tables, one being ...
1
vote
1answer
199 views

Best approach to handle user statuses and keep their history

I am developing a Spring application which has a few different modules. There will be a bunch of users added in the database. I want to add a feature, which will allow me to track users' availability,...
2
votes
1answer
75 views

Techniques for handling large reporting periods in MSSQL

We have a fairly large database with some fairly large tables (100s of millions of rows). Some of those tables are reported on. We have indexes in place to make this as fast as possible, but still ...
1
vote
2answers
533 views

Is database linked list a good architecture for replies and reviews system when using mongodb?

I want to create a system of user reviews and replies to the reviews in a website. There can be replies to replies. I'm using mongodb database which I think is an important detail. The review ...
1
vote
1answer
50 views

Firebase prefer separate collections over mixed collection?

I'm writing an education app and my current Firebase structure looks like: Course ∟ Lesson ∟ Modules (Includes video, audio, text and quiz modules in one collection) Where "Course", "...
1
vote
2answers
122 views

System design for filtering/sorting objects whose properties are stored in distributed storage

Imagine that we have a storage of objects O (can be in SQL, NoSQL, doesn't matter) where the object contains some property P_1 to P_n. Some of these properties are stored in storage S1, some in ...
2
votes
3answers
143 views

Modeling different types of "Posts"

I have different types of posts a user can create: TextPost ImagePost VideoPost The frontend client needs to retrieve the last 10 posts from a user. I am wondering how to model this structure ...
2
votes
1answer
96 views

Application Security Trimming vs Database Security Trimming

We have a fairly complex Meetings Management solution which would have one meeting to contain different agendas which would contain some tasks and also documents, etc. There are also different roles ...
6
votes
5answers
810 views

Relational integrity without relations

I have about 15 years software engineering experience, writing business software with relational databases. Mainly SQL Server and Oracle. I've always been of the opinion that you should define ...
1
vote
1answer
28 views

Approach to modeling for a new attribute to attach to exactly one of the many in a one to many relationship

Apologies for the awkward title; I wasn't quite sure how to concisely describe what I'm trying to do. My situation goes like this: We have a Product table in our database; Each Product may have 0 or ...
3
votes
2answers
376 views

Database design - maintaining versions of an object

Suppose I've a Person object. This Person object in turn is having name, address, accountNumber and many other fields. Now my requirement is that I want to maintain multiple version for each person ...
1
vote
0answers
162 views

How to minimize errors between teams working on a shared code base

Imagine there are two solutions that each have their model, and data layer separately, and are developed separately on different git repositories, but they both are using the same database. Now, team ...
0
votes
1answer
204 views

Is event-sourcing and event-driven design works in scalable architecture for a wallet?

considering you need to design a simple wallet service that keep tracks of transactions, and give balance for each account. I started designing the solution using event-driven architecture, so every ...
-1
votes
3answers
73 views

Architecture to show the number of results within the filter

I am building a web application connected to MySQL database that is supposed to work like an e-shop. User should be able to filter products, but I want to display the number of results that would be ...
0
votes
2answers
161 views

Hierarchical vs chronological schema for a tree depth of 1

"All modern chatting interfaces, without exception, implement a hierarchical and a not-chronological schema for chat" I was searching for SO answers and came across this: https://dba.stackexchange....
1
vote
3answers
66 views

Having unique identifier for user while Updating user entity (by deleting and adding new one)

I am adding authentication to my web app and I consider the following case: I have USERS table which look like this simplified version Id {int} | Username {nvarchar(100)} | CollectedPoints {int} | ...
3
votes
1answer
328 views

Is it recommended to use schemas on non-relational databases?

I'm trying to figure out if the use of schemas on non-relational schemaless databases like MongoDB is a recommended practice or a bad practice to be avoided. I am doing research work and am looking ...
2
votes
1answer
97 views

Logging temperature for each device in a different table vs all in one table

I'm very new to databases and have no knowledge of how to design them. I've been using ASP.net Core to develop my website/API So what I want to achieve is that remote devices log their temperature ...

1 2 3
4
5
25