Skip to main content

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
-3 votes
0 answers
28 views

Logical Comprehension Question [closed]

Logical Comprehension Question The main idea or primary purpose Information that is explicitly stated
vikas M's user avatar
-1 votes
0 answers
45 views

Best Practices for Migrating 2TB MSSQL Databases with High Availability to PostgreSQL

I'm planning to migrate a large-scale MSSQL environment to PostgreSQL and would appreciate some advice and best practices. Here are the details of our current setup: Current Environment: Two servers ...
Obada Bakfalonui's user avatar
0 votes
1 answer
32 views

Designing a schema with non-enumerable, unpredictable public ids in MySQL

I'm building a REST API, backed by MySQL. Normally for every table I will have at least one: id UNSIGNED INT NOT NULL PRIMARY KEY AUTO_INCREMENT field as a default, but the issue with this is that it ...
Evert's user avatar
  • 151
0 votes
1 answer
22 views

What impact has uniqueness constraint jsonb on postgres db performance

I'm designing a postgres database in which there is a table that has a jsonb type column. I would like this column to be unique. There is no need to have two objects with the exact same json ...
hasdrubal's user avatar
  • 103
-1 votes
1 answer
33 views

SQL Server Management Studio "Key cannot be null" error when giving a specific user permissions on Stored Procedures

I'm attempting to give a specific user execute permissions on a Stored Procedure. When I attempt to do so, I get an error: Key cannot be null. Parameter name: key (System)" When I attempt to ...
LYNITS's user avatar
  • 1
0 votes
0 answers
54 views

What kind of DB is best for frequent writes / rare reads?

