Skip to main content

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).

Filter by
Sorted by
Tagged with
0 votes
1 answer
37 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 ...
Chris's user avatar
  • 101
0 votes
1 answer
31 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 * ...
wasfy mohammed's user avatar
0 votes
0 answers
18 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 ...
John Wilkinson's user avatar
-1 votes
0 answers
12 views

How we can generate database schema from json file

I have a database dump given in json format. I need to write the database schema for it.How we can generate relational database schema from json file? The text fields are straightforward; but I am ...
MobiusT'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

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
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
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
1 answer
56 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 ...
HDL_CinC_Dragon's user avatar
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
1 answer
27 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 ...
MJM's user avatar
  • 127
0 votes
0 answers
20 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 ...
chrestomanci's user avatar
0 votes
3 answers
163 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 ...
Harshal Patil's user avatar
2 votes
2 answers
52 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 ...
BooRuleDie's user avatar
0 votes
1 answer
86 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 ...
abenci's user avatar
  • 113
-1 votes
2 answers
41 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 ...
Bill--'s user avatar
  • 1
1 vote
2 answers
72 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 ...
Giacomo M's user avatar
  • 163
1 vote
1 answer
73 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 ...
Giacomo M's user avatar
  • 163
1 vote
0 answers
80 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 ...
Code-Crasher911's user avatar
1 vote
1 answer
83 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 ...
xedox_za's user avatar
0 votes
0 answers
44 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 , ...
Filipe Giusti's user avatar
3 votes
1 answer
81 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 ...
riwu's user avatar
  • 133
1 vote
1 answer
171 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 ...
Check12's user avatar
  • 63
0 votes
0 answers
27 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 ...
loris's user avatar
  • 103
1 vote
1 answer
49 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 ...
tomsu's user avatar
  • 111
0 votes
0 answers
37 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 ...
AndysPythonStuff's user avatar
1 vote
0 answers
22 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 ...
DBK's user avatar
  • 378
1 vote
0 answers
18 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 ...
nicholas.fowler's user avatar
0 votes
0 answers
52 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 ...
Ivan's user avatar
  • 101
0 votes
1 answer
30 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 ...
pjmv's user avatar
  • 113
0 votes
0 answers
21 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 ...
Izlia's user avatar
  • 101
0 votes
1 answer
462 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 ...
Farid Zahidov's user avatar
0 votes
0 answers
43 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 /****** ...
James's user avatar
  • 1
0 votes
1 answer
144 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, ...
niloufar padkan's user avatar
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 ...
tichy's user avatar
  • 1
0 votes
1 answer
57 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 ...
dukanuwdn's user avatar
1 vote
2 answers
69 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 ...
pjmv's user avatar
  • 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 ...
Pierre-Alexandre's user avatar
2 votes
1 answer
71 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....
Hem Bhagat's user avatar
0 votes
0 answers
35 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 ...
something101's user avatar
2 votes
1 answer
340 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 ...
aswine's user avatar
  • 143
0 votes
0 answers
53 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 ...
Leehan's user avatar
  • 207
0 votes
1 answer
41 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 ...
Manngo's user avatar
  • 2,819
1 vote
1 answer
704 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 ...
Scrumble_Meta's user avatar
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 ...
Pawel's user avatar
  • 111
1 vote
1 answer
101 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 ...
Tim's user avatar
  • 545
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 ...
VVT's user avatar
  • 11
2 votes
1 answer
192 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. ...
Dan Drake's user avatar
  • 123
1 vote
0 answers
929 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 ...
yayza's user avatar
  • 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. ...
hksfho's user avatar
  • 1

1
2 3 4 5
22