Questions tagged [database-design]
The development of the conceptual schema and/or the logical model and/or the physical settings of a database.
6,225
questions
0
votes
0
answers
15
views
Creating a real time data pipeline with transactional DB
I am working in fintech industry, they recently needed a dash-boarding solution for executive , finance and other departments while they also want AI solution to keep track of valid disputes and fraud ...
0
votes
1
answer
37
views
data modeling - alternatives to NULL foreign key?
I am currently working on a browser game. There is a map, each play has at least one village. Each spot on the map is either empty OR used by a village.
I have a map table with a list of all position ...
- 103
0
votes
1
answer
24
views
Table structure where name changes to be retained
Example Situation - order table with client list and products. In below example table , when client name/company changes (company name may change) when updating records need to retain old company name ...
0
votes
0
answers
20
views
design tables with multi prerequisites
I am working on a browser game and currently on the data modelling part. This is a browser game where every player has one or multiple village, with different buildings, and each building can be ...
- 103
3
votes
0
answers
59
views
Best practices for heavily coded data in a relational database and lookup tables
I'm working on converting a legacy system into a more modern web application and I keep finding myself tripping over normalizing the data.
The dataset is quite large; about 30 tables with around 15 ...
- 31
1
vote
2
answers
621
views
one table or different tables for different transactions?
considering an app for online consulation and managing patients. there are 3 types of transactions. 1- users/doctors can withdraw, or deposit money in their wallet 2-users can pay for online ...
0
votes
0
answers
26
views
Database design with split and merge logic
My machine learning project predicts when is the right time to do rail track maintenance. I need to design a table to store the model prediction. The model runs every 6 weeks and produces a CSV which ...
0
votes
0
answers
86
views
How to calculate hitrate of shared buffer in postgresql, using TPC-C workload?
I use TPC-C(benchmarksql 4.0) to test postgres. I set up warehousenum=1000, the whole database is about 100GB, and I set shared_buffers=10GB in postgresql.conf. I also set terminalWarehouseFixed=false ...
2
votes
2
answers
266
views
Ecommerce app design - relation between order and cart
I found many design for ecommerce app where there is a separation between order and cart
where order table has 1n to order_item and order_item 11 to product
and same goes for cart - cart_item - ...
- 121
0
votes
0
answers
44
views
How can I treat food products as unique when my suppliers do not?
Background
I work with a handful of small food manufacturers and store their product information in a database for use in a menu-building application I control. They distribute their product ...
- 53
0
votes
0
answers
37
views
3NF Decomposition and NP-Completeness
I have a question about the computational complexity of the algorithm that allows us to decompose a schema into 3NF.
My book says that the decisional problem of telling if a schema complies with the ...
0
votes
0
answers
34
views
Database design for numeric attributes with different convertible units
I need to store numeric attributes that can be saved in different units with possibility of conversion. The number of attributes is rather fixed, so there is no need to create dynamic solution. I don'...
- 1
0
votes
1
answer
32
views
ER Diagram for Missed Visits
I have to design an ERD for a hypothetical scenario. In the scenario, the nurse at a rural clinic needs to get summary data regarding patient visits so she can reschedule canceled or no-show visits.
...
- 1
0
votes
2
answers
298
views
Auto-increment text primary key in postgres
I would like my content to be searchable by users with a unique 6 characters id (i.e. zab4ac, cba884, ...).
I was thinking of creating a id character(6) primary key column, but then I started ...
- 3
0
votes
0
answers
29
views
Temporal Attribute using supersededBy vs updating the entity?
Back in the day when databases were on physical disks and seek times are slower, I was told that one performance tweak that can be done is to avoid doing UPDATE or DELETE in a table and instead rely ...
0
votes
0
answers
39
views
Is this an abuse of UniqueIdentifiers?
I'm designing a new Sql Server database. Every major table uses a UniqueIdentifier as a primary key. I'm adding in a new data object called "Notebook" which is a universal object that can ...
- 101
-1
votes
1
answer
34
views
Separating two numeric characters from among several characters between two columns
how Separating two numeric characters from among several characters between two columns and subtracting those two numbers separated from each other in SQL according to the figure
sys3....
0
votes
2
answers
66
views
Moving tables to different database within same sql server
There is a SQL server, there are around 100 databases in it. I have to query a few tables from one of the databases. when I query, it's very slow and I think CPU utilization is very high at that time. ...
- 101
0
votes
1
answer
26
views
Query on table A to find the number of overlapping jobs for each job
Table A:
job
start_time
end_time
A
1:30
2:30
B
1:45
2:15
C
2:45
3:45
D
0:50
2:50
Table B:
job
start_time
end_time
A
1:30
2:30
B
1:45
2:15
C
2:45
3:45
D
0:50
2:50
Sample output:
job
...
- 9
0
votes
1
answer
72
views
MSSQL - marketplace database design
Ayo guys! So, I'm trying to design a database for a marketplace where customers can buy and vendors can sell. Pretty simple.
A customer can never be a vendor or viceversa. The challenge I'm facing is ...
- 125
0
votes
2
answers
52
views
Model likes, dislikes and comments in a SQL databse
I'm now creating a website similar to Reddit.
Here, a user can write a post, while other users, including the writer, can comment and/or up/downvote (1 or -1) the post.
And to store the necessary data,...
user275485
1
vote
2
answers
127
views
Does having a specific database for each user in a web app good design?
I have a client who's insisting in a SaaS application to have one table for all users, and then for each user to have a separate database on which we perform CRUD operations based on an authentication ...
- 113
1
vote
1
answer
56
views
Database design suggestion for improving existing design
Background
I am working on an IoT application that deals with sensors, their configurations and their readings. Following are the requirements of the system,
Each sensor can support multiple features....
- 111
0
votes
0
answers
28
views
Looking to improve current database schema
I have recently developed a schema for managing sports-related data, and I would greatly appreciate your opinion on its structure. Additionally, any advice or suggestions you may have for improvement ...
-1
votes
1
answer
119
views
How can I avoid data redundancy?
One of the goals of good database design is to remove data redundancy (duplicate data). How can this be achieved during the design of the database?
0
votes
0
answers
30
views
Database design for ID (RFID) card access
One of the system design interview questions I got many years ago was to design an ID (RFID) card access system. I don't remember how I answered the question back then but I attempted a solution ...
- 101
1
vote
1
answer
44
views
Database Schema design - Sparse matrix; when does it make sense?
I was exploring the different reasoning for schema designs. I came across the following scenario. Scenario 1 has a data schema that has a bunch of columns, but the data is sparse mostly nulls (for ...
Raymond Hoang
3
votes
1
answer
66
views
Need help with first ER diagram
This is my first time doing the diagram. My teacher isn't the best at helping out or responding to questions so I've come here to ask for further assistance. I think I've mostly got it correct, but ...
- 31
1
vote
1
answer
1k
views
What are the performance penalties of using text as a primary key in Postgres, if any?
To explain myself, I am creating a database where the users have unique handles (say @johndoe34 ). Users can also follow each other Ig style.
I consider that, since the handle is unique, I can use it ...
- 11
0
votes
0
answers
15
views
Implementing multi-tenancy
I have designed my DB and created all the necessary table, such as company, user, department, etc for a user management system.
Now I need to make changes so that entities/tenants can register in ...
0
votes
4
answers
143
views
LEFT JOIN 3 tables NULL rows not as intended
Scenario - a sailing event with entrant categories.
I am a volunteer and I am struggling to get a result table for each entrant listing their selection (or NULL if none chosen) for each category. ...
- 1
0
votes
2
answers
446
views
ORA-01654: unable to extend index by 8192 with enough free space
I'm working in Oracle 19c DB. Block size is 8k.
I'm trying to insert new data in my table. I have enough free space (according to dba_free_space more than 2 GB).
But I've got ORA-01654: unable to ...
-1
votes
1
answer
22
views
Modeling combinations (options and dependencies)
How would you modelize the following data? I have a solution I wrote in a separate answer. Don't forget to read it.
Context
It's for a website. Speed is of the essence.
Data
You have objects that are ...
- 61
0
votes
2
answers
77
views
Database design for tracking student exam performance at topic level with possibly 10 billion rows
I am developing a test/exam solution where We have around 100K questions (MCQ / Objective Type). Each question belongs to a topic and each topic belongs to a subject.
Students practice these ...
- 101
0
votes
0
answers
11
views
Azure SQL How to Check Partition Logic
I have table where i have partitioned it by day , my table
CREATE TABLE test
(
date DATE,
/// other fields
) on dailyPartitionScheme(date)
When i do the query
Select * from test where ...
- 101
2
votes
2
answers
56
views
Azure SQL Design Recommendation
I have a use case where I have a job that inserts around 30 million rows every hour. It's the kind of snapshot we are taking for some data source and we are interested only in the latest snapshot for ...
- 121
0
votes
2
answers
211
views
Deadlock while concurrently updating multiple rows on the same table
We have a database where concurrent kafka streams are updating multiple rows at the same time, We are seeing deadlock occur in very few cases and I am not entirely sure what's causing it
--------------...
- 3
0
votes
1
answer
33
views
Should I create 2 different databases if they would keep 70% similar data?
I am writing a microservices based NodeJS-Cassandra application and I haVe few services they would need 70% similar data( like username, avatar, videos, etc.) and I am just wondering to know is it a ...
- 119
1
vote
2
answers
53
views
Best way to store millions of measurements with hundreds of columns
I work with infrared spectrometers (several hundred of them) to analyze chemical compounds, and I am looking for an efficient solution to store the measured data so that I can process it later on.
...
- 11
0
votes
0
answers
26
views
Save Japanese string to the mysql 8.0 database
To accept Japanese strings and save them in MySQL 8.0, I setup the field with the utf8mb4_0900_ai_ci collation, but on some strings, it returns the "Conversion from collation utf8_general_ci into ...
- 1
1
vote
1
answer
353
views
Database design/schema for position hierarchy approval process for leave management
How do I design a database so that when a lower employee (associate) requests a leave that needs to be approved by their superiors the request will find its way up to them?
Any employee can request a ...
-1
votes
1
answer
41
views
Guidelines on learning database internals
I have been a developer for the past couple of years and now i am interested in learning more about the internals of database systems. I figure one of the best place to start is to do a deep dive into ...
- 121
0
votes
1
answer
105
views
Table design for a simple event driven architecture - order workflow
I am looking for an idea for the DB / Table design for an async application communicating via message queues.
There is a food delivery application. In order for me to say the order is complete, the ...
- 103
0
votes
1
answer
96
views
Is it correct to use 2 instances of Redis server one with persistence while the second with no persistence?
I'm starting a new project. In this project I'll use Redis to store some data which must be persistent. For these data I'll use Append Only File (AOF) persistence.
In the project there are 2 ...
- 101
0
votes
1
answer
125
views
Set column value to different randomly chosen values from another table
My goal: Set PID_FM in every row to a randomly picked value out of all the values matched by the subquery.
However the FM_ID is only picked once, and therefore the whole column has the same value. I ...
- 1
1
vote
1
answer
67
views
Bidirectional self join table
Consider:
CREATE TABLE object (
id integer NOT NULL DEFAULT nextval('object_seq'),
...,
CONSTRAINT object_pk PRIMARY KEY (id)
);
CREATE TABLE pairing (
object1 integer NOT ...
- 61
0
votes
0
answers
32
views
Composite foreign key relationship with primary key in relational database
I have these two tables:
Table1
(
FileID,
...
)
Table2
(
FileID,
DataID,
UsefulData
)
The PK for table Table1 is FileID, and for Table2 are FileID and DataID.
I think I can't create a FK ...
- 1,843
0
votes
2
answers
100
views
Should I include a junction table with a primary key in my ski rental database schema?
I need to make the ER diagram and the relational schema of the Ski rentals. I have an Customer, Rental and Ski as a entities as well as their attributes. Customers and Rentals should have the one-to-...
2
votes
0
answers
37
views
Modeling strictly referential relationships to third-party databases
To be clear, I mean to ask this question from a strictly "Data Modelling and database-design, including referential-integrity" PoV.
I am currently designing a database that will serve as an ...
- 121
0
votes
0
answers
27
views
Model resource ownership in a flat structure
I have a database model with different entities related to each other, but in the end they all have to be 'owned' by the master entity which in this case is the Organization.
I was wondering if it is ...
- 101