Questions tagged [database-design]

The development of the conceptual schema and/or the logical model and/or the physical settings of a database.

Filter by
Sorted by
Tagged with
-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 ...
cricket900's user avatar
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. ...
cricket900's user avatar
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, ...
Maine's user avatar
  • 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 ...
betelgeuce's user avatar
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 ...
xedox_za's user avatar
-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 ...
Janaaaaaaaa j's user avatar
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 ...
riwu's user avatar
  • 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 ...
chinthakaS's user avatar
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 ...
Check12's user avatar
  • 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 ...
asimtot's user avatar
  • 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 ...
ZiiMakc's user avatar
  • 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 ...
Galaxy's user avatar
  • 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 ...
oakca's user avatar
  • 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 ...
White Owl's user avatar
  • 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 ...
Diego Alves's user avatar
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/...
antri11's user avatar
  • 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 ...
AndysPythonStuff's user avatar
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 ); ...
Cidos's user avatar
  • 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 ...
Veeru Sharma's user avatar
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 (...
zor's user avatar
  • 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 ...
Luis Di Matteo's user avatar
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 --> ...
Javi M's user avatar
  • 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 ...
Cydennn's user avatar
  • 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 ...
allennghayoui's user avatar
-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 ...
Jacob K's user avatar
  • 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, ...
datascinalyst's user avatar
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 ...
JS3's user avatar
  • 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 ...
pi3o1416's user avatar
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 ...
DBK's user avatar
  • 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 ...
Natural Unintelligence's user avatar
-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 ...
flvsouza's user avatar
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 ...
Flavio's user avatar
  • 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 ...
Ashwin Jacob's user avatar
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 ...
AmineBinOthman's user avatar
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, ...
Ai4l2s's user avatar
  • 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 ...
Ele975's user avatar
  • 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 ...
nicholas.fowler's user avatar
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 ...
T M's user avatar
  • 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: ...
lorem1213's user avatar
  • 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 ...
lorem1213's user avatar
  • 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 ...
toraritte's user avatar
  • 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 ...
to-find's user avatar
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 ...
Daniel Evans's user avatar
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: [...
Badri's user avatar
  • 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 ...
GraniteFastNet's user avatar
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 ...
izuu's user avatar
  • 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 ...
pvncoder's user avatar
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 ...
Niyaz's user avatar
  • 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 ...
bFur4list's user avatar
  • 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 ...
Slim's user avatar
  • 235

1
2 3 4 5
125