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
1answer
15 views

Postgresql schema does not exist

This query works fine. select icca.invoice_id as "DR Inv Reference", icca.total_ex as "Line Tax Exclusive", icca.total_inc as "Line Tax Inclusive", icca....
0
votes
2answers
25 views

Need help designing database, Create a table with additional columns/fields in the table, or create a new table for each different type?

So the tables that I have are Certificates, Approvers, CertificatesApprovers, Categories, Approvees, Status, AdditionalFields, AdditionalFieldsValues. Many Certificates to Many Approvers relationship. ...
0
votes
1answer
38 views

Do you think using ObjectID as field name is a bad practice? What can go wrong if I follow this design?

I design a MongoDB collection where I am planning to use mongo ObjectID as a field name. The ObjectID is a PostID and in the array, I track all the userID who liked or disliked the post.
0
votes
1answer
14 views

How do I handle a single `reference` to multiple different types of data?

I host a file sharing site. The idea is to take a short string (e.g. abcd) and do one of the following: serve an image display some text redirect to another url Historically, I've hosted this site ...
0
votes
0answers
19 views

running DDL and DML statements on multiple customer Mysql databases

We have a dozen customers who each have their own database (they are all on the same RDS instance). The database schema is exactly the same for all customers. When a new code deployment feature ...
0
votes
1answer
19 views

Granting select on syscat.schemata - DB2

I was wondering if it is safe to grant select rights to syscat.schemata to users on a DB2 database? What could go "wrong"? Some tool like DataGrip and DBeaver cannot load connection schemas ...
0
votes
0answers
12 views

How to represent Hierarchical data in Redshift when using OBT denormalised table design

We are loading a fairly simple dataset with 50-60 columns into Redshift in a flat One Big Table denormalised design (with just time dimension and one other split away). It will represent an ...
1
vote
1answer
16 views

How do I set up privileges for different schemas?

How do I set up 2 schemas with a total of 5 users? Schemas: hp hpc Users: dba - has full access to both schemas (hp, hpc) hp_dbo - owns all objects in hp schema hpc_fbo - owns all objects in hpc ...
0
votes
3answers
26 views

Where is it best to store a reference of the user-selected default? [duplicate]

Consider a user can have multiple addresses. There is a users table, an addresses table, and a user_addresses table that links addresses and users with a many to many relationship. Users can define ...
0
votes
2answers
40 views

MSSQL and ALTER AUTHORIZATION on table has no affect?

I have a schema called [test] with an owner X. I transferred an ownership of one table [test].[table] (ALTER AUTHORIZATION) to owner Y. Now, if I understand correctly, even though the owner X owns the ...
1
vote
1answer
25 views

PostgreSQL: Switching schema name in a loop

I am trying to switch the schema name dynamically to do some data manipulation. I am running the following. DO $$ DECLARE schema_name TEXT; BEGIN FOR schema_name in select "name" ...
0
votes
1answer
52 views

Partition switching vs schema transfer

I'm designing a database whose data will be generated daily by an ETL. After some research, I found that one can use partition switching or schema transfer to maximize the uptime of the "live&...
0
votes
1answer
9 views

create schema with optional relations

I have 4 tables that has the same columns. Tier1, Tier2, Tier3, Tier4. Their columns are id, code, and name. The complete relationship is like this: Tier1->Tier2->Tier3->Tier4 Where Tier1 has ...
1
vote
1answer
35 views

SQL: How to get all tables' columns & keys + the primary key?

Database Info MySQL 8.0.22 InnoDB The Goal I'm trying to get all tables in a database, with their column definitions and foreign keys, and primary keys on their own row for each table group. Given the ...
1
vote
0answers
12 views

MongoDB Shema to support concurrent update on a document

We were working on a project with a 300 documents with currentValue field in a main collection, in order to track the history of each document of first collection. we created another collection named ...
0
votes
0answers
27 views

“Document Failed Validation” what is wrong with my schema validation

I am trying to create a collection with JsonSchema (really its "BsonSchema") validation. This is a document that I want to insert (with bogus values). Note that Id is coming from the mongo ...
-1
votes
2answers
176 views

