Questions tagged [database-design]
The development of the conceptual schema and/or the logical model and/or the physical settings of a database.
6,226
questions
-1
votes
2
answers
29
views
Understanding Secondary Log Hard Disk Impact on Write Speed and Data Integrity After Power Outage
I've recently come across the notion that a secondary log hard disk can be employed to enhance write operation speed in a database system. I find this concept intriguing but am struggling to grasp the ...
- 15
0
votes
1
answer
17
views
Seeking Guidance on Storage Models for Frequently Executed SQL Statement
An application frequently executes the following prepared statement in SQL:
SELECT AVG(StockUnits)
FROM PRODUCT;
I'm currently exploring the most suitable storage model for the database architecture. ...
- 15
0
votes
3
answers
45
views
Modifying PostgreSQL Table for Handling Multiple Delivery Modes
I'm working on a project where I need to modify the PostgreSQL table structure to handle multiple delivery modes for an ordering system.
I have a table named seller with columns like id, user_id, ...
- 1
0
votes
0
answers
35
views
Balancing Legacy CRM Needs and Security in Multiple Databases
I have a set of databases on a server.
Database_alpha
Database_beta
Database_gamma
Database_delta
Database_alpha houses a legacy CRM system which has an inbuilt dependency on the sa account to ...
- 101
0
votes
1
answer
45
views
How should I design my databas for online voting web app?
Currently I'm trying to build web app for online voting where registered user can create their own Election.
For the database, every time user create an election should the system create new required ...
- 1
-4
votes
0
answers
25
views
database 7777777 [closed]
A book is identified by its ISBN number, and it has a title, a price, and a
date of publication. It is published by a publisher, each of which has its
own ID number and a name. Each book has exactly ...
2
votes
1
answer
67
views
Enforce unique constraint without denormalization
Option 1
I have a database schema that looks something like this:
https://dbfiddle.uk/JKCTjadD
A Task belongs to a Service (like in ECS)
A Task can be reassigned to another Service.
TaskConfig tracks ...
- 121
0
votes
1
answer
33
views
MySQL and PostgreSQL database table schema comparison
Is there any direct method to compare tables between MySQL and PostgreSQL.Comparing should include field name, types, default values, etc; As an example below,
Below is a dump of PostgreSQL
...
0
votes
1
answer
148
views
How can I simplify the process of importing only changed data?
Background
Two years ago, I asked a question about how to model a food's relationship with its nutrients. Today, that design has evolved. The biggest change is that whenever a food is updated, we ...
- 53
1
vote
2
answers
34
views
Database Design For Authorization Of An Relation
Maybe the title of the question is wrong but I could not find a different way to put it. I am trying to create a term project for my database class. Here is my diagram
Here you can see the teacher ...
- 11
1
vote
1
answer
64
views
Database design, one to one relation split into multiple tables
Let's assume we have following database:
Or in pseudo sql format (all columns are not null):
create table road (
id int PK
)
create table item_on_road (
id int PK
road_id int FK road.id
...
- 135
-3
votes
1
answer
60
views
How to design database system of an online toy shop?
Hi i'm designing a database for a toy shop this shop offer many products with different categories. This is the project question. Can you please help me which tables and relationships should I add in ...
- 1
1
vote
1
answer
54
views
building this not-so-necessary relationship is a good approach?
I created a database for an app, where I have several networks, which have several nodes and elements and also several scenarios, all related to the networks. [see picture below]
My question is: since ...
- 113
0
votes
0
answers
22
views
User types as foreign keys
Largely theoretical question or idea discussion
Lets say we have several simple dictionaries:
create table dic1(code integer primary key, description varchar(256));
create table dic2(code integer ...
- 645
-1
votes
0
answers
38
views
what violates the first normalization rule
I was convinced that what violated the first normation rule was having a multivalued column.
id name friends
1 'john' 'Maycon, Gabriel'
That has multiple names in the same column.
Compound ...
- 119
1
vote
0
answers
9
views
Data model for web form with different field (question) types
I am currently working on a data model for an application which includes a form / questionnaire.
I have reached a point where I am not sure how to proceed: What is the best way to store "grid/...
- 11
-1
votes
0
answers
24
views
Best way to set up tables for EOD stock database
Im a newbie with postgre, and the course I did focused on querying already existing databases, not creating a database from scratch.
Im aware that using csv tables might be the best way to go for ...
0
votes
2
answers
55
views
Correct (as in normalized) way to express a relationship between 3 tables with complex(?) constraints?
I'll be using Postgres for the examples, but feel free to show examples in other databases if needed.
The simplified schema:
create table factory (
id serial primary key,
detail text not null
);
...
- 11
-1
votes
1
answer
28
views
OS Upgrade Path for DB and Application
Are there any best practices which define which Operating System Upgrade Path we should choose and any rational behind it?
Path 1: Upgrade Database OS then Upgrade Application OS.
Path 2: Upgrade ...
0
votes
2
answers
58
views
What is the best database design when storing documents of different structures for users?
The requirement is: Users should be able to define the structure of the document they want to store.
For example, a user can decide to store a bank statement with the following structure:
post_date (...
- 3
0
votes
1
answer
41
views
Postgresql near real-time
Before asking the question I will update you on the situation I face.
I have physical tables in postgresql that are continuously updated by various external flows (.NET, Java, etc...), at the moment ...
0
votes
0
answers
27
views
How to stablish a partitions & indexes stategy in Postgres for 100s of milliion rows
For the purpose of learning, I am playing with Postgress. In particular partitions and indexing.
The Tables
Let's say I have the following tables on my system:
webs
web_id --> BigInt
web_tld --> ...
- 51
1
vote
2
answers
65
views
Should a frequently-growing log file be stored as a table or a blob in the database?
I'm an undergrad and haven't worked with any actually database yet so my apologies if I misunderstand/misuse any terms. My question comes from an assignment of mine, but this is my attempt at learning ...
- 13
0
votes
0
answers
8
views
Polyglot Persistence and Data Redundancy
I am building a project that will have a recommendation model I built with TensorFlow.
I am using MongoDB as my main database but I read about graph databases like neo4j and some people say that it ...
-1
votes
0
answers
16
views
How to Model Large Number of Different Objects
I have several hundred types of objects that I will be tracking attributes of. Each of these objects share some properties (e.g. Name, Desc, etc.). However, each of them will have properties unique to ...
- 99
0
votes
0
answers
36
views
one to one relationship in SQL Modeler
USING ORACLE SQL MODELER
I am trying to make Relationship between 2 entities (Roles and Users). The Users table has role_id as a foreign key. The Relationship between Roles(1) and Users(Many) is 1:N, ...
0
votes
1
answer
52
views
Deleting a row in one table should also delete the corresponding rows in another table
I have these tables: station and water_types. The user may only have one station, and if the user wants to delete the station, the corresponding water_types must also be deleted. In a station, there ...
- 105
0
votes
0
answers
24
views
Database Design for Tickets: Single Table with Conditional Fields or Separate Tables
I'm working on designing a database for a ticket management system, and I'm looking for guidance on the best database schema for my use case. The system allows tickets to be created by either a ...
- 1
0
votes
0
answers
10
views
Data Modeling Problem - Schema to store approval process
I need to design a schema to keep track of data changes (I do not need Change Tracking or Change Data Capture or Temporal Tables) to columns in a few tables. When the data is created, modified, or ...
- 368
0
votes
2
answers
112
views
Database design for mapping users to questions
I'm currently building a quiz app and I need to design a database schema for it.
This is what I've tried so far:
The biggest problem I have is with how to represent which questions have been already ...
-1
votes
2
answers
67
views
Database tenancy model
I'm redoing a legacy system, and currently, I have a database with the same structure for each client. I'm really unsure whether I should maintain it this way for the new system or if I should create ...
- 1
0
votes
1
answer
38
views
Modeling a data processing system for consigned employees
I'm currently in the process of developing a payroll system using SQL Server and PHP. However, the system involves a multitude of processing rules.
During this processing, I may receive files ranging ...
- 1
0
votes
0
answers
32
views
How to Preserve Past Order Details Amidst Customer Information Changes?
I'm facing a scenario in my e-commerce setup where after an order is marked as Done, any subsequent changes made by the customer to their information (like shipping address) are reflected incorrectly ...
- 101
0
votes
1
answer
25
views
Modeling a database for business that require several roles and privileges
Part I
I have a database model with members and organizations with each organization has many members, so it is clear that is a one-to-many relationship. The difficulty is that among those members ...
0
votes
0
answers
18
views
ETL process between two Postgres Databases
I have two postgres databases, let's called them layer 1 and layer 2, that both are hosted on a separate servers. In between, there is a third server, that first, loads tables from layer 1, second, ...
- 101
0
votes
0
answers
7
views
Ternary relationship in ER diagram
From a text description I have to draw the ER diagram of a database for blood donation department of a hospital. I struggle to illustrate one information:
"A patient receives blood from a ...
- 113
0
votes
0
answers
14
views
How to optimize a system with balance and in-out TXs to avoid re-calculating user's balance?
I work on a service wherein a user will have balance, will be able to deposit and withdraw money, perform actions that generate and burn it. As the amount of the transactions grow, the amount of them ...
0
votes
1
answer
23
views
MYSQL transactions and guaranteeing inserts/deletes/updates
I have a stored procedure called processWinningBet. It takes a pendingBet and does these things.
update balance table for user
creates a row in historic bets table
deletes the corresponding row in ...
- 11
-1
votes
1
answer
12
views
How to map question to chapters in a examination system?
I am going to create an multiple choice based examination system. I am new to database design and want your help to answer a few questions.
How to model questions and questions category?
Context:
...
- 101
0
votes
1
answer
21
views
How to model a Multiple Choice Question Database?
I am going to create an multiple choice based examination system. I am new to database design and want your help to answer a few questions.
How to model the questions and answers?
Each question will ...
- 101
0
votes
0
answers
29
views
How to implement a relationship between 2 tables where the type of participation is mandatory for both?
Found the example below in a book teaching logical database design, but I'm stuck on how one would implement this in PostreSQL:
The diagram reflects the following business rule:
An instructor must ...
- 111
0
votes
1
answer
64
views
Looking for a better design approach while migrating to Oracle from SQL Server having too many databases
We are in the process of migrating from Microsoft SQL Server to Oracle. In SQL Server we have 60 databases(like XMLDB_1,XMLDB_2,XMLDB_3, ...... , XMLDB_60) to insert data after reading 60 XML files at ...
- 5
0
votes
0
answers
23
views
Database Design Suggestion for Ordering System
This migration was made using Prisma ORM and using MYSQL as the main database. This schema is exclusively made for an ordering system
I would just like to ask for suggestions on whether or not the ...
0
votes
0
answers
13
views
Advice on MongoDB data modelling
I have a schema written in mongoose, I want suggestions on how to improve this schema to scale.
members: [{
user: {
type: Schema.Types.ObjectId,
ref: 'User',
},
roles: [...
- 1
0
votes
1
answer
49
views
Which database design is better for products and their packaging?
I have 2 options for making a database. Which one could be more future proof and why?
Use-case:
We have a shop with different products like 50. They are categorized into 6 different types of packaging ...
0
votes
1
answer
54
views
What Databases would be recommended for historical data with over 500M rows and data ingested daily? [closed]
I'm wondering what Databases would best be suited for storing historical data for multiple clients with over 500M rows and incremental data ingested daily per client? Since data would be used for ...
- 1
1
vote
0
answers
84
views
Which is the best way to model different types of people and relationships in a database?
I have a scenario where I have two tables, person_natural and person_legal.
Case 1:
I have employees, farmers, customers, and suppliers. Should I have an enum in each table (person_natural or ...
- 19
0
votes
2
answers
60
views
Which one is better, the OR operation or JOINING two tables in MySQL?
I have a table let us called table_A. This table has a field let us called Entity. This field could have either zero, one, or two values for each row. Min is zero, max is two, and most rows have ...
- 218
1
vote
2
answers
75
views
better database design for nested relation (many-to-many-to-many?)
I am a student learned about database theoretically, not as practice.
But now I have a chance to design database while doing this little team toy-project.
Here is a diagram (omitted 1:1, 1:n, n:m) as ...
- 113
1
vote
2
answers
75
views
Separate small column updated a lot from large jsonb columns to optimize performance?
A table has the following structure:
a: id
b: large jsonb
c: large jsonb
d: timestamp
Looking at stats in 2 weeks, see many updates:
90k requests updates the d field alone (total time: 25min ...
- 235