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,056
questions
0
votes
0
answers
7
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
0
votes
2
answers
46
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
...
- 131
0
votes
0
answers
20
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 ...
- 131
0
votes
0
answers
69
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 ...
0
votes
1
answer
47
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 ...
- 1
0
votes
0
answers
42
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
2
votes
1
answer
68
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 ...
- 123
0
votes
1
answer
150
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 ...
- 53
0
votes
0
answers
26
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
0
votes
1
answer
41
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 ...
- 101
-1
votes
0
answers
25
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 ...
0
votes
0
answers
10
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 ...
- 368
0
votes
0
answers
14
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
30
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
29
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
0
answers
12
views
Find document with regular expressions in collections related to populate in mongoose
The aim is to filter a collection of documents (Requests) based on the document field from another collection (Patients, Clients, Doctors, and Services), of which there are several references in the ...
- 101
0
votes
1
answer
185
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
0
answers
40
views
Linked server view Select query doesn't execute on production server
I've build one view. I can run sys.databases from the linked server. However, when I tried to execute the following view. It is not retrieving any data from the linked server.
USE [SSISDB]
GO
/****** ...
- 1
0
votes
1
answer
63
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
0
answers
14
views
changing the schema of incoming data for postgresql
I'm currently working on connecting a few large google sheets into postgreSQL. My current system is that the sheet gets extracted & loaded to postgreSQL, I'd then do the transformations ...
- 1
0
votes
1
answer
43
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
60
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
0
votes
0
answers
20
views
design tables with multi prerequisites
I am working on a browser game and currently on the data modelling part. This is a browser game where every player has one or multiple village, with different buildings, and each building can be ...
- 103
1
vote
1
answer
58
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....
- 111
0
votes
0
answers
28
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
160
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
51
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
34
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,769
1
vote
1
answer
381
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 ...
1
vote
0
answers
30
views
Schema for users that can "activate" others, that in turn can activate others [closed]
This is my first question ever so sorry if it's not up to standards.
I have a problem with figuring out solution for an interesting requirement:
A user on a system has their own url that when shared ...
- 111
1
vote
1
answer
75
views
Transferring ownership of table to a schema using sp_changeobjectowner
I have a number of tables in the database that belong to schema FOO, at least I think they do; they appear in the tables listing in SSMS as FOO.tablename, and when I right click on one of them and ...
- 435
0
votes
0
answers
12
views
Moving tables between postgresql schemas without messing up Hasura schemas
I have a database, which some teams are connecting to with their own Hasura instance.
I inherited a DB where everything is in the public schema, and I want to refactor it so that different parts of ...
- 111
0
votes
0
answers
64
views
Difference between schema in oracle and mysql [duplicate]
In the Oracle database, a user owns a single schema. What about MySQL? Is there something like a user owning a single schema in MySQL too? I read some documentation of schema on MySQL, it seems the ...
- 1
0
votes
0
answers
65
views
mongodb schema for multitenant entities with shared/public vs. tenant-specific/private properties
Example Background
Dog entity with user-independent properties (e.g. "breed")
User entity
Multiple users (<100) can own a dog, a person can own multiple dogs (>10 000)
Each owner has ...
- 101
1
vote
1
answer
24
views
Table Schema problem, column with multiple dependencies
I have a product table shown below:
CREATE TABLE(
Id_Product INT PRIMARY KEY,
Product_SKU INT NOT NULL UNIQUE,
Product_Name nvarchar(100) NOT NULL,
Product_Section ...
- 11
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
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
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
2
answers
86
views
What columns are needed to list the database, schema and table a record comes from?
I am writing queries which read identical tables across databases and schemas and I need the columns to include which schema and table a row comes from.
While searching during the creation of this ...
- 1,021
0
votes
2
answers
86
views
Allowing negative values up to a specific value
I have a situation where we store the value of credits a user has in the database:
`credits` DOUBLE(22,2) UNSIGNED NOT NULL DEFAULT '0.00',
The credits are numbers going from 1 to any positive value. ...
- 188
0
votes
0
answers
57
views
How to avoid new users to be able to access old and new databases in postgresql?
I am trying this query:
ALTER DEFAULT PRIVILEGES IN SCHEMA public;
REVOKE CONNECT ON DATABASE FROM PUBLIC;
but I am unable to make it work in PostgreSQL.
My goal is to avoid new users to be able to ...
- 1
1
vote
0
answers
29
views
Structuring an N-Gram-counts-over-time database
I’m working on an application similar to the Google Ngram viewer, tracking the occurrence of terms in articles from different news publications over time.
In the past I’ve worked primarily on front-...
- 13
0
votes
0
answers
165
views
Using Pre deployment and post deployment Script to alter schema while deploying in Azure Devops
As part of CI, In Azure Devops Deploying Azure Sql Db to prod facing an issue Rows were detected. The schema update is terminating because data loss might occur.
I Want to use Visual Studio pre ...
- 1
0
votes
0
answers
34
views
Translating given requirements into tables
New to SQL as I've worked with it for 24 hours.
I've been given a list of requirements which I would like to turn into various tables.
I've taken a stab at it and written down what I think is the ...
0
votes
1
answer
94
views
Cloning a table into a separate schema in SQL Server
On an SQL Server database that I manage, there are a fair number of backup clone tables lying around, littered throughout the dbo schema. I am about to create such a backup clone now, and I would like ...
0
votes
2
answers
659
views
Hide schemas (tables,views,etc under schema) from users
I want users not to be able to see the objects under a schema , as well as the schema. I have denied their access to all these objects on select, update, etc, which is successful. Hence they can’t ...
- 9
1
vote
0
answers
308
views
Database schema design of company time series data
Purpose:
To monitor the changes in total capital and paid-in capital of various companies, and identify trends among the different company categories. By determining whether the sum of total capital ...
0
votes
1
answer
684
views
How does PostgreSQL resolve pg_temp?
pg_temp is an alias for current session's temporary-table schema.
Likewise, the current session's temporary-table schema, pg_temp_nnn, is always searched if it exists. It can be explicitly listed in ...
- 101
0
votes
1
answer
68
views
Store different data of the same format in one table, or multiple tables of the same format?
Consider I have the following MySQL tables:
products
id | created_at | updated_at | name | description | barcode
stores
id | created_at | updated_at | name | description | address
product_attributes
...
- 101
0
votes
1
answer
230
views
How to get users to see specific schemas only
Thanks for your time.
I need to have set of schemas which are to be viewed by single users or a group of users ( set in a role) and others should not have access to them. I don’t want to put each user ...
- 9