Questions tagged [schema]
A schema of a database system is its structure described in a formal language supported by the database management system (DBMS) and refers to the organization of data to create a blueprint of how a database will be constructed (divided into database tables).
1,068 questions
0
votes
0
answers
20
views
Figuring out dimensional modeling requirements for OLAP in a database containing many pre-aggregated fact tables
I have the situation where we have an application that uses a PostgreSQL database for OLAP queries for reporting (less DWH/exploratory). I am trying to find an optimal database design for this ...
- 111
0
votes
1
answer
41
views
Could a Tool Like ChartDB Work for MongoDB Schema Visualization?
I’m one of the developers behind ChartDB, an open-source tool for database schema visualization, particularly useful for relational databases (RDBMS). Our tool helps users generate ER diagrams to map ...
- 11
0
votes
1
answer
16
views
postgreSQL server ver. 14 , how to import dump ( A directory-based archive ) file using pg_restore by remaping schema name?
postgreSQL server ver. 14 , how to import dump ( A directory-based archive ) file using pg_restore by remaping schema name?
we need to use pg_dump efficiently with parameter j , as a result we ...
- 1
3
votes
2
answers
231
views
How can I run sp_refreshview across all views in my SQL Server database
I'm using SQL Server, and I received an error indicating that one or more of my SQL views is out of sync with its underlying SQL table.
'MySQLServerName' returned data that does not match expected ...
- 339
-1
votes
2
answers
32
views
Which Postgres schema should I go with?
This is my first time working with databases. I'm designing a database for an e-commerce project. I ended up with two versions of the schema, and I don't know which one to go with or what its pros and ...
-3
votes
1
answer
57
views
Are there mnemonics in the database design space that you find helpful when communicating database design concerns? [closed]
A popular and useful mnemonic in the software development space is SOLID.
Single responsibility
Open–closed
Liskov substitution
Interface segregation
Dependency inversion
Are there mnemonics in the ...
- 135
0
votes
1
answer
68
views
How should I differentiate between system-defined and user-defined data?
I'm using PostgreSQL and designing a relational database schema for an application where I want to store both system-defined (built-in) data and user-defined (custom) data in several tables, e.g.:
...
- 65
0
votes
1
answer
89
views
Is there any disadvantage to putting join tables in their own schema?
Is there any disadvantage to putting join tables in their own schema?
Title, I could have a few schemas:
app
user
permission
app_join
user_permission
If I’m working with sql I’d have to include ...
- 105
0
votes
5
answers
166
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 ...
- 151
0
votes
1
answer
70
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 ...
- 233
0
votes
0
answers
93
views
Schema not accessible implicitly on dev DB
I've got 2 databases, one for production and one for development.
On production I can run
select * from [mytable] -- and query [myschema].[mytable] without specifying the schema
but on the ...
- 117
0
votes
1
answer
46
views
Recommended way to build database to contain users and chatgpt data
I am looking to create a Rails application to interface between users and Azure ChatGPT. I will include my ERD diagram showing a couple of options in building the database tables and table ...
- 101
0
votes
1
answer
201
views
PostgreSQL - Selecting from different schemas in the same database
Will creating a view Table in schema 2 that pulls data from Table on schema 1 , affect the database or cause any other issues?
my code:
CREATE VIEW "test"."Excavation" AS
SELECT *
...
0
votes
0
answers
293
views
Grant permission for a user to create objects in a PostgreSQL schema is not working
I have recently upgraded to PostgreSQL 15 and observed that a db user say X (not a owner of schema) is no longer able to create a table in public schema. From the below release notes looks like it is ...
- 101
0
votes
0
answers
73
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, ...
0
votes
0
answers
30
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.
...
0
votes
0
answers
24
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 ...
- 103
0
votes
1
answer
85
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` ...
0
votes
1
answer
63
views
Is my schema "correct"?
Is my proposed schema below prone to make overly complex and slow running queries? Is there a better way to layout this database?
The situation:
I have no formal DBA training.
tl;dr section below this ...
-1
votes
2
answers
127
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 ...
- 123
0
votes
1
answer
63
views
Automating PostgreSQL query so I don't have to change schema name each time in sql query
I have a multistep PostgreSQL + PostGIS query/geoprocessing operation that I need to change the schema name for the output tables each time I run it. The base tables I use are all in another schema ...
- 127
0
votes
0
answers
21
views
Postgres unique constraint with a custom comparator [duplicate]
TLDR: Is it possible to add a unique constraint to a column in a Postgres table that will use a custom comparison operator or function to compare the newly inserted or modified column with the rest of ...
- 45
0
votes
3
answers
168
views
How to design a table that can reference two different table?
I am designing a schema for a very simple contact management software. I have two tables, viz. person and customer. A person is an individual while customer is meant as a company or individual or ...
- 119
2
votes
2
answers
83
views
MongoDB schema for visit and like
MySQL serves as our primary database, while MongoDB is employed for handling intensive write operations. I have been tasked with implementing a system for tracking visits and likes on products. The ...
- 21
0
votes
1
answer
94
views
How do I protected my sensitive data (financial) from website/CRM programmers?
As a company owner, I need to protect my sensitive data from my website/CRM programmers. I know I can scramble DB values with a simple script but it needs to be applied manually all the time and when ...
- 123
-1
votes
2
answers
48
views
Storing small lists of FKs in array column and joining
Is it (1) possible and (2) faster to use an array to store small lists of Fk's then to use a separate table.
I believe you would use an IN clause to do this but I'm not sure if it's possible to use an ...
- 1
1
vote
2
answers
82
views
database design for tree structure with just one level
is it possible to design a database with a classic tree structure (no nested-set), forcing to have just 2 levels?
the table I thought should be:
id
title
parent_id
1
row
NULL
2
child
1
3
...
- 163
1
vote
1
answer
98
views
Model a database for user subscription that can change during time
I'm struggling modeling a database.
This is the domain:
an user does have a subscription that has a start_date and a end_date
an user can change its subscription, starting from a certain date (it can ...
- 163
1
vote
0
answers
81
views
expanding a current database
I am rewriting a server program. currently the program has order batching and orders.
(currently written in winforms with mssql database with no orm- to be rewritten as a blazor server app with mssql ...
1
vote
1
answer
116
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 ...
- 11
0
votes
0
answers
47
views
How to create a schema for a combination of values that needs to be unique
How would I create a schema for the following data:
Dimension A
Attribute X, Attribute Y
positive , positive => Description 1
positive , negative => Description 2
negative , ...
- 256
3
votes
1
answer
89
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 ...
- 133
1
vote
1
answer
214
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 ...
- 65
0
votes
0
answers
29
views
Structure for table to record events in other tables?
Say I have three tables:
people
groups
memberships
with the following primary keys:
uid
gid
uid+gid
I would like to record events such the creation/deletion off people/groups/memberships. I could ...
- 103
1
vote
1
answer
58
views
Optimizing the SQLite database as source data for R scripts
I have an application written in R Shiny that supports a large number of csv's (and 3 new ones are added every day). These files have a structure that is quite unpleasant to handle, so I decided to ...
- 111
0
votes
0
answers
58
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 ...
1
vote
0
answers
30
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 ...
- 378
1
vote
0
answers
22
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 ...
0
votes
0
answers
226
views
MySql: Ecommerce product variant structure
I have a problem with the database organization for ecommerce product variations. Googling, I found a lot of articles and every article says that if the product has more variations, it should be in a ...
- 101
0
votes
1
answer
31
views
How can I avoid writing a loop when allocating orders to a stock impacted by the orders?
I have three tables in my database representing product storage, order items, and shipments:
CREATE TABLE storage (
product_id INT PRIMARY KEY,
storage1 INT,
storage2 INT
);
CREATE TABLE ...
- 113
0
votes
1
answer
937
views
Finding when columns are added- SQL Server
In SQL Server is there any way to get information about when columns are created and which columns are new? Also, I need to find any change in columns, for example which columns' data type changed. I ...
0
votes
1
answer
228
views
Doctor CRM database design
I am designing a doctor CRM system. Now I need to have a list of patients in the doctor panel. what I do is I find all the bookings which are related to doctors and extract user data from there. Also, ...
0
votes
1
answer
66
views
Should I put the attribute in each table even if they are linked to increase performance?
Creating a schema for a workout app, currently building the logs for finished workout sessions.
Here's the part I'm wondering about:
Wondering if I should put date in each table. The reason is that I ...
1
vote
2
answers
99
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 ...
- 113
2
votes
1
answer
78
views
Database design suggestion for improving existing design
Background
I am working on an IoT application that deals with sensors, their configurations and their readings. Following are the requirements of the system,
Each sensor can support multiple features....
- 121
0
votes
0
answers
36
views
Looking to improve current database schema
I have recently developed a schema for managing sports-related data, and I would greatly appreciate your opinion on its structure. Additionally, any advice or suggestions you may have for improvement ...
2
votes
1
answer
610
views
What's a good way to handle cross-database keys in PostgreSQL?
I'm writing an accounting app that uses MySQL, but I'm thinking about switching to PostgreSQL. My app has a meta database that stores users, document types, a list of companies, etc. Each company (a ...
- 143
0
votes
0
answers
70
views
how to reduce the time to list all schemas of PostgreSQL
QGIS 3.18 PostgreSQL 14.
We use QGIS to access data from a PostGIS server. The data are stored in 33 schemas.
one contains all the data in 17 tables;
the others schemas contain views and materialized ...
- 207
0
votes
1
answer
54
views
Separating out the standard functions in PostgreSQL
I’ve been using PostgreSQL for many years, and, among other things, I have added a number of my own user defined scalar functions.
Of course, if I knew about it at the time, I should have added my own ...
- 2,919
1
vote
1
answer
1k
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 ...
