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
1 vote
3 answers
25 views

How to index a table using column of another table having relation with it?

I have two tables, Pilot and Company. My queries will be searching for the Pilots using the CompanyName. For example, I want to get Pilots in a company named 'Boeing'. How should I write my CREATE ...
0 votes
1 answer
41 views

Make sure that such a field combination exists in another table when inserting

Table A: id | name | user_id Table B: id | type Table A_B id | table_a_id | table_b_id | user_id Table A_B is a standard linking table for a many-to-many relationship except that probably using a ...
1 vote
2 answers
19 views

setting search_path to role doesn't seem to work in postgres

I've created a backend api that connects to postgres and performs various queries. I've created a specific user (role + can login privilege) for that backend api: backend_api_user I've also dropped ...
1 vote
1 answer
28 views

A clarification about data normalization

I've been a software developer for 8+ years now and I think I have a pretty good grasp of the concepts but as an entirely self-taught developer, I have some unfortunate gaps in proper terminology. ...
0 votes
0 answers
26 views

Database scheme design advise on composite primary key

I planning to make database on ms sql server with fields "exp_id", "host_id", "cam_id", "record_id" which supposed to identify a row uniquely and to be used as ...
0 votes
0 answers
20 views

Schema design for tracking the gaz prices in MongoDB

I would like to build an app that give the ability to search for the cheapest gaz price around Display the gaz price trend whether it's up or down.(similar to the stock price) The data source are ...
0 votes
0 answers
19 views

Database schema design feedback

I'd like to develop a simple portfolio manager web application with the following functions: Create/Edit/Delete portfolios Add/Edit/Remove trades Change language of the app (English for now) Change ...
0 votes
0 answers
27 views

ERD Design for mangers and their respective locations

I am tasked with replacing the company spreadsheet containing all our sites and which belong to the three upper managers in their respective hierarchy to a relational database. This is a relatively ...
0 votes
1 answer
28 views

Postgres: How to express Many-tables to Many-tables relationship?

What I have I have a schema of listeners defined like so: create table listeners.ethereum ( id serial primary key not null ); create table listeners.solana ( id serial primary key not null ); I ...
1 vote
0 answers
28 views

Could you help me evaluate my database schema for my pet project?

I'm planning to make a pet project about a shop with a cart to consolidate knowledge into practice. I'm pretty new to databases and would like to hear from you about how I can improve my data schema. ...
  • 11
0 votes
1 answer
22 views

Create a schema for each user in a group

I'm trying to find a way to create a schema within a database, for each user in a group... The goal is to automatize the creation process for a structure in a school. Say, each class is a database, ...
1 vote
0 answers
45 views

Can a foreign key be a primary key?

I want to know the relationship takes, teaches, advisors tables underlined attributes are foreign key or primary key? or both?
1 vote
2 answers
92 views

How to implement/architect a SQL database schema which can handle types/tables with many optional properties?

I am working on a trimmed down database schema (in PostgreSQL) like the old FreeBase, just not quite as much stuff. There are about 100 tables so far, but that is before I'm considering adding dozens ...
  • 155
0 votes
1 answer
75 views

In Postgres 12, how do I set a default schema when logging in using psql?

I'm logging in to my Postgres 12 (running on Windows 10) db using an alias, which resolves to PGPASSWORD=$DB_PASSWORD psql -U${DB_USER} $DB_NAME I would like to find a way to tell Postgres which ...
  • 515
0 votes
1 answer
25 views

Multiple tables for many-to-many mappings or one table with multiple columns?

I'm using Postgres 14 and I have a tags table with a many-to-many relationship with other entities such as posts, comments, pages, etc. I've currently been representing these with tables like ...
0 votes
1 answer
57 views

Opinion on my schema

I've decided to create a database as a personal practice. The project is based on a fictional English language school called "ESL" located in Japan. The school has 4 branches in 4 different ...
  • 1
0 votes
0 answers
28 views

Should I avoid JSONB column for subschemas?

I'm working on a schema for an audit subsystem and I'm wondering about the tradeoffs of some potential design choices. About the schema, each event holds the same critical metadata event_id, ...
2 votes
1 answer
85 views

