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
0 votes
0 answers
13 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
14 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
35 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
9 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
33 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 votes
0 answers
20 views

postgreeSQL performance estimation w.r.t reads and writes per second

Here are all the parameters involved:- instance (aws rds db.t3.micro):- vcpu:2,memory:1GiB, 1 core Image :-PostgreeSQL 15 Tables:- TableA:-(aid (PK),a1(FK to B.bid),a2,..a7) TableB:-(bid (PK),b1(...
varun_k_21's user avatar
-1 votes
0 answers
14 views

How should I design my mongo database having multiple buy/sell orders and requires summary of the orders?

I am using MongoDB to design somewhat like an inventory management system where a user can place orders for multiple items. These orders can be BUY or SELL orders along with the quantity and price. My ...
d_bhatnagar's user avatar
0 votes
0 answers
9 views

Player friendships database design

I'm trying to understand the best approach when storing player friendships in a MySQL database. It boils down to either one of these two "methods". Method A - One shared row per friendship, ...
asl's user avatar
  • 1
-1 votes
1 answer
20 views

Do ER diagrams help directly answer "any" relationship question?

Situation In the image below one can answer relationship questions between team-team, team-play, play-referee very simply. But trying to figure out the relation between team-referee is not trivial, ...
sanjarcode's user avatar
0 votes
1 answer
25 views

How Can I Efficiently Structure a Data Model for Handling Both One-Time and Recurring Tasks in a Task Management app?

fellow developers and architects! I'm in the process of designing a webapp aimed at managing tasks and recurring tasks, which can be seen as habits. The unique challenge I'm facing revolves around how ...
nanobot's user avatar
0 votes
0 answers
14 views

Do I need to populate member id with eg address email?

I am creating a logical model based on the picture I attached. Based on this, do I need to give attributes for example to members? Or can I still create a logical model with just the information ...
Joao Paulo's user avatar
-1 votes
0 answers
29 views

What is the problem with this design? Is it too normalized? Should I introduce surrogate keys?

I have a database that keeps track of email addresses observed on URLs, along with other information about domains (DNS lookups, etc). The queries I do expect to be getting are, "All emails ...
Franz Kafka's user avatar
0 votes
2 answers
49 views

MySQL replica connection not working

I tried to skip an error on the replica: STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE; but still getting the error below: *************************** 1. row *************************...
V S devops's user avatar
0 votes
0 answers
29 views

Database design for application tracking

I am new to the world of DB design and was hoping to get some pointers on the design I have been thinking of using. My goal is to track applications that are publicly listed on a website. Every hour, ...
ldhwaddell's user avatar
0 votes
0 answers
15 views

High volume insert/update for data size of 20 KB on an average

Needed to insert/update 80 million records as fast as possible. However, while write is happening I don't want my active read query to be blocked, the rate of which is around 5k reads per second. what ...
vicky kumar's user avatar
2 votes
2 answers
591 views

Design: What should be my PK, FK etc

I'm new to PostgreSQL and SQL in general and I've got a bit of an issue understanding PKs, FKs an serial id and when to use them. I'll try to add info on what I have already tried so far, but I can't ...
JUHJUH's user avatar
  • 23
0 votes
0 answers
15 views

help with database design for discord bot

I'm a beginner. I'm building a discord bot and i need help with database design for plugins/subplugins. the structure i have thought of. a plugin may or may not have configs, subplugins or values. ...
Sandesh Shahapur's user avatar
0 votes
1 answer
21 views

"Proper" way to design history log

Currently, I have an entity Ticket, now I plan to create another entity called TicketHistory just as the image shown below to track the changes that happened to Ticket entity, I wonder if this is the &...
siunhanbumpbump's user avatar
3 votes
5 answers
832 views

Is COMPACT a better format for fixed length rows than DYNAMIC?

In MySQL / MariaDB I have this table with fixed-length rows (no VARCHAR, TEXT, etc) CREATE TABLE trigram ( id BIGINT(20) NOT NULL, trigram CHAR(3) NOT NULL COLLATE 'utf8mb4_general_ci', ...
O. Jones's user avatar
  • 563
0 votes
0 answers
23 views

When is it most appropriate to use relationships rather than direct embedding in MongoDB?

I'm currently attempting to write some data on MongoDB to store character data, stuff like their name, description, etc. until there it's fine. But I run into situations where to me it's logical to ...
Blastcore's user avatar
  • 103
0 votes
0 answers
12 views

cassandra stress test results showing dropped performance in C* version 4.1.3 compare to 4.0.5

we are planning to upgrade our cassandra cluster running on version 4.0.5 to 4.1.3. Before upgrade, we performed stress-test on both version. steps we followed :- created 3 node cassandra cluster ...
Vivek Kumar 's user avatar
0 votes
1 answer
46 views

Create an affiliate system (also known as a referral program like Rewardful, Tapfiliate) in SQLite?

I want to create a simple affiliate system that pays affiliates who refer a customer a commission of $100. I currently have the following tables generated via an ORM. CREATE TABLE `session` ( `id` ...
deadcoder0904's user avatar
0 votes
0 answers
15 views

Implementing Surrogate Keys in Fact Tables

Can I just clarify a point: Primaries are still necessary to implement a Dimensional Model? As in, the duality of Surrogates and Primaries is incontrovertible? The context is I am researching ...
Geoffrey Turner's user avatar
0 votes
0 answers
52 views

Sql Server migration from old server to new very slow SQLEXPRESS

Hope someone can assist me. I think I might know what the problem is but this is not really resolving the issue and was wondering if it is even a valid conclusion. I am not a Guru at all with SQL. ...
Eduard's user avatar
  • 1
2 votes
1 answer
49 views

How to create unique id in flutter to add in the database

How to create a unique id in Flutter to add to the database in firebase?
Ravin Laheri's user avatar
0 votes
0 answers
17 views

RDBMS vs NoSQL decision making

I am pretty novice when it comes to data store, and want to learn few tricks. I want to know how to decide between RDBMS and a NoSQL store. Lets take example of a system where data size is pretty big, ...
Mandroid's user avatar
  • 123
0 votes
1 answer
25 views

How to implement historical data with this EERD?

I am looking for a way to implement historical data with the following EERD (what I have tried, below the image): What have I tried? Temporal tables Didn't work because I use MySQL and it has no ...
user23600793's user avatar
0 votes
0 answers
56 views

Design question: mapping table with allowed combinations

For a platform measuring different metrics we plan a (Postgresql-)database that lets users add sensordevices to different entities. There sensordevices consist of different sensors. To limit the ...
sebastian.do's user avatar
1 vote
3 answers
87 views

Enforce uniqueness on combination of columns + aggregate function

I have a SQL Server database with the following tables: CREATE TABLE [dbo].[Rule] ( [RuleId] [int] IDENTITY(1,1) NOT NULL, [Value1] [int] NOT NULL, [Value2] [int] NOT ...
Dragos Miron's user avatar
0 votes
3 answers
146 views

How can a column's data type be safely reduced from NVARCHAR(max)?

My biggest table owes most of its size to a stupid NVARCHAR(max) column that I wager could easily be NVARCHAR(4000) or smaller. What steps can I take to become confident that this change is safe to ...
J. Mini's user avatar
  • 139
0 votes
1 answer
38 views

After Insert and Update Trigger on the same table

I have the following table in MySQL: CREATE TABLE `userlogtype` ( `UserLogTypeID` int NOT NULL AUTO_INCREMENT, `UserLogType` varchar(150) DEFAULT NULL, `InsertUser` varchar(100) DEFAULT NULL, `...
Steve Miller's user avatar
0 votes
1 answer
45 views

ERROR: duplicate key value violates unique constraint "sometable_pkey"

I have a giant table (500,000)+ rows with many columns that are varchar yes/no, and I want to reduce the size the db occupies, by changing the data types of varchar (yes/no domain), to int. Then ...
RandomNumberFun's user avatar
0 votes
0 answers
34 views

Associative relationship for "manager working term"

I am creating ERD following some database design requirements. This specific part on the ERD is what I am trying to represent. I made the MANAGEMENT entity because it's related to DATE_ACCEPTANCE, ...
Albert's user avatar
  • 1
-1 votes
1 answer
32 views

Is it mandatory to include every entities mentioned in the description when creating an er diagram

Is it mandatory to include every entities mentioned in the description when creating an er diagram. The description don't give any information about relationship between the entity mentioned before ...
Namii Classroom's user avatar
-1 votes
1 answer
73 views

MySQL design and best design practices

I am designing my first database for a mobile application, which is also planned to be developed into a web application. The photo shows the part responsible for user authentication and information ...
GetReady's user avatar
0 votes
1 answer
40 views

Populating parent table from multiple child tables with compound keys

I'm currently experimenting a use case where I have several child tables (here two for simplicity's sake) that must be referenced by their "type + id" in a unique parent table, and tested ...
s.k's user avatar
  • 362
0 votes
2 answers
35 views

database design for a checklist app (units, inspections sheets, inspection questions)

I am working on a checklist app for inspection sheets for users on our shop floor. It's a paper process currently and they want to digitize it. I am using Postgres SQL for the database. I started out ...
ProfoundHypnotic's user avatar
0 votes
1 answer
32 views

General approach for creating updated records with identical primary keys while preserving existing records

I am working with a database which has some records in it already, and I can't modify the existing tables although I can create new tables. I want to create new records which have the same primary ...
Robert Dodier's user avatar
0 votes
1 answer
41 views

How to migrate 2 different Cassandra databases into one cluster

We have two separate Cassandra clusters, each with six nodes: Cluster A and Cluster B. Our requirement is to merge these two clusters to save costs. Because this is a critical application, we need to ...
Sai's user avatar
  • 35
0 votes
2 answers
92 views

Need help on database design for 5 entities complex relations

What it is Have many stories (with many lines in each). Each translated in many languages. Each language have many accents. I'd want to offer user to choose the languages (can choose many of only ...
Md. A. Apu's user avatar
0 votes
2 answers
66 views

Parallel Structures in database: One table with type field or multiple nearly identical tables

I'm designing a database where I'll have some data that has basically the same structure in multiple places, and I'm wondering if I should make it in 1 table or multiple. For example: I have an Apple, ...
user286116's user avatar
0 votes
0 answers
29 views

How to create a tag system on my Access database?

I work with Access in a very rudimentary way. I use it to store data about events that involves guests and our staff. The amount of people in total is enormous, so it is imposible to add the people in ...
Teusdu's user avatar
  • 101
-1 votes
1 answer
53 views

Should we repeat multilevel dependency reference columns to avoid multiple joins breaking the normal forms for better performance

In general I know repeating information in tables are not considered a good database design But I have a question for following particular design for multi level parent child relationship ...
Pawan Nogariya's user avatar
1 vote
0 answers
28 views

what is the best way to create database index on json cloumn in postgress [closed]

I want to create a database index on json column on field customer_number which has the follwoing data.. { "version": "3.0", "order_header": { "...
Vinaya Nayak's user avatar
0 votes
0 answers
41 views

How to Preserve related Table Entries for a final composition?

Please bear with me if I don't quite hit the right wording for my question, or perhaps don't understand the problem correctly. I have an application where you can take an abstract version of something,...
Slevin's user avatar
  • 101
0 votes
2 answers
48 views

Referencing information from same table in Postgresql

I have a geo table that contains countries localities (city, town, village, island, archipelago) locations (venue/business + boroughs/district/area), e.g - Big Ben, or Southwark Borough. For extra ...
BinaryVeil's user avatar
0 votes
0 answers
44 views

Database design: specifications for different types of products

I have table product that contains all types of products. Each product has a set of specifications unique for that particular product type. For example if the product is smartphone its specifications ...
FAMO4S's user avatar
  • 1
0 votes
2 answers
55 views

Choosing a Database system

I am currently in the process of designing a multi-business system that involves user management, business details, and order processing. I am contemplating whether to store all business data in a ...
Shadow's user avatar
  • 1
0 votes
1 answer
123 views

How to calculate MAX server memory and MIN server memory?

There is a total of 10GB of RAM. I want to set MAX server memory and MIN server memory for SQL Server. How do I set this? Would 20% of the total RAM for the MIN value and 80% of the total RAM for the ...
Merve's user avatar
  • 55
-1 votes
1 answer
93 views

Optimizing Negative Int/BigInt Ranges in SQL Server: Strategies and Benefits

I am currently delving into the potential applications and advantages of utilizing the negative range of the int or bigint data types within SQL Server, with a particular focus on the range extending ...
Ali varzeshi's user avatar

1
2 3 4 5
126