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
1
vote
3
answers
728
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 ...
- 119
2
votes
3
answers
488
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 ...
- 31
1
vote
1
answer
136
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
2
answers
233
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 ...
- 139
-1
votes
2
answers
130
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 ...
- 99
-1
votes
1
answer
308
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 ...
- 115
1
vote
2
answers
101
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 ...
- 121
0
votes
1
answer
54
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
2
answers
415
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 ...
- 151
1
vote
2
answers
595
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
1
answer
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 ...
- 129
1
vote
1
answer
41
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,618
4
votes
3
answers
644
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 ...
- 841
1
vote
1
answer
329
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 ...
- 155
2
votes
2
answers
2k
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 ...
- 31
9
votes
2
answers
748
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 ...
- 215
1
vote
1
answer
49
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 ...
- 113
2
votes
1
answer
499
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,...
- 149
2
votes
1
answer
82
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 ...
- 137
1
vote
2
answers
724
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 ...
- 165
1
vote
1
answer
53
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", "...
- 129
1
vote
2
answers
320
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 ...
- 119
2
votes
3
answers
163
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 ...
- 131
2
votes
1
answer
115
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 ...
- 131
6
votes
5
answers
935
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 ...
- 299
1
vote
1
answer
33
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 ...
- 121
3
votes
2
answers
489
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 ...
- 141
1
vote
0
answers
262
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 ...
- 37
0
votes
1
answer
274
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 ...
- 11
-1
votes
3
answers
76
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 ...
- 101
0
votes
2
answers
192
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....
- 111
1
vote
3
answers
71
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} | ...
- 119
3
votes
1
answer
424
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 ...
- 51
2
votes
1
answer
99
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 ...
0
votes
1
answer
689
views
Optimizing inserting/storing Large amount of data in database
We have a POST API that takes data from the Client and send it to different applications (Payment, Email, SMS). We also store all the data sent by the Client in a database for future analytics ...
- 139
2
votes
2
answers
196
views
Different types of account transactions
I'm working on a system where users have different types of 'Accounts' of the financial type. I'm struggling to come up with a design that works.
Background
A user has 2 types of accounts: A 'tab' ...
1
vote
0
answers
85
views
Practice for modeling class - multiple container relationship
I have a class that could:
Have multiple types of containers
Have multiple types of implementations
and what I did to model so far is:
public interface ChildClass {
Container getContainer();
...
- 162
1
vote
3
answers
120
views
How to make a data warehouse of a single full Database?
After learning about the data warehouse concepts whole day, I couldn't found a source that can actually say when you should not do data warehousing. In most tutorials I read, it says that DW is for ...
- 119
2
votes
1
answer
433
views
Can we have a ternary relationship together with a recursive one in E-R diagrams
I am designing an E-R Diagram for a simple system where there are some users and contests. A user can create a contest or invite other users to the contest who can decide to accept or not.
I am ...
- 39
2
votes
1
answer
2k
views
Optional relationship on ERD diagram
Upon generating an entity-relationship diagram of my database I observed several occurrence like this:
This diagram (in IDEF1X notation) shows the following:
Our team's DB convention is that each ...
- 1,316
2
votes
2
answers
3k
views
Is it allowed to calculate the derived attributes by using other tables' attributes directly?
I tried to build a database for Driver Booking system. Here's brief details:
Drivers have several vehicles and they offers the driving service with them
Drivers will charge different hourly rates on ...
- 31
2
votes
1
answer
156
views
Better practice of workaround for implementing a type field in Java
We have a class in our company's main library as follows:
public class User {
private long id;
private UserType type;
// Getters, setters, constructors, etc.
}
and have several options ...
- 162
0
votes
0
answers
38
views
Building a Feed Handler and Persisting JSON Data to Postgresql
I'm starting a pet project and would like some advice. I need to perform the following:
Scrape JSON Data from a REST API
Save the Data into my Postgresql table
Paginate through the rest API and ...
- 101
0
votes
1
answer
103
views
Combining relational and document database for movies
As a architecture design brainstorm, I am pondering on how should I define schemas for movie database where heavy text searching is delegated to document based database (eg. elasticseach) while rdbms ...
- 103
2
votes
3
answers
4k
views
How can I implement maker-checker principle?
I am looking into ways for implementing maker checker principle for various classes over our company repository. Basically the rules are as follows:
An entity is an object to be seen on a website (e....
- 162
1
vote
2
answers
160
views
Can a wide table be the correct choice in design?
I am relatively new to database architecture and I have been tasked with designing a database that will store 250 values per entry by my group. This is pushing the upper limit for fields in an Access ...
- 21
1
vote
1
answer
74
views
database implementation, how to organize files
I try to build a very small object database, not for production only to learn a bit in my spare time.
I've got a data structure that saves my data in blocks of a specific size und combine them when ...
- 139
3
votes
4
answers
442
views
How to Handle Item Prices Change for Reporting
I am building a Point of sales software with Sql Server 2008-R2. I am now confused about how to handle multiple items with prices change.
Let us say price of Pepsi is $2.0 on 10-09-2019 but today it'...
- 33
3
votes
2
answers
179
views
Most efficient way to return results to a web page with a sorting algorithm
Recently, I've been building a search page where the user can input their search criteria and the server side will use a scoring and sorting algorithm to return the 'best' results first. The current ...
- 41
1
vote
2
answers
140
views
Create a column mostly with null data SQL like
In postgresql I have a table with a list of cities. One row for city. Around 15000 rows. A column is CITY_NAME, now I have to add an english translation of the city name. The problem is that only 10 ...
- 459