Is there a way to use time series databases effectively without absolute timestamps?

I have multiple sets of scientific data (European Data File files for EEG data) that I am reading in via MNE Python. Each EDF can be read in with a Numpy array that contains the time basis, say 0.00, ...
  • 145
1 vote
1 answer
47 views

The best approach to abstract the item in storage database (theoretical question - no code required)

I am struggling to find a better approach on the following topic and would appreciate any advice. 1. Description: Database purpose - warehouse storage. There are a couple of entities such as: ...
  • 13
0 votes
1 answer
34 views

Associating transactions with payouts in marketplace

I'm developing a marketplace-style application that allows users to upload purchasable digital items -> the public purchases these items -> and for my application to pay the users (owners of ...
0 votes
0 answers
16 views

What is the best way to track user ip and id for the page visits

I am 15 pages in my site. I want to track the guest user (through IP) & loggedin user (through userid) for all the pages. What I have schema is: Create a table; create one entry; and make 15 ...
  • 101
-1 votes
1 answer
42 views

Desired State Schema Best Practices for continous schema + data upgrades at different on prem locations

I am looking however to blogs/articles on people who did the same so that i can reference my work against other people who also went in this direction before / or even a github repo: I have a project ...
  • 131
0 votes
1 answer
39 views

Creation Synonym among different db schemas

might seem a stupid question, but I'm pretty new to the db world. Is that a good practice to have two oracle schemas which have synonyms pointing to each other ? like SCHEMA_A owns a SYNONYM_1 ...
  • 1
1 vote
1 answer
34 views

mysql optimization for intensive schema-altering queries (DDL)

In the context of CI would like to massively parallelise the running of tests. The code under testing require a database to be present, so MySQL is used (as the application goes with that right now). ...
0 votes
1 answer
250 views

Recover schema and the data in it from accidentally deleted schema

I did something careless. I deleted a schema. I have backups of the \mysql...\data\refadmin folder so I (hope) I have all the tables and data. That's backed up daily. When I look at work bench, I ...
3 votes
1 answer
69 views

Lists and list of items instead of one table per 'list'?

