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
0
votes
1answer
625 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
159 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
68 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
117 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
263 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
1k 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
2k 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
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 ...
0
votes
0answers
34 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
85 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
3k 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
154 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
59 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
331 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'...
3
votes
2answers
178 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
123 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
883 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
60 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
810 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 ...
2
votes
2answers
822 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
162 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
130 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
votes
1answer
72 views

Which is better for my use case? A hybrid customer data model or a document store customer data model?

I am trying to get some preliminary insight into the pros and cons of using either a hybrid model (jsonb document-relational) or an all jsonb document store model for customer data in PostgreSQL. Both ...
2
votes
1answer
119 views

Designing the schema for a database of Spanish language words?

For a project I'm working on that will help people learn Spanish, I would like to create a standalone service to handle the retrieval of data about words. For this, I've captured and codified data ...
0
votes
2answers
255 views

What's the best way to save/track change requests on multiple objects in a database?

I have a schema containing contracts, sub-contracts, services offered by each of those and sub-services. I also have a application that allows to extend the duration of the contract and to modify ...
0
votes
1answer
84 views

Are there pitfalls to using only a remote database for a mobile app?

My research indicates that the ideal database design is: Mobile App -> Local Database -> Syncing -> Remote Database using something like PouchDB to do the data replication/syncing. But ...
2
votes
1answer
37 views

Do I need to put foreign keys into a metadata repository?

As I was creating a metadata repository, I came across a doubt. As I'm new to databases, I was wondering if I should add the foreign keys data into the tables that have them (for context, imagine a ...
0
votes
1answer
59 views

What DB design pattern it is: main entity has field with actual value and details are in separate table? [duplicate]

I have PERSONS entity and I should add ASSIGNED_LEVEL (INT) field to it and keep the history who assigned the level, when assignment was done and what documents were used in the assignment. My ...
0
votes
2answers
137 views

Gathering data from JSON fields in a relational database

I'm working on a small e-commerce solution which uses MySQL. At first there was an initiative by the boss to use MongoDB but I suggested relational database because there are lot entities with ...
3
votes
1answer
256 views

Integration database on micro-services

I need some wisdom on this. Currently I'm learning about micro-services architecture, and so I decided to make a simple project in a micro-services architecture. The project I'm building is just a ...
1
vote
2answers
118 views

How to manage item locations in an inventory

I have to manage item locations and life cycle as a new requirement. I'm not familiar in warehousing/inventory/storage systems, so maybe my question is trivial. The task: We have original items that ...
0
votes
1answer
59 views

Should we keep time related audit fields as integers or as timestamps?

We have a PostgreSQL database which we use as an intermediate staging area for our ETL pipelines. In the tables of this database, we have kept the time-related audit fields like creation time and ...
0
votes
1answer
158 views

Event planning application database schema

I am planning to develop an Event Planning Application. Currently I'm in the stage of planning the app structure, for example database schemas. Basically there will be two kinds of users: The event ...
2
votes
1answer
299 views

Design question on synchronization of two asynchronous data streams

I have two async streams suppose- Trip : {tripId, date, city} Bill : {billId, tripId, date, amount}. I need to design a system to get real time aggregated view of following nature: City, TripCount, ...
-1
votes
1answer
159 views

Is my database schema going to work? Tracking git issues

My boss wants an automated weekly report on how many git issues have been moved to and from various "lanes" (pipelines) on a virtual kanban board. I wrote a quick python script to get that data using ...
0
votes
2answers
89 views

Need suggestions for creating field level permissions which can be changed dynamically in an app

This is currently my User-Role-Permission model in the Db schema (excluded other tables to avoid confusion). My implementation goes like this: I have a Customer table and each Role has a different set ...
1
vote
1answer
125 views

How do I test a method which interacts with a database?

So I have a database class which has methods dedicated to specific actions. For example I have below method which accesses and writes to a table in the database: def trackUserPerformance(self, ...
1
vote
3answers
164 views

Architecture/algorithm for unusual recommendation system

There are thousands (or tens of thousands) of possible movie titles. New user enters my website and selects hundreds of titles that he likes. The only single goal of my website is to output the list ...
3
votes
1answer
154 views

One database table that is referenced by almost all the others

I have a relational database similar to the one in the diagram where there is one table called app instance which is the foreign key in almost all other tables in the database. It is meant to ...
0
votes
2answers
255 views

Is it a software engineer's responsibility to structure data as opposed to a DBA?

As a backend engineer at a small company, we're running into a question of whether we need a DBA or another backend engineer (SWE2). The reason why this is a question stems from a difficulty in ...
3
votes
0answers
34 views

How Best To Store Data Structures Around Flow

I'm working on building a simulator for HVAC units. The conceptual model I am working with is basically that an HVAC unit has inputs and outputs, and I need to be able to calculate the outputs for a ...
5
votes
2answers
2k views

When to use an associative table instead of 1 foreign key?

There are plenty of examples of both associative table and one foreign key designs out there, but I can't find clear any explanation of when should each of them be preferred. For example let's assume ...
0
votes
1answer
290 views

Database Design Structure

I am building a WebApp which has a list of Assets. I have a peculiar requirement and I am unsure of the best way to store the data in SQL. Requirement: My Asset Headers currently have a relation to ...
0
votes
1answer
389 views

Bridge/Join Table for a Many to Many Relationship between One Table and Multiple Others

Background I have a relational database an I want to have a table that contains flags. I want multiple tables in my database to have a many to many relationship with the flag table. Options I ...
0
votes
1answer
435 views

Is it good practice to create a static class for database tables calculations?

I need to implement some system (using python pandas dataframe in my case) that parses raw data, then adds on calculated data, and then validates that calculated data (boolean output on some columns) ...
2
votes
3answers
133 views

Many-to-many assignments in user system

I have a few software architecture/design questions related to a technically quite simple problem. We already have all the functionality implemented properly, however from a design perspective it is ...
0
votes
1answer
116 views

How to store data which can be a foreign key or free text

I am not sure how to explain my problem, but basically I have an input field which lets you enter member's name or select one from a dropdown list. If the user has selected the person from the list ...
0
votes
2answers
105 views

How should I update non-maintained database tables between production and development?

I'm working on a Django web application (with a mySQL back-end) that uses non-maintained tables(tables not modified by the web app). However, I have two copies of the data tables (one for production ...
0
votes
1answer
62 views

Use single table with lots of columns or break it out and use Views, when using Entity Framework Core

I am currently building a game series information site and have come to a design issue. I have a table called Abilities that would have character ability information in it. Depending on the game ...
0
votes
1answer
47 views

Temporary users to restrict db access of query?

I am new to database managment, and I am wondering if it is a good idea to create temporary users to restrict access to only a single table. Like having an user named _logs can only (read?) and ...

1
3 4
5
6 7
25