Software Engineering

Questions tagged [database-design]

Ask Question

For questions about structuring the data within a database. How to lay out tables, whether to use a relational DB or not, etc.

1,076 questions
Newest
Active
Bountied
Unanswered
Filter by
Sorted by
Tagged with
-4
votes
0answers
28 views

Most efficient database for the location based statistics

I would like to create a simple application where I will show the tourists counts of a particular location with the tourist origin. For example Spain got 20 million visitors from which 5 million of ...
3
votes
2answers
82 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
64 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
39 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
22 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 ...
3
votes
3answers
132 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
119 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
47 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
275 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
46 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
62 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
66 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 ...
0
votes
2answers
98 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
40 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
60 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
128 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
78 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
4answers
572 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
25 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
278 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
70 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
42 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 ...
0
votes
1answer
45 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
70 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
57 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} | ...
2
votes
1answer
111 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
90 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
vote
1answer
80 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 ...
2
votes
2answers
117 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
0answers
62 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(); ...
1
vote
3answers
109 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 ...
2
votes
1answer
50 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 ...
2
votes
1answer
137 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 ...
2
votes
2answers
115 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 ...
2
votes
1answer
154 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 ...
0
votes
0answers
18 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 ...
0
votes
1answer
57 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 ...
2
votes
3answers
403 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....
1
vote
2answers
138 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 ...
0
votes
1answer
51 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 ...
3
votes
4answers
205 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'...
0
votes
0answers
43 views

Scaling Dashboard Metrics and Analytics Using DynamoDB Data

We have an application that uses DynamoDB as its transactional database (OLTP). This is our preferred solution as we need to handle 1000s of key-value reads/writes per second and like the schemaless ...
3
votes
2answers
167 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 ...
1
vote
2answers
114 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 ...
1
vote
1answer
205 views

Triangle relationship in database

I have a problem figuring out the relations between some of my database tables. The scenario for this buisiness case is a b2b buissnes. I have 3 tables: users companies distributors. A Company ...
1
vote
2answers
48 views

Architecture of “tags” for contacts in a marketing system and hard-coded IDs so functions can refer to them

I have these tables: contacts, tags, contact_tags. (contact_tags has columns contact_id and tag_id.) Administrators can manually create new tags via an internal website. Administrators can also ...
1
vote
1answer
151 views

Database Design - Hierarchical Structure [closed]

1 We are trying to redesign a database that currently holds thousands of parameters for different datatypes and currently has a flat data structure. We generate XML files from the database, which ...
1
vote
2answers
129 views

Designing database: split into different tables or group them together?

I am fairly new to database design and now trying to build my own forum-based application. The app will have nested categories as countries > systems > levels > subjects In every subject ...
4
votes
1answer
159 views

Delaying sensitive database operations until additional authentication conditions are met

I have a REST API where authorised users sometimes need to make changes to data about their company. For the most important information (e.g. IBAN details might fall under this category), we need to ...
-3
votes
2answers
113 views

How to structure a database where a record can be a child of a parent or a grand parent record

I have these concepts for a database application: Session, Subsession and File A session can have multiple files A session can have multiple subsessions A subsession can have multiple files A session ...

1 2 3 4 5 … 22