A sql database I am working on at a customer has been created with an unusual structure. For example, instead of having one table for each of the following: Country, State, City, Units (in, m, ...
0 votes
0 answers
13 views

Importing a database with the exact same schema as the destination database

I am working in Access with a fairly simply relational database. I want to import another database of the EXACT same schema, but when I use Access' import tool the software creates extra tables (...
0 votes
0 answers
18 views

ERD relationships rental_car

So I'm having difficulties with making an erd for a project about renting cars. I think I'm almost done, I only have to make the relationship between 3 tables ,2 of them had many to many relationship ...
  • 1
0 votes
0 answers
63 views

Airport booking system schema (ERD) guidance

I have to make an ERD for an Airport. I have created and identified some entities but I am having some trouble connecting them among them. For the airport's booking system I have the following ...
0 votes
1 answer
56 views

Is my database ERD well designed

I'm creating a PostgreSQL database for datasets, which should store historical and future data of weather forecasts and train timetables. I'm wondering if my ERD is good enough for a database which ...
  • 1
0 votes
1 answer
2k views

Postgresql DB - insert or update on table violates foreign key constraint + Schema Design Question

I have 1 big table with 9 fields/columns, some of theses columns have a lot of duplicates, so i decided to split them into 2 tables in Postgresql i.e. TEST_A table with columns that have a lot of ...
  • 1
1 vote
1 answer
19 views

How to model data which sometimes (but not always) has a subsection?

I'm trying to help my wife create a schema for a project of hers, and I'm somewhat stumped by some of her data and how to model it. She has a bunch of "standards" that go in to a database. ...
  • 13
1 vote
1 answer
281 views

Unique array values for this string_to_array

This is a follow-up to: Best way to map different JSON keys to same target columns Based on these sample tables: data_providers: id | field_map -------------- 1 | {"segments": "...
  • 203
3 votes
1 answer
556 views

Best way to map different JSON keys to same target columns

I have CSV import data that comes from multiple vendors with 50+ different columns, and each vendor has slightly different naming. Instead of trying to spend hours mapping all of the column names to a ...
  • 203
0 votes
1 answer
38 views

DB Schema Design for storing Test Results

I am working on a project to view test results for different 15 subsystems . Each subsystem is more or less similar but requires different monitoring parameters to be tested . Each subsystem has ~300 ...
  • 1
1 vote
0 answers
42 views

Database design for aggregate data (containing duplicate records)

I'm looking to generate statistics based on the MySQL database - but I'm having issue in designing the proper database schema for it. Scenario : User has a bunch of machines which upload their ...
0 votes
1 answer
75 views

Storing order of relation as JSON array

Let's assume I have Playlist, Song and playlist_song tables. Where's it's better to store song ordering data? In Playlist as JSON songs_order = [song_id_1, song_id_3, song_id_2] or better on ...
1 vote
1 answer
2k views

Chat schema for PostgreSQL and MongoDB

I'm designing a schema for a very large chat app. I had problems with NoSQL databases such as MongoDB and Cassandra. Some part of the schema is relational i decided to use both PostgreSQL and MongoDB ...
0 votes
0 answers
73 views

Add extra column to anyelement type - querying across multiple schemas with extra information

I'm trying to create a generic function which can be used to query across all of my schemas. I'm using schemas to separate my tenants, so they all have the structure. I found Staab's post useful: ...
1 vote
1 answer
59 views

Role-playing Dimensions

I am new to data warehouse schema and i am confused about role-playing dimensions. i have searched a lot and regarding to dates in data warehouse, it is always the same type of example they give ...
0 votes
0 answers
60 views

Mongodb search and sort

My scenario is to search and then sort according to date for this I have made my schema like this { section:String, name:String,(always lowercase) updated_at:Date.now ...many more fields } ...
0 votes
1 answer
104 views

Database Design For Realty Website Review

I am designing a real estate website and wanted feedback from some professionals about the database schema that I came up with. I created a property table that would have each house. Each house can be ...
0 votes
0 answers
28 views

How to organize common columns to a single table?

I'm very new to database designing and I'm creating a small project to understand it better, I'm trying to come up with a new school crm. I started with the below schema: create table student( id ...
  • 113
0 votes
1 answer
303 views

PostgreSQL granted all privileges on schema, but user still cannot insert a record

This is maddening. I'm running PostgreSQL 14 in a Docker container using a standard image. # docker-compose.yml version: "3.7" services: pg: image: postgres environment: - ...
-1 votes
1 answer
322 views

Understanding a double entry accounting DB Schema

This question is related to a previous question "Why the Accounting DB Schemas do not allow to maintain a clean Products Table?", asked on this network by someone else. Assuming that the ...
0 votes
1 answer
24 views

How to design Postgres schema with multiple nullables?

I want to create a table for tasks in my database. There are a number of roles accounts can have in my app—admin, operator, user. All these roles can create tasks, and admin and operator can close the ...
0 votes
1 answer
133 views

Is it possible to apply filters on SqlPackage?

We develop a lot of modules separately. This means the Blog module has its own database in development. All objects of each module start with the module schema. So the Posts table inside the Blog ...
  • 179
0 votes
1 answer
111 views

Survey DB Design. No user answers, only questions

The task is to SQL a bunch of surveys in order for the researchers to have examples for creating new questionnaires in the future (and insert those questionnaires into DB). So no information about the ...
  • 1
0 votes
2 answers
38 views

data structure for grouping children without parents

I have a list of name ID Name 1 John Doe 2 J. Doe 3 John Doe 4 John Doe With different methods, I check which names are the same person by checking their actions. For example, in ...
  • 4,281
0 votes
1 answer
103 views

How to spot all transitive dependencies in mysql database design?

I've read up on 3NF and transitive dependencies but had a couple of questions I'm unsure if I have achieved 3NF with my database design. Context: I want to create a basic bankings design where ...
  • 13

1
2 3 4 5
21