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
0 votes
1 answer
360 views

Oracle Single table with json vs set of tables using joins

I'm building a reporting solution. The data will be stored on Oracle database. I expect to get near to several billion data set since i have to keep data for 1 year period. When designing database ...
-1 votes
0 answers
17 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 ...
5 votes
3 answers
6k views

SQL table design for primary keys (best practice)

I have a User table that is made up of the the following columns: ID (Auto-generated GUID) UserName Password FirstName LastName Email AddressID (GUID) This is linked to the table Address (in a one-...
-1 votes
1 answer
50 views

Database Design for Attendance Management

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 ...
0 votes
1 answer
617 views

Understanding many-to-many ternary relationship sets

I am new to entity-relationship diagrams. I'm a bit confused when it comes to interpreting ternary relationship sets such as this: Does this signify that every relationship instance of Party will ...
0 votes
0 answers
19 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 ...
1 vote
1 answer
384 views

Ensure same tenant in a Many to Many relationship

I am trying to draft a very simple data model for a multi-tenant database. So far I have only 3 tables: company: pretty much my tenant account: is part of one company and zero or more groups ...
1 vote
1 answer
398 views

Same primary key for multiple tables vs separate primary keys

Lets assume we have an employee table as below employee id (PK) | name | date_of_birth | age | created_at We have a scenario where we need to add some metadata for each employee and not every employee ...
1 vote
1 answer
616 views

Create user mapping without password how to configure authentication

I am trying to create a user mapping in PostgreSQL without a password, but I am encountering an error that says. local_db=> select * from employee; ERROR: could not connect to server "...
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 ...
57 votes
3 answers
51k views

What is the optimal data type for an MD5 field?

We are designing a system that is known to be read-heavy (on the order of tens of thousands of reads per minute). There is a table names that serves as a sort of central registry. Each row has a ...
1 vote
2 answers
2k views

How to snapshot or version a relational database when data changes?

My system receives data feeds. Each data feed will end up creating inserts and/or updates to most tables in the (relational) database. I need to capture the snapshot of what the entire database ...
-1 votes
1 answer
30 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 ...
0 votes
0 answers
44 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 ...
2 votes
1 answer
1k views

Database model with Users, Resources, Permissions

I am fairly new to the DB design. I need to handle what a user can do on a specific resource. One of the main queries I need to drive is: Get all the cars a user has write permission to. I think ...
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 ...
0 votes
1 answer
944 views

How do foreign keys affect partial and transitive dependencies (databases)

I'm struggling to work out how foreign keys can affect partial and transitive dependencies when normalising databases. I have multiple tables, two of which are job offer and accepted job. in job offer,...
-1 votes
2 answers
43 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, ...
1 vote
1 answer
3k views

How to fix MariaDB commands out of sync, missing expression near ON?

I have the following procedure DROP PROCEDURE IF EXISTS find_transactions_past_5_days; DELIMITER $$ CREATE PROCEDURE find_transactions_past_5_days (IN input_date DATE) BEGIN ...
0 votes
2 answers
456 views

Lead to Order Table design

I have a User table having following fields: id, name, phone, email and an Order table: id, order_name, order_desc, user_id (fk to User) and a Lead table: id, lead_desc, user_id (fk to User), ...
1 vote
2 answers
87 views

How to ensure that a referenced record is unique across a subset of rows defined by a referenced record from another table?

Example situation Let's imagine the following situation : A user can place orders. Orders are composed of items. Shipments are sent to the user. Shipments are comprised of items. In order to optimize ...
0 votes
1 answer
310 views

How to structure database in order to lookup and store post ID's in one PostgreSQL command?

I have a table with user info (each user has a unique SERIAL ID), a posts table (each post also has a unique SERIAL ID, and references the ID of the user who posts it), a following table with two ...
4 votes
1 answer
904 views

Tables for Product Components AND Component Combinations

I have a basic understanding of relational databases and would like to assign a number of attributes to an assortment of products. Products examined consistently have four components: model, fabric, ...
0 votes
1 answer
154 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 ...
1 vote
2 answers
523 views

MySQL - Many to Many relationship, or one to many with additional columns

Our product contains some constraints that made us contemplate which table design will yield the best performance. Our model: Question table which contains fields like id, body, difficulty, and tags. ...
0 votes
1 answer
29 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 ...
0 votes
3 answers
304 views

Can we prevent table from update directly but allow update using trigger from another table?

So, let's say I have 3 tables: stocks table, it have columns: id, product_id, stock sell table, it have columns: id, product_id, qty buy table, it have columns: id, product_id, qty What I want to do:...
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 ...
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 (...
1 vote
1 answer
867 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 ...
0 votes
1 answer
2k views

Data Schema For Stock Control / Multi Source Inventory

I'm working on a project that involves stock control with multiple stock sources and sales channels. The overall hierarchy I've got so far looks like this; Sales Channels <---- Allocated Stock ...
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 ...
1 vote
1 answer
191 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 ...
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 ...
0 votes
1 answer
30 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 ...
4 votes
4 answers
13k views

How can I model a medical scenario in an entity-relationship diagram?

I am a self-taught frontend developer that is trying to go full-stack, so I am trying to teach myself data modelling by means of working on some exercises out of a database management book. In this ...
0 votes
1 answer
429 views

Can someone give me feedback on my ER diagram?

I am new to creating ER diagrams and was wondering if anyone could review one that I constructed for an assignment. I used min, max notation in my design but I am not entirely sure if I did everything ...
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('...
1 vote
0 answers
46 views

How to represent arbitrary logical expressions in a many to many relationship?

I have a Course entity in my database and I want to represent its prerequisites. A Course can have many prerequisites. A Course can be a prerequisite for many other Courses Clearly, this would be ...
1 vote
1 answer
759 views

How to store user activity that supports undo?

I can track user activity on posts, so that users can see for example "2 updates!". The essence of the strategy I’m using leverages 2 tables: post_activity: post_id | user_id | time_created ...
2 votes
1 answer
2k views

Why do OLAP cubes perform better than Relational databases on certain tasks?

My team uses a Multidimensional OLAP database for a lot of it's analytics and predictive modeling. The standard line in all the documentation is "OLAP cubes are better suited for this type of ...
0 votes
2 answers
564 views

How to design a database for query builder project?

I have this project in which user can create queries using web based UI in which there will be list of columns and applicable operators. Later users can edit these queries, too. So I need to store ...
1 vote
1 answer
93 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 ...
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 ...
2 votes
1 answer
472 views

Modeling an either/or relationship

I've seen this post (How should I model an “either/or” relationship?) but it's not exactly what I'm looking for. both answers are suggesting creating a subtype instead of a relationship. Say I have an ...
4 votes
4 answers
24k views

Is it necessary for a foreign key to be a primary key in another table? Why?

I am new to database implementation. I want to know if it is necessary for a foreign key to be primary key of another table. Foreign key is not unique - it may have duplicate entries. If it must be ...
0 votes
1 answer
524 views

Optimize MariaDB 10.6

I am not a database guru. But here's the matter with which I'd like some help. I have a relatively freshly-minted server running AlmaLinux 8.7, with 64GB RAM and a pair of terabyte SSDs. After ...
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 ...
0 votes
1 answer
782 views

Saving daily data for each user - MySQL

I'm currently thinking about how to store data that the user might (or might not) change each day. Please note, I'm not a rdb expert and I'm still trying to learn everything. For example, the user may ...
0 votes
2 answers
642 views

Composite Key with a column that auto increments and reset when foreign key value changes

I am trying to implement a ticket system, the numbers generated would be sequential. 4000, 4001, 4002, 4003 My idea was to have a table that relates to current raffle, and the ticket_no column is an ...

1
2 3 4 5
127