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
37 views

Is this a solid database design that passes 3rd normal form?

I am a tennis coach and created a small database to track student testing. I have several programs( beginner, intermediate, advanced). And Throughout the year/season, they are divided into four 8 ...
0
votes
1answer
19 views

SQL: Entity belongs (many-to-one) one of two different entities

Let's say I have organization and person and they have a one-to-many relationship with post. post could only be owned by either an organization or user (person). What I'm doing right now is making the ...
0
votes
0answers
14 views

Appropriate way to convert Extended ER diagram to Hibernate Entity and repository

I am new to Hibernate and don't know well how to convert EER to JPA entity and repository. Would you demonstrate how to create Hibernate/Jpa entity and repositories from this diagram . You can ...
0
votes
0answers
24 views

How to get all the extended properties inheritance and regarding this heritage apply the values of extended properties?

Hope you are well I'm been a bit stuck trying to develop the code to get the inheritance of extended properties. I will explain the situation: The thing is have a query to list all extended properties ...
0
votes
1answer
29 views

Database scheme for re-testing records

Goal Monitor records of temperatures for each room, daily. What I have created so far Firstly, this table shows the rooms and their minimum & maximum values of the temperatures they should meet. ...
0
votes
0answers
10 views

dbeaver connection cannot change driver properties schema

For the dbeaver connection, edit connection (Athena JDBC, i.e. AWS daata lake), "driver properties" tab: I cannot change driver properties schema field (default) anymore. This used to be ...
-1
votes
1answer
38 views

ClickHouse MV is not working perfectly as i need [closed]

I’m new to ClickHouse and having an issue with MV. I have a record table which is the data source. I’m inserting all the data here. Then created another table called adv_general_report using ...
0
votes
0answers
42 views

Database Scheme

I have a problem with a use case and I don't know how to design my database. Here are the detail : I have an entity "event" which can occur at different frequency but for each frequency ...
0
votes
0answers
23 views

How do i design a schema with proper DB engine to accumulate data depending on this need on clickhouse or in any other database?

We're a new Adtech company and I was planning to design a database where I'll pull all the data to a single table and then make new tables with a materialized views for others to generate multiple ...
0
votes
2answers
111 views

Oracle: Simple way to compare all objects of a schema in two databases?

I'm attempting to compare all objects of two schemas located in two different databases. I know there may be ways to do this with TOAD and SQL Developer, but is there another way to do this by script ...
0
votes
3answers
36 views

Best Practice to define has many through relationship

Given the Images. Sometimes I needed to fetch lessons related to a course without joining the topic table. It is a good way to model the Schema in the diagram at the bottom? Kindly advice.
0
votes
1answer
11 views

How to interpolate a function return of column names into the SELECT section of a QUERY [MYSQL]

I deal with tables with many columns - recently I realised I can generate a list of column names excluding one column (maybe PK), that I don't want. Like so: mysql> SET @notColumns = 'primary_key,...
0
votes
0answers
23 views

Question for my entity relationships

I'm not sure how I should think regarding my database design. I have made this database schema below. And I'm trying to think about the relationship between the entities. I have read through online ...
0
votes
1answer
26 views

Postgresql: avoid referencing schema in trigger function

