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
0answers
30 views

Best way to “superset” combine many disparate schemas for database table creation?

I have a bunch of data that consists of public records from the state government dating back to the early 90s. Along the way, the data organization and attributes have changed significantly. I put ...
1
vote
0answers
17 views

Should I use Postgres' JSON columns for additional related data of different types and not yet known?

I have a simple database table, lets call it tbl_main with a few columns and relations to other tables via foreign keys. The tbl_main is the most frequently written to table, as in it receives new ...
0
votes
0answers
32 views

Look Up Table for View, or Relation

Setup I designed a database scheme which purpose it is to act as a foundation for testing a System under Test (SUT) which weren't testable by automated test on default (have no Oracle and are ...
0
votes
1answer
20 views

More tables or More records performance wise

i am into a scenario that upon looking at the our project database contains Identical tables with the schema described below: id url region_1 region_2 region_3 region_4 date_created My ...
0
votes
0answers
12 views

Database schema for forum with unapproved posts [closed]

I'm creating a forum (PHP and MySQL) and hit upon a fundamental design question. My forum posts go into related tables such as forum_topics and forum_posts I was wondering about security and ...
-2
votes
1answer
15 views

Database Schema design MySQL Multiple site [closed]

i wanna a database schema design MySQL for Multiplesite using Wordpress (Subdirectories). example.com ,example.com/1/ ,example.com/2/ ,example.com/2/ -i would like that the user can with the same ...
0
votes
3answers
46 views

Oracle - Is a separate database more secure than using the same database and a separate schema

We have very few developers and the ask from management is insane (computer vision, AI, IoT, etc.). Management wants to split the production database which uses Oracle Apex because the Apex ...
0
votes
0answers
22 views

Liquibase on Oracle SQLcl - table and index drops not being propagated

I've been using Oracle's SQLcl, specifically the lb command to use Liquibase, for the last day or so to use for versioning my development, staging and production databases, which are using Oracle's ...
0
votes
1answer
38 views

Deploying Oracle ADW Changes from development environment to production

I'm currently running an Oracle Autonomous Data Warehouse (Oracle's cloud-based database offering) on version 19c. I have a development database on the Always Free Tier and my production database is a ...
0
votes
1answer
27 views

How to design a table structure for customer and providers? [closed]

The schema is showing a product distribution model from provider to customer. The circles are small providers. Rectangles are main store providers. Small providers ships proudcts to main providers. ...
0
votes
0answers
33 views

Adding a new table to a database - are my dependencies off?

So I'm adding some functionality to an existing table for a system I'm working on. It has a few foreign key relationships and it follows the snowflake design, please see below. I've censored out the ...
1
vote
1answer
28 views

Optimal mySQL design for 500k+ products with up to 50 specs

I facing a problem where I have to design a MySQL database capable of containing +500k products in approx. 1.500 categories, that can hold up to 50 specs. (like size, weight, speed, color, etc). My ...
0
votes
0answers
27 views

Can a junction table have more than two foreign Key?

I have to design a schema that involves entities. Schools, Teachers, Students, Classes, Sections, Subjects, Departments, Timetables Below is the schema that I have designed. SchoolsClassesSections // ...
0
votes
1answer
15 views

Update main and second table with a before update trigger for message logging

