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,043
questions
-6
votes
0answers
47 views
need to help for designing a complex pricing application and its database
I have to develop a web application , to the calculation of the aviation fuels oils prices for all airline company
from the excel Sheet this is the summary:the final price calculation formulas of ...
1
vote
2answers
101 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
47 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 ...
2
votes
3answers
52 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
17 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 ...
-2
votes
0answers
31 views
Suggestions on system design for data scraping [closed]
I will need to scrap data from different property listing sites and be able to do search by name and location.
I do not have much experience in designing the whole system. My initial thought is I ...
3
votes
2answers
157 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
109 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
139 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
43 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
75 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
1answer
59 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
148 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
108 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
vote
1answer
24 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 ...
1
vote
1answer
96 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
0answers
45 views
Storing KPI metrics in database table instead of caching all the time?
I have an application that has different types of data and KPI's that a customer could query, currently I only provide a simple and basic thing that is calculated
each time a web request comes ...
0
votes
2answers
80 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
61 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
29 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
58 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
84 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 ...
1
vote
1answer
91 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
87 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
46 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
37 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 ...
1
vote
1answer
88 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, ...
0
votes
1answer
96 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
54 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 ...
0
votes
0answers
53 views
Effective database design for time card tracking
I'm building a timecard system for a company of ~50 employees. My problem is designing an efficient way of storing this data. Currently, their timecards are done on a table like the one shown below.
...
0
votes
1answer
102 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
2answers
123 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
136 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
160 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 ...
2
votes
0answers
30 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 ...
3
votes
2answers
199 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
122 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
47 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
178 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
119 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
56 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
94 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
39 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
40 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
vote
3answers
165 views
Service Oriented Architecture (SOA): One Database or Multiple Databases?
In Service Oriented Architecture (SOA), should there be One Single Database "with the share as much as possible philosophy" or Multiple Databases by Service/functional area?
I know in Microservices, ...
1
vote
2answers
79 views
RESTful API - Adding nested resources as a query parameter?
I recently had thoughts on handling the flat vs nested architecture when designing a RESTful API.
Would there be anything wrong with having a system where your nested resources can be specified ...
0
votes
2answers
217 views
Best way to match timezone with time offset?
I have a c# solution which uses mssql database to store data.
In my database there is a big table of entities (e.g. accounts) for each of which the TimezoneId field is defined. This field is filled ...
-2
votes
1answer
63 views
Relationship between users and items tables in a database
I have two tables in a database: A users_table and a items_table. I need a way to be able to define which items each user gets access to. I thought to create a third table containing: 'user_id', '...
0
votes
1answer
90 views
ER Diagram in Barker Notation
I engineered an ERD diagram using Barker's notation.
The problem was to make a database for system of water pipes where one node is connected to another using a section (pipe), each node can have ...
0
votes
0answers
32 views
Design a way for multiple and single group mapped to a Role?
Currently I am trying to setup a relationship between user groups and roles.
I am trying to satisfy:
Multiple roles apply to single group
multiple groups can have same role
multiple ...