I have written function that I registered in a trigger that ensure referential consistency in non-normalized data (like we store agency_id + branch_id and I ensure that a referenced branch is inside a ...
0
votes
1answer
69 views

db schema for storing multiple choices user settings

I am working on a project with the following data structure : A user need to be able to perform personal filtering on a given project, and this filtering needs to be saved (for each user/project pair)...
0
votes
1answer
28 views

Tables that Link To Many Other Tables (but only one at any time per record) [closed]

I am redesigning the application that drives our company phone system. There are various types of features within the phone application, such as menu systems, announcements, time-based routing of ...
0
votes
0answers
59 views

Is my ER Diagram correct according this business rules?

Tiny Hospital keeps information on patients and hospital rooms. The system assigns each patient a patient ID number. In addition, the patient’s last name, first name, date of birth, and date of ...
0
votes
0answers
46 views

Super complex Schema will not migrate to PaaS

Using a Dedicated 2016 datacentre SQL Server 2 vcpus, 14 GiB memory for testing. We have currently found a migration issue with the database Migration SQL 2016 to Azure PaaS. The transfer of schema ...
5
votes
1answer
328 views

How do I create an insert-only user for a Postgres table with an index?

Here's my proposed schema: CREATE TABLE Surveys ( id serial primary key, user_email citext, survey_data jsonb, created_at timestamp default current_timestamp ); CREATE INDEX ...
2
votes
1answer
171 views

Equivalent of a SQL Server schema in MySQL?

I have a modular system that is being migrated to MySQL. In SQL Server, there is this concept of the schema for grouping tables and database objects together. For example, all user-related tables can ...
1
vote
1answer
28 views

Creating a schema synonym in Oracle?

I read about the parameter _enable_schema_synonyms which allows a user to create schema synonyms. However, to enable the parameter it would require a DB reboot. Are there any negatives to enabling ...
0
votes
0answers
31 views

mysql perfromance schema events_statements_current timer units

I'm wondering if this is a known bug, or I'm just doing something silly. according to documentation, timer_start in events_statements_current is supposed to be in picoseconds. It is also supposed to ...
0
votes
0answers
100 views

How can I DENY a SQL Server Database User from seeing tables in a specific schema? VIEW DEFINITION doesn't seem to work

This is somewhat of an X/Y Problem: my original problem is that I want to use SSDT to maintain my database design, with the catch that my database design uses two schemas: The dbo schema that ...
0
votes
1answer
33 views

How can I improve this normalization to 3NF/How do you self-check normalization?

this is NOT homework, I'm just trying to improve my normalization skills. The first part is the functional dependencies i've identified and the second is what I believe the appropriate 3NF ...
0
votes
1answer
30 views

Are there any downsides to referencing tables from another schema using synonyms?

We are developing a modular system composed by an administration module and a few web apps. The database model is also split in that way: we have a schema for the admin module and another for each ...
0
votes
1answer
124 views

Can't create schema for a db with PostgreSQL

Use psql -U postgres to login. postgres=> CREATE DATABASE mydb; postgres=> \c mydb; You are now connected to database "mydb" as user "postgres". When create schema, got ...
1
vote
0answers
22 views

What should be correct DB schema when input data are kinda versioned?

I have been bit confused on how should I create a proper DB schema for my particular case - everithing I come up with is very suboptimal (duplicated columns and so). I think I'm missing right DB ...
-1
votes
1answer
31 views

Fields for storing user passwords

What coulmn heads would you need, generally speaking, for storing user passwords in a database, and what would be their datatypes be (irrespective of specific hashing/encryption algorithm used)? One ...
0
votes
0answers
25 views

Is my schema correct?

The system has 3 main entities: Staff Courses Students I will try to explain only the Student table, and I hope the rest of the data will be clear. [ Students ] student_id: Primary Key full_name: * ...
0
votes
1answer
40 views

How to design database for monitoring the time trends of article tags?

My purpose is to monitor which tags increase/decrease rapidly last week on different bulletin boards. Or which tag do new appear. The current table schema of articles is as follows: article_id e.g. ...
0
votes
0answers
25 views

Database Schema Review

I am working on a web app for an ESL institue. The app will be used only by the staff members (Not the students) to manage classes, payments, and student data. I have gathered all of the data from the ...
0
votes
2answers
41 views

Storing sports teams with multiple alternative names

I am designing a MySQL database to store some football match results and statistics in. The data for populating the DB will be scraped from multiple sources and the names of the teams will be slightly ...
0
votes
1answer
51 views

Amazon Athena database -- is there a way to generate an Data Schema for Amazon ML in an automated way

Amazon Athena database -- is there a way to generate an Data Schema for Amazon ML in an automated way (e.g. a SQL command that would generate the Amazon-format) Please see: https://docs.aws.amazon.com/...
0
votes
1answer
200 views

Database design for one payment and monthly payments

I'm working on leasing shops system and here is my database for now: I have contracts these contracts have 2 types, lease for a duration (year) and the payment will be once, and the other type is ...
0
votes
1answer
20 views

MySQL: Does the default value checked by the `CHECK` constraint?

Say I have the following schema, would the default value https://sample.com be checked by the CHECK (Website LIKE "https://%")? CREATE TABLE Company ( AccountID varchar(25), Name ...
-1
votes
1answer
55 views

What is the correct way to organize tables for a SQL view?

I'm new to data modeling. Currently working at creating a personal contact manager but I need some help with modeling the table relationships for a given view. The final view columns I'm shooting for ...
0
votes
0answers
76 views

MS Northwind Traders DB Inventory Transactions Entity relationship question

Trying to understand Northwind Traders' db schema but I cannot comprehend the relationship between: Inventory Transactions<--->Purchase Order details Inventory Transactions<--->Order ...
0
votes
2answers
29 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
52 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
42 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
23 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
57 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
24 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
19 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
28 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
48 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
110 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
94 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
11 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
395 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
2 3 4 5
20