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,244
questions
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 ...