It needs to: Handle around 80,000 writes / second Store billions of rows Store them for many years Consistency is a must Reads are very rare Data is currently stored in an RDBMS (I need to collect ...
Puffin's user avatar
  • 1
0 votes
0 answers
36 views

Simplifying Complex Traversal Patterns Involving Multiple Association Types (1:N, M:N, N:1 ...)

I'm working on a data model for a system that represents interconnected entities with complex traversal requirements. Specifically, I have the following scenario: An EntityA connected to multiple ...
roumail's user avatar
1 vote
1 answer
34 views

How do model different logical views on data

Simplified example of a data modeling problem that I have: I have a domain model with an image table, a camera table, a machine table, a foreign key constraint between image and camera (...
Pete's user avatar
  • 13
0 votes
1 answer
32 views

Using internal or external ids when storing external data

When storing external data, I usually have two ids. One is external and one is internal. I inevitably need to keep indexes on both ids for lookups. Examples: Storing stripe customers, products, ids ...
wongx's user avatar
  • 109
0 votes
1 answer
30 views

Manage the amount of data on tables

I can have several tables with 10+ millions rows in by year. I would to know what's the best approach to maintain responsiveness with this amount of datas and I have two ideas. Don't touch these ...
Matt's user avatar
  • 1
0 votes
2 answers
46 views

Can you have a minimal transactions data warehouse?

Good Morning, I have a client doing scientific research. The current database (Azure postgres) design is 3NF but very few transactions are happening. There are several "pipelines" that add ...
GPGVM's user avatar
  • 103
0 votes
1 answer
21 views

Designing a data model to handle both recurrence and nested dependencies

I'm building a web application that involves both recurrence and nested dependencies, and I'm struggling to pin down the appropriate data model. The problem that the application aims to solve is: we ...
Tobias Fünke's user avatar
0 votes
1 answer
27 views

Mysql problem not valid syntax

reason:Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'почта VARCHAR(50), тип травмы ENUM('...
Godelisk's user avatar
0 votes
1 answer
34 views

Questions get answered by a user, how to store historical data alongside the answer?

I'm building a database where a User can answer Questions, each Question belongs to a Category. Now what if someone changes a question or category name later, I need to store the historical data of ...
Miguel Stevens's user avatar
-1 votes
1 answer
54 views

Database Design for Attendance Management [closed]

Although this question has been asked many times on stackoverflow but none of them fit my situation. I have an application which possibly going to have 2-3 million student records. Each student is ...
KAVYA SHARMA's user avatar
1 vote
1 answer
96 views

Efficient way to store and retrieve big amount of data

I need to store a big amount of data (about 2.5 billion of new data rows per month), but also I need a very fast way to retrieve the latest value per group on specific time point. The data looking ...
the_it_guy's user avatar
0 votes
1 answer
22 views

ideas to reduce association tables while building a data warehouse

I'm trying to create a data warehouse for my BI project. Initially I have a raw database that contains 100 data tables, most of them are association tables. My goal is to create normalized dimensional ...
Jalel MED's user avatar
1 vote
1 answer
17 views

Multiple parents for AspNetUsers table for Asp.Net Core 3.1 app

I have the following table in MySQL.: CREATE TABLE `aspnetusers` ( `Id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `UserName` varchar(256) CHARACTER SET utf8mb4 ...
Hello World's user avatar
2 votes
2 answers
194 views

Transactional Replication - how to set up alerts/notification to send alerts before transaction log space is full on a Publisher?

I am setting up transactional replication on my 2008R2 server to 2012 distribution server to 2012 Subscriber server. All the three servers are on FULL recovery mode. Log backup is taken every 15 ...
Preety's user avatar
  • 27
0 votes
0 answers
36 views

Is using a unique string as key a common practice in databases?

Consider this table from a multi-database, modularized-monolith application: CREATE TABLE `Forms` ( `Id` bigint(20) NOT NULL AUTO_INCREMENT, `Uuid` uuid NOT NULL DEFAULT uuid(), `Key` varchar(...
Saeed Neamati's user avatar
0 votes
3 answers
46 views

in Relational database, will index accelerator query: select count(*) from table where score >= number

Let's say we use MySQL With 100 million records Table schema: userid(pk), score score ranges from 0 to 10^6 score has duplicates So the question is when adding a index on score, will it accelerate ...
ma ky's user avatar
  • 11
0 votes
1 answer
22 views

How to reference uploaded blob objects in database tables

When there are urls in a database table that represent media files uploaded to my own blob storage: Is it common to link a column relationally to an objects table Ex: videoHdObjectId: "XXX"...
wongx's user avatar
  • 109
1 vote
0 answers
8 views

Design: How to store boolean values for entites whose name may change?

Let's say I want to design a DB for an FMCG Manufacturer - and they have several business units, and several factories. BUs: Beauty, Homecare, etc. Factories: SiteA, SiteB, etc. Now, I want a table ...
Somenath Sinha's user avatar
0 votes
1 answer
32 views

Question related to Database Design in SQL Server

I have two tables: Products and ProductVersions. The table structures are as follows: Products Table: ProductId RegionId ActiveProductVersionId isDisabled ApplicationId ProductVersions Table: ...
lifeisajourney's user avatar
0 votes
0 answers
27 views

How to model transliteration (and translation) in a database?

I am working on a project in Django (backed by Postgres) to store manga titles, that will have values that should be translated and transliterated. Since I am starting from scratch, I figured my ...
Rob Rose's user avatar
  • 101
0 votes
1 answer
47 views

Setting up Transactional Replication - Can i safely remove transactional replication if it is causing issues on my publisher?

Please excuse me if I am not making very much sense here. I am very new to replication and have been researching for quite some time about transactional replication to implement in our scenario.. I am ...
Preety's user avatar
  • 27
0 votes
0 answers
19 views

Linking records through multiple tables?

I'm writing a game server. Similar to the Sims. Players can own furniture items they purchase from the catalog. There can also be rooms. Players can be in a room. A room can have furniture items of ...
jacks's user avatar
  • 121
0 votes
1 answer
11 views

Chen ERD: What improvement could I make? Can relationship `RECEIVES` be omitted since entity `INVOICE` is a weak entity of `CAR`?

Assumptions: A salesperson may sell many cars, but each car is sold by only one salesperson. A customer may buy many cars, but each car is bought by only one customer. A salesperson writes a single ...
허지웅's user avatar
0 votes
3 answers
74 views

Designing Tables for Large Amounts of Time-Series Data

I'm designing a Postgres table to store large amounts of time series data and I'm trying to figure out the best way to structure the columns. I've looked at answers such as this one, but since it's ...
Robert's user avatar
  • 123
0 votes
0 answers
31 views

Looking for fast large-scale image embeddings comparison database structure

I plan to store 1-2 million of entities into a database. Each entity will be linked to 10-100 image embeddings. The goal is to use an external image that may or may not be part of the database and ...
miran80's user avatar
  • 101
1 vote
4 answers
125 views

MySQL solution for many to many associative table that scale over billion entries

Scenario Imagine we have a table user and an item user. These 2 tables have an associative table called user_item to define a many to manyrelationship. We start 100 item records We have 500 Millions ...
Federico Baù's user avatar
0 votes
2 answers
33 views

SQL Database - how to design entities to make search operations faster?

I need to design some database entities to store logged events. The events have several properties, among which the message. This message is actually coming from a limited set of templates, with ...
Serban's user avatar
  • 101
0 votes
0 answers
43 views

Additional Information Required During Requirements Analysis for Database Design

I am a student currently Studying in Financial Technology in Kitchener. so I have an academic assignment focused on database design, and I need some expert advice. As part of my assignment, I need to ...
nanda vihari's user avatar
-1 votes
3 answers
142 views

How are columnar databases faster for querying?

Why do so many people prefer columnar databases? To my understanding, people prefer columnar databases because they are faster for querying due to the fact that the cpu would be able to cache the next ...
KoalaMaybe's user avatar
1 vote
1 answer
47 views

Unique constraint for single structure that simulates relations between tables

Imagine I have several tables in SQL Server 2016: Cat Cat carrier Cat transit box Cat car Every object can be stored in higher level, but not necessarily directly one above with N-to-1. So a cat can ...
Macok's user avatar
  • 11
0 votes
2 answers
58 views

How to calculate revenue by sales team if sales agents change teams?

Agents ID | Agent 1 | A 2 | B Teams ID | Team 1 | AA 2 | BB Memberships ID | AgentID | TeamID | Start Date | End Date 1 | 1 | 1 | 2023-...
simpatico's user avatar
0 votes
1 answer
63 views

How to implement an "Apple or Orange but not both" constraint

A database schema design question: Suppose I want to design a database with Robots, Technicians and Sites. I want to use my database constraints to limit the data to prevent entering some nonsensical ...
rich p's user avatar
  • 233
0 votes
1 answer
45 views

Table design for handling counter

i have requirement reward will give to user but reward have some condition a. reward can have limit hourly , daily , monthly here is table maintaining user reward tracker CREATE TABLE `...
Rahul Kumar's user avatar
1 vote
4 answers
109 views

are my expectations for mysql unrealistic?

I just made my first mysql database and table, but was surprised by slow performance of a simple select statement. My table has 400 million rows, and my select statement returns about 100,000 rows, ...
Trevor D's user avatar
-1 votes
1 answer
27 views

problem of correspondence between scheduled shifts and actual shifts

The employee has a strong key. On the one hand I record the shift calendar of each employee (shift calendar) and on the other hand I record the shifts that were actually carried out by the employee. ...
Domenico Lugubre's user avatar
0 votes
0 answers
42 views

Designing a schema for data aggregation

First of all, I'm mainly looking for ideas and approaches that I can look into when it comes to this design, as opposed to complete solutions. I have an application that needs to show an aggregate of ...
aarnaut's user avatar
  • 101
0 votes
0 answers
29 views

Normalizing a table with all info into different tables

Let's say I have the following table without normalization: Design 1: Without normalization: Design 2 : Considering Normalization Here are some of the tables that are possible to come up with based ...
Tan's user avatar
  • 113
-1 votes
1 answer
50 views

Customer Address Design Probelm

I'm designing a database for E-commercial. So the Customer have many address also 1 address also has many customers owned so I have another CustomerAddress table (many-to-many relation). Here detail : ...
John's user avatar
  • 3
1 vote
3 answers
134 views

Database for large matrix

I am trying to find a good database to save a large matrix. The matrix would be something of a 3000x3000. The operations that are needed are fast reads of 1 specific item e.g. row 2 column 30. Another ...
Brecht Vanden Houwe's user avatar
0 votes
0 answers
30 views

Database design for dashboard (statistics of performance)

I have a device (printer) that I can connect to using protocol and query for different statistics. To keep it simple, I get in return jobname and number of total prints for that job. Example: >&...
MyICQ's user avatar
  • 101
0 votes
0 answers
27 views

model for user, companies (departments, teams, jobs)

I want to start a new project and I am not sure which of the DB models to choose (from bellow). I am gonna use Mysql for the BD. The rules are: user belongs to one or many companies one company can ...
calin24's user avatar
  • 101
0 votes
0 answers
33 views

Which databases natively support sum types?

I would like to use database for storing my simple sum type. Here is the model in Rust language: enum Item { Heading { id: String, level: u8, text: String }, Paragraph { id: String, text: ...
Max Smirnov's user avatar
0 votes
2 answers
59 views

Does putting foreign keys in another strong entity equal bad design and what is that table?

I see so many tables designed like this: https://www.quackit.com/sql_server/sql_server_2016/tutorial/create_a_relationship_in_sql_server_2016.cfm Where the foreign key is dropped into another table ...
tickmeister's user avatar
0 votes
0 answers
13 views

DB Schema Design to Support Custom Application Flows & Logs

I'm working on a Database Schema Design, where custom fields can be added in the future. I'd like to figure out the following: If adding columns to the existing table as per requirement is good or ...
idk's user avatar
  • 101
1 vote
1 answer
68 views

Limiting the number of rows that can be inserted for a given WHERE clause

In Postgres, I want to be able to confirm users' RSVPs for an event that has limited capacity. How do I do this in a way that protects against race conditions? I have a table event_attendance with ...
Luke Hutchison's user avatar

1
2 3 4 5
127