I currently have the following testing schema: CREATE TABLE userMessage ( message_id BIGSERIAL PRIMARY KEY, creator_id BIGINT NOT NULL REFERENCES "User" (id), created_at BIGINT ...
0
votes
1answer
31 views

Reordering rows in a chapter table when creating, sorting, and deleting

I am creating an application where an Author can create a Book with say 10 Chapters, and let Readers read it. But I feel that the Author should be able to rearrange Chapters in order, sometimes DELETE ...
0
votes
0answers
14 views

How to move all schemas from postgres to userdb(user createdb database) in Postgres 11

End users are created all tables in PostgreSQL default database also in public schema. As per best practice, I want to move all tables from public schema of "postgres" database to user created ...
0
votes
0answers
19 views

Choosing database type and schema for rental availability

I'm looking at what type of database and schema is best to use for product rental availability. The requirements are simply that it needs to cater for high writes and low reads. So I've been pushed ...
0
votes
0answers
13 views

MySQL - Joining table with different charset in a view

I want to join tables from two different schemas. But the problem is both schemas follow different charset utf8_general_ci & utf8mb4_unicode_ci. When i do a join on both tables, it works. But, ...
1
vote
1answer
46 views

Unable to replicate schema changes in transactional replication

I have implemented a transactional replication on one of my dbs. In the documents provided by Microsoftsql (here and here), I should be able to change the schema and that these changes will be ...
1
vote
1answer
16 views

Why does there have to be a tradeoff between optimizing a DB for transactional data and optimizing a DB for analytical data?

I'm a data scientist who's more on the math and modeling side of things than the engineering side, and I don't know much about the logic, data structures and design principles that underly database ...
0
votes
0answers
20 views

Altering big table with gh-ost on Google Cloud

We need to add some indexes and one additional column to a large MySQL table. MySQL is Google Cloud SQL version 5.7.25-google-log. Some table info: currently holding 1 331 363 322 rows size of the ...
0
votes
1answer
31 views

Better jsonb schema for querying in postgres

I have a JSON with the following structure: uid | item 1 |[{"id":1, "m":"123", "s":1},{"id":2, "msg":"on","s":2},{"id":3, "msg":"of","s":1}] 2 |[{"id":1, "m":"yes", "s":2},{"id":3, "msg":"gh","s":...
0
votes
1answer
47 views

relation “table” does not exist

i created the following schema , inside that schema i created a table and inserted a some values. After that i created a function that get a value in the table based on an ID. Here is all the ...
0
votes
1answer
19 views

How to implement database schema for tracking the status of a purchase?

It's an internet stop, physical products with delivery. I'm trying to figure out how to implement transactions/payments, fulfilment, shipping, payments, refunds, etc. Namely, a status of each sale. ...
0
votes
2answers
32 views

How to create a table with a field 99% INT; 1% STR values?

I am creating a table in PostgreSQL and looking for a best approach that I should follow. One of my fields are mostly consists of integer values. But really mostly. Like: 1,2,3,4,5,6,7,8,9,10,11,12,...
0
votes
0answers
13 views

schemes based on 2-Phase Locking (2PL)?

I wanted to ask, what are the schemes based on 2-Phase Locking (2PL) approach that are well suited for data consistency for distributed database systems. I was looking up the answer for this online ...
1
vote
0answers
37 views

SQL Server Replication How to keep primary key schema at publisher

I have implemented Transactional replication and want to have similar schema structure at both subscriber and publisher end. But while creating the publication, Not For Replication appears in front of ...
1
vote
0answers
22 views

PgAdmin: backup and restore schemas using the GUI

I know how to backup and restore one or more schema(s) from a PostgreSQL database using psql: Go to the folder that contains the executables of PostgreSQL: cd C:\Program Files\PostgreSQL\11\bin ...
1
vote
0answers
29 views

How to structure SQL data model to maintain data integrity

Background I'm building a Private Chef booking service where you can book a Chef to cook you a custom Menu. I'm having trouble creating a SQL db schema that accurately represents the domain while ...
2
votes
1answer
36 views

SQL Server - dump schema into separate files keeping installation order

While dumping SQL Server's schema, I have these two options: single file or multiple files. Single file is good because is can be used to install schema on clean server - parents are installed before ...
2
votes
1answer
70 views

How to backup PostgreSQL database (a schema) where a user has access to only one schema

I have created a sales schema in Postgres. (so in total there are sales and the default public schemas) I added the user salesuser to Postgres, and granted it the access to the sales schema. This is ...
0
votes
0answers
9 views

database restore with cross foreign keys on different schemas/databases

As I read on this question Mysql allows cross-schema foreign keys but how to deal with database (schemas) restore? I mean, if table A (schema 1) has a foreign key on table B (schema 2) and table C (...
1
vote
1answer
83 views

Designing a multi-tenant database for a scenario with multiple user types

I'm developing a SaaS application for recruiting which has multiple user types such as Client, Recruiter, Panellist and Owner etc. There is also a chance that the user types may increase. The Owner ...
0
votes
1answer
43 views

Best way for naming schemas in SQL Server 2019 [closed]

Would like to ask about using of schemas in Sql Server 2019. For now we are using numbers as schemas for procedures and functions: 0,1,2 etc. to version procedures and functions. For example [1].[...
0
votes
2answers
38 views

How to change/add to schema in a SQL Server Always On Availability Group?

I have a 2-node Always On Availability group cluster in SQL Server 2016. I am wondering how should I go about adding a new table, or modifying the schema of an existing table in an Always On ...
0
votes
1answer
59 views

How can I fill in a foreign key value after creating the first entry of the table the foreign key is referencing?

I am seeking to create an online store so I got to the database definition. I want to have as few tables as possible, so I've got them narrowed down to: Users Products Orders Cart_Items I got to the ...
0
votes
2answers
53 views

Designing One-To-One Relationships

To begin with, I have to admit that I don't have great experience in designing database schemas. Being influenced by OOP , I find that by dividing the info we have about a object into different ...
0
votes
1answer
17 views

Why does the SHOW FULL COLUMNS command show the Type field in lower case?

When I run SHOW FULL COLUMNS, it shows the Type column in lower case, such as text and varchar(255). I thought these type names were supposed to be upper case (TEXT and VARCHAR), per https://dev....
0
votes
1answer
28 views

Query another schema on a different sid Oracle

I have 3 db's that should be in sync with each other, but there are some differences that I am trying to rectify. We try to stay out of the main schema and do all of our work on a separate user ...
0
votes
0answers
25 views

Is this a pattern or can I improve this schema?

I inherited a MySQL 5.6 database that I am trying to optimize in terms of structure and speed. Quick background: Users submit their daily time records for the day through a application which goes ...
0
votes
1answer
51 views

How is this called in a snowflake schema?

I'm learning the basics of DWH modeling and I didn't find a name to refer to what I colored in red in the image below, i.e. a group of tables related to each other, with one of them being related to ...
0
votes
0answers
30 views

Need an ERD of the schema mentioned

Need the erd of the schema for doctor, patient and appointment database. scenario is given as under: Clinic Management Entity-Relationship Model -Doctor has a unique id, name, address, qualification ...
0
votes
1answer
76 views

Can a single field reference two PRIMARY KEY (or UNIQUE) fields of two different tables? [closed]

These are my tables: CREATE TABLE uregister( u_id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL, name VARCHAR(150) NOT NULL, u_mail VARCHAR(150) NOT NULL, u_password VARCHAR(8) NOT NULL, PRIMARY KEY(...
-2
votes
1answer
106 views

Why not nested schemas in PostgreSQL? [closed]

I only use PostgreSQL, and never will switch databases softwares ever again in my life, so I don't care about being compatible with some theoretical "SQL standard" which for all practical purposes ...
0
votes
0answers
23 views

Unable to view all schema and tables in Postgres DB in SQL Developer via a Putty Connection

I am trying to connect to a Postgres Database via an SSH connection. I am able to connect via SQL Workbench but when in SQL Developer, though the connection is successful I am unable to view any ...
1
vote
1answer
38 views

Two substantially different but similar cases: Are they the same in terms of table cadinality?

Imagine these different cases when designing a database: Case 1: One book (only one instance of a title physically) can be borrowed by many people, but only one person at once but many people during a ...
0
votes
1answer
20 views

Is there a way to specify which schema the database task “Export Data-tier Application” exports for the other entities besides the tables?

I noticed in the Export Data-tier Application database task, you can specify which schema to export data from in regards to tables, but I also want to exclude certain schemas for the other object ...
1
vote
0answers
24 views

Default Schema not being applied even after setting default schema and default DB

Hello Stack Exchange! I have an interesting problem! Let me set the stage for you: 1) Windows Authentication 2) Default Schema identityiq has been set for the user with login XCORP\USERA 3) ...
0
votes
0answers
28 views

Is it possible to have SQL server look for entities under other schemas than DBO when the schema name isn't specified in the query? [duplicate]

Not sure if this is a thing, and I understand it's better practice to write your queries using the two-part naming convention, but it would be cool if SQL Server had a way to setup a schema priority ...
1
vote
0answers
28 views

CMS - Database Schema Review / Questions [closed]

Database Schema Review for CMS I'm in the midst of creating a Laravel application that will (hopefully) rival WordPress. The thought process is quite similar. Developers are able to create resources (...

1
2 3 4 5
18