Many to Many - Concept

I know there are a lot questions about MANY TO MANY relationship, but none seem to work for me, because it is more of a question about the concept rather than one of code. So I need some clarification ...
3
votes
1answer
85 views

Unexpected result from PostgreSQL information schema

I have written a simple query that should show all table sizes of any given schema in human-readable format: select table_name, pg_relation_size(quote_ident(table_name)) from information_schema.tables ...
1
vote
3answers
34 views

Is it good practice to refer to a table with different names in a database schema? [closed]

We have a database currently running with a table named a certain way (let's say thing for the sake of example), and references to that table named the same way (FK would be named thing_id for ...
3
votes
0answers
41 views

How to create SCHEMA from a template?

Is there any way to create a new schema from template a similar way to creating new database? CREATE DATABASE music TEMPLATE template0; Maybe something like this imaginary snippet CREATE SCHEMA ...
0
votes
1answer
26 views

Single-entry accounting schema

I've already built a double-entry accounting schema on a previous app, and it was very complicated. For a new app I want the simplest and most elegant accounting schema possible (transaction volume is ...
0
votes
1answer
32 views

Recover dropped mysql sys schema

I dropped the sys schema while tinkering around mysql 8(current version). Is there a way to recover it. I am using ubuntu 20.04. I tried using the mysql documentation but I couldn't find anything ...
0
votes
1answer
22 views

how to associate columns between differnt tables [closed]

So i'm designing a database for my application which is basically a qustionaire. I designed my db as best as i could and i think it kinda works. The questionair isn't straightfoward. by this i mean ...
0
votes
1answer
47 views

Example of Logical Schema vs Physical Schema? [duplicate]

I've read through multiple Q&As attempting to intuitively understand the difference between logical schemas and physical schemas. Unfortunately, I've only been able to take away the following ...
1
vote
2answers
69 views

How to allow NULL in foreign keys of a compound primary key

I want to have NULLs in foreign keys of a compound primary key. This is an example of what I would expect to be valid data. product_id variant_id 123-123 ABC 123-123 NULL 456-456 ABC I cannot ...
0
votes
2answers
35 views

Anonymous and Public User Table Design in twitter like application

I have schema design problem which I am not 100% sure if my assumptions are correct. So the requirements are: I need to have 2 different type of user, anonymous and public. A user can have both ...
0
votes
0answers
8 views

Making similar points close in distance O(n) query by distance

I'm trying to create a database in DynamoDB that has many points with high dimensions. For the most part, I have designed the database to the regular access patterns. However, for a search endpoint, I ...
0
votes
2answers
22 views

What would a schema look like for setting up tables for RPG character creation restrictions?

I'm trying to figure out how to layout tables for an RPG database where information such as character race, class, and deity have restrictions on what combination is permissible. For example: Race ----...
1
vote
1answer
68 views

Permission denied for view beyond its own schema

There are two schemes rohdaten_fiat and staging_fiat. I have created a view in the latter one based on the first one. CREATE VIEW staging_fiat.udz_odx_dtcs AS SELECT * FROM rohdaten_fiat....
0
votes
1answer
34 views

Deny DDL_ADMIN permissions to a role on [dbo] schema

I have several database schemas like, [ext],[stag],etc and of course the built in [dbo] schema, and also have a role called [MyRole]. My plan is to add [MyRole] to the DDL_ADMIN role, so they can ...
0
votes
0answers
9 views

MySQL, can't see my schema's tables in the navigator

i'm new to MySQL. I just created a database and i do not get problem while running it. The problems come when i want to query it so i create another connection (?) but the navigator on schemas doesn't ...
1
vote
2answers
62 views

Schemas permission in SQL DB

I am a fresh admin in SQL DB and I need to learn how to establish the security settings in SQL DB. I have encountered an issue that is somehow unclear or even weird for me. I know that if one user has ...
0
votes
1answer
25 views

associate different credit cards with every purchase

I have a schema in which I store data regarding customer's credit cards and I want to find a way to associate each order (can have multiple payment entries -partial payments-) with its the selected ...
1
vote
1answer
40 views

Relationship design for areas, managers and general manager

Given some specifications I need to design a database for an organization. One of the parts has to do with areas. In the organization there are different areas, each area has a boss, and there is also ...
0
votes
1answer
27 views

Should we have OrderId and separate OrderNumber for E-Commerce Website or we can have only field for both?

I am working on an Online Grocery Store type of website in which I have one order table to store the order details. Here I am having confusion that whether I should have an OrderId (primary key) and ...
0
votes
1answer
29 views

Shared availability rules schema design

I'm designing a schema which includes the concept of availability rules. The rules are periods of a day when a linked entity is available. This can be the opening hours of a business, the working ...
-1
votes
1answer
57 views

Terminologies in Oracle-SQL and MySQL | Mutiple Database, Mutiple instances comparsion [closed]

In Oracle-SQL: Database = set of physical files on disk Instance = processes, daemons, and memory structures (i.e state of operation) Schema = collection of database objects, including logical ...
1
vote
1answer
49 views

EAV for status of tables [closed]

I am faced with this situation, an application that has multiple states for different entities. Entity A can have Opened, Closed and Waiting. Entity B has its own set of states, I was considering ...
0
votes
1answer
26 views

Task Management Tracker

I have this as my proposed structure for task management module I had been working on. I think I can pull off viewing projects I create with the above schema, however I do want to add the ability to ...
2
votes
1answer
65 views

Find tables with columns with empty and NULL values in Postgresql

After some research I found an example where I can find tables and its columns with NULL values. But the function also returns true when the column is empty. It gives me errors when I try to add an or ...
0
votes
0answers
55 views

MYSQL ASSERTION on same last name and dob

I have to make an assertion on this: No patients should have the same last name and date of birth and are treated by the name same doctor. CREATE TABLE Patient( SSN varchar(11) ...
1
vote
1answer
51 views

Need Help Normalizing a Database for Media Website

I've created a media website where visitors can watch trailers, view pictures, etc. Along the way, I've found that I've needed additional images for sharing, etc. I went the "easy" route and ...
0
votes
0answers
10 views

What should the schema look like for an API-based SaaS product?

I'm building a developer tool product, which will be accessible solely by APIs. Following are a few features I've identified that will be needed: Issuing/refreshing API keys Purchasing API credits ...
0
votes
1answer
15 views

ALTER SEQUENCE to use schema?

Consider tables cats and dogs in ani animals schema. During sequences creation, sequence for cats.id used schema, whereas sequence for dogs.id didn't use schema: create sequence ani.cats_id_seq create ...
1
vote
1answer
62 views

What is the best way to design a database for this specific type of 'offer' that this restaurant chain offers?

I am working on adding a few features in a mobile application of a restaurant chain and the tech stack used is Java for Android and PHP + SQL for backend. Until now I have always used NoSQL, but since ...
0
votes
1answer
52 views

What should be the schema of the database if the parent table is being referenced in more than one child table?

I have three entities in my database: Category, SubCategory, and Product. Now the thing is SubCategory is dependent on Category, whereas Product can be dependent on SubCategory or directly either on ...
0
votes
0answers
10 views

Graph Database Constraint Across Multiple Edges

Backend is Dgraph's Slash GraphQL DBaaS. Looking for a possible way to improve a portion of my schema while it is young. I have notes and allow users to add reactions to the notes. Users are linked to ...
1
vote
1answer
54 views

Weak entity having n instances as the 'one'

I'm struggling with a question, I'm just trying to figure is it even valid , if not where does it violates the rules of a weak entity relationship, I think it's not valid because how can a weak entity ...
1
vote
3answers
72 views

What are best practices for storing many iterations of a product in an RDBMS?

We are building a tool to track the prices of products over time, and using Postgres as our RDBMS. It is important that product attributes can be changed, and that the history of an product's ...
0
votes
1answer
67 views

Data Schema For Stock Control / Multi Source Inventory

I'm working on a project that involves stock control with multiple stock sources and sales channels. The overall hierarchy I've got so far looks like this; Sales Channels <---- Allocated Stock ...

1
2 3 4 5
19