Questions tagged [database-design]
The development of the conceptual schema and/or the logical model and/or the physical settings of a database.
6,231
questions
0
votes
1
answer
97
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 ...
- 101
0
votes
1
answer
130
views
Set column value to different randomly chosen values from another table
My goal: Set PID_FM in every row to a randomly picked value out of all the values matched by the subquery.
However the FM_ID is only picked once, and therefore the whole column has the same value. I ...
- 1
1
vote
1
answer
68
views
Bidirectional self join table
Consider:
CREATE TABLE object (
id integer NOT NULL DEFAULT nextval('object_seq'),
...,
CONSTRAINT object_pk PRIMARY KEY (id)
);
CREATE TABLE pairing (
object1 integer NOT ...
- 61
0
votes
0
answers
32
views
Composite foreign key relationship with primary key in relational database
I have these two tables:
Table1
(
FileID,
...
)
Table2
(
FileID,
DataID,
UsefulData
)
The PK for table Table1 is FileID, and for Table2 are FileID and DataID.
I think I can't create a FK ...
- 1,843
0
votes
2
answers
106
views
Should I include a junction table with a primary key in my ski rental database schema?
I need to make the ER diagram and the relational schema of the Ski rentals. I have an Customer, Rental and Ski as a entities as well as their attributes. Customers and Rentals should have the one-to-...
2
votes
0
answers
37
views
Modeling strictly referential relationships to third-party databases
To be clear, I mean to ask this question from a strictly "Data Modelling and database-design, including referential-integrity" PoV.
I am currently designing a database that will serve as an ...
- 121
0
votes
0
answers
27
views
Model resource ownership in a flat structure
I have a database model with different entities related to each other, but in the end they all have to be 'owned' by the master entity which in this case is the Organization.
I was wondering if it is ...
- 101
0
votes
1
answer
29
views
ERD Review, Employee travel lump-sum payment based on rank
I'm building application for employee official travel with lump-sum payment based on the employee group or echelon they have and I need a little help how to store the data correctly, as the problem im ...
2
votes
3
answers
57
views
When designing a database, how to keep track of nonexistent entities?
Sorry if the title is a bit confusing, but I don't really know how to convey the question in a straightforward way.
This is a design problem. Let's say you have this really simple tables
create table ...
- 284
1
vote
1
answer
71
views
Should I use a single column with many dimensions with columnar data?
I'm using Apache Iceberg which performs queries on Parquet files. The data saves a lot of types of events and their respective numeric values. I'm hesitating whether it's more efficient to have one ...
- 113
1
vote
1
answer
115
views
Best design pattern for Posts and Comments, whereas Comments are also Posts
I have a Posts table and want to be able to have comments that are full fledged Posts in their own right. So instead of creating a Comments table I was thinking of adding a parent_id column to the ...
- 113
2
votes
2
answers
119
views
Database Design: Constrain one table against another tables Foreign Key
I have the following tables in a SQL database, I want to ensure that a Contact can't be created against a ContactType and Matter that have different Clients. I'm not sure how I can go about creating ...
- 123
1
vote
1
answer
56
views
Database Design: Modeling Bridge Tables
Team: I have a question regarding a requirement: One security officer works can work at many offices. Depending on the office that the security officer is working on that day, the officer can have a ...
- 11
0
votes
1
answer
31
views
Should I model statistical summary as a separate entity or with the summarized entity as part of the key
I run several tests on each unit on a wafer and want to store a summary (median, cpk, std dev, etc) of the results for each test across the wafer. I also want to store a summary of the results for ...
- 101
0
votes
1
answer
207
views
Database structure for multiple user types
I am developing a database that will store users of different types. Currently, there are only two types of users, clients and agents. In the future, there may be a new type of user, "...
- 1
1
vote
1
answer
99
views
Column containing keys from columns of another table
I have two csv files, countries and birth_rate_stat with the following data:
ISO
ISO3
ISO_CODE
FIPS
Display Name
Currency Name
Phone_Code
Region Code
AF
AFG
4
AF
Afghanistan
Afghani
93
Asia
AX
ALA
...
- 11
1
vote
2
answers
241
views
Design many-to-many join table with additional column
I have product and documentation tables and want to hava a many-to-many relationship between them with an additional condition.
The tables look like this:
create table product (
id bigserial primary ...
- 11
0
votes
0
answers
39
views
MySQL join 2 column which every column got string with command seperator and add same value
Hello guys,
I want to combine 2 column with same total comma separator and add same string in the middle, anybody can help me how to do it?
column1
Column2
Column3
a,b,c,d
1,2,3,4
Output Table1
...
- 1
0
votes
1
answer
53
views
Large table with too many indexes stalls my app
PostgreSQL version: 13.10
My app has a notification feature, where users have a list of events that happened in relation to them (much like the Facebook notifications). Each notification is a row in ...
- 101
1
vote
1
answer
19
views
Best DB architecture approach to store transaction and boundbook records
This DB design is related to developing an application for a Gunshop that keeps acquisition and disposition records of firearms they bought and sold. As a database, we are using postgreSQL.
For each ...
- 111
0
votes
1
answer
294
views
Correct entity relationship diagram representation of a normalized table with a NOT NULL foreign key
Intro
Hi everyone, this is mainly a classification/theoretical question on the topic of inheritance and normalization techniques in database design and their appropriate representation in entity ...
1
vote
1
answer
165
views
Secondary indexes vs Using elastic search
When does it make sense to put data in elastic search vs creating secondary indexing on Primary datastore?
Elastic search with another primary store
Pros:
Primary datastore can be optimised for read ...
- 111
0
votes
2
answers
63
views
more columns or separate tables
Let's assume I've a table called post and want to add comments, they both essentially the same so there's no need to create another table, now I can just make a FK column to point to the same model, ...
- 101
0
votes
0
answers
20
views
How I can aggregate a final store schedule in postgresql?
I have the following table that I keep for each day of week when a store is open:
store
---
id PK
name VARCHAR
weekly_store_open_schecule
---
id PK UNSIGNED SERIAL
store_id INTEGER not null ...
0
votes
1
answer
35
views
Source tables from JSON API extraction
Trying to implement an API extraction that dumps the results from JSON apis daily in a postgres db. The goal for these data is to be used in analysis. Afterwards I will handle those using dbt to do ...
- 103
1
vote
0
answers
785
views
Database schema to manage customers subscriptions
I'm currently developing a system that allows users to select a plan and subscribe to it. For simplicity, let's assume there are 3 plans: Silver, Gold and Platinum. Also let's assume that the prices ...
- 171
1
vote
0
answers
238
views
Use 2 columns of a table as foreign key in an other table to avoid circular-reference
I have two tables:
ObjectContainer
containerID INT NOT NULL PRIMARY KEY
mainObjectID INT NOT NULL FOREIGN KEY REFERENCES Object (objectID)
Object
objectID INT NOT ...
- 11
4
votes
1
answer
699
views
Query performance on static, large PostgreSQL table
I tried to have this as detailed as possible. Sorry about the length!
Background
I created the following partitioned table protein_snp_assoc on a PostgreSQL (version 12.13) database:
CREATE TABLE ...
- 43
2
votes
0
answers
110
views
DB design for tree structure of user access permissions
I am trying to come up with a good database design for the following problem:
We have an application where users can create objects (such as notes) and link those objects to other objects, projects or ...
- 21
0
votes
1
answer
48
views
Proper way of constructing database of historical datasets
I've been attempting to learn how to build a database of historical sports statistics using postgresql/sqlalchemy ORM the past few days and could really use some advice on approach. My use case seems ...
- 101
0
votes
0
answers
34
views
ERD based on FE mockups review
I'm trying to create an ERD based on the FE mockups here: https://i.stack.imgur.com/gRMNq.jpg.
It's a multi-tenant database where a tenant represents a company. Azure AD is used for authentication. ...
- 109
0
votes
1
answer
400
views
How to prevent double booking of a room in this hotel booking database design?
I have two tables for hotel booking. One is rooms table where I save room type, hotelId and total number of rooms that the hotel has for that type.
HotelId
RoomTypeId
RoomType
TotalRooms
100
2
...
- 101
2
votes
1
answer
149
views
how to make a table for describing subsets of rows of another table?
I'm wondering about the idiomatic, or at least a workable, way to make a table that represents subsets of rows of another table. This is motivated by efficiently storing and looking up these subsets.
...
- 123
1
vote
0
answers
167
views
MySQL: Hierarchical Organization of Tables
There are several SE Q&As regarding how to hierarchically organize tables in MySQL:
How to use a naming convention for large databases?
Asked 12 years, 11 months ago
How can I organize a glut of ...
- 11
0
votes
1
answer
293
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 ...
1
vote
0
answers
20
views
Related object across many table
The problem
I'm building a database about book that currently as the following tables
book
part
chapter
paragraph
with the expected one-to-many relation ship (ie book has many part, with many ...
- 11
0
votes
0
answers
15
views
Shipment Detail table design with rows from 2 different tables
I have a Shipment table with just the following fields:
shipment_id, date, store_id.
A ShipmentDetail table whose rows represent the items sent. Now, these can come from two different tables: the ...
3
votes
0
answers
37
views
Data modelling for dynamic grouping of rows in Cassandra
I have a feed of game events with a schema like;
{
"gameId": text,
"playerId": text,
"eventId": text,
"ts": bigint,
"metrics": map<...
- 31
0
votes
1
answer
53
views
Is it good practice to join many tables together [User Data Design]?
I'm trying to design a database for storing information about users--say name, email, GPA, classes, and more information in the future.
I believe the proper design involves storing each field as a row ...
- 1
0
votes
0
answers
24
views
How best to design this database…?
I am designing an application to handle clubs, teams and people (players, parents, coaches, team managers and other club staff). The application is multi-sport, meaning it would handle, for example, ...
- 41
1
vote
0
answers
29
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 ...
- 11
1
vote
0
answers
604
views
Products, attributes, and options schema for a marketplace
I've been trying to figure out the best way to implement a table design for products for a marketplace-type app (think amazon).
I'm going to upload a bunch of products for sellers to add to their ...
- 11
1
vote
1
answer
1k
views
Is it acceptable to store global date times in UTC without offset for timezone?
I'm looking at a Data Warehouse which collections data from systems across the world. The Data Warehouse uses UTC for time but only stores the UTC offset and not the timezone. I have noticed that ...
- 113
0
votes
1
answer
36
views
Is id in all table a good schema design for MongoDB?
We are designin User and Order collection.
In our User collection, we already has DBRef referencing to Order collection but our manager asked us adding an extra field in the order collection.
...
- 1
0
votes
0
answers
32
views
Versioning and relationships between entities
Scenario:
Two database tables with multiple versions for the same entity.
Table EntityA with
A_id (primary key, surrogate), name, effective_from, effective_to, entityA_id
Table EntityB with
B_id (...
- 101
-1
votes
1
answer
36
views
What arrow is used to specify the relationship between a junction table and the FK tables [closed]
This is part of my first database design. I am unsure what type of arrow to use to describe the relationship:
The tables are parking space, parking space features, and features.
I am unsure of how to ...
- 1
-1
votes
1
answer
160
views
Understanding OLTP and OLAP
I know what is the meaning of OLAP and OLTP and who is specialized to do what. Currently, I'm just studying data modelling theoretically and I don't know the practicality of the differences.
I wanted ...
- 3
0
votes
0
answers
69
views
Fastest Mechanism: Monitor new record being added to MySQL DB, and push info via WebSocket/SSE?
I have a MySQL DB on MaridDB.
There's a table that gets new records inserted at inconsistent intervals. sometimes every seconds, sometimes every few minutes.
I have a web page and instead of having ...
- 101
1
vote
1
answer
30
views
When is a group of attributes considered prime?
Given the candidate keys CD and DE and the functional dependency AB->CDE. Is CDE considered prime because all attributes (C, D and E) are prime? Or is it non-prime because CDE is not found in a ...
- 11
2
votes
0
answers
797
views
How to design a database schema for multiple user types?
Question:
I have a system where I can have multiple types of users: buyers, vendors, admins, etc. These types of users aren't identical, but they're not utterly unlike each other.
There will be ...
- 121