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

Strategies for Database Schema Redesign on Un-Normalized Group of Order Tables in Inventory System

Being my first database-driven and SQL powered program, I'd created this more or less on-the-fly, learning as I go.. trying to be cautious- but more or less created with the intent of simply working.....
0
votes
2answers
38 views

Copy a database to a new schema in an existing database

Just curious if there is a way to create a new schema that is based on an existing database. Assuming the existing database has .dbo schema only.
5
votes
1answer
263 views

Why do user defined scalar functions require the schema?

When creating a user defined table function, or a user defined procedure, you can call the function or procedure as named. However, a user defined scalar function requires that you call it with the ...
1
vote
1answer
25 views

List user-defined schemata

If I create schemata using... CREATE SCHEMA [schema_name] AUTHORIZATION dbo ...is it possible to obtain a list of these user-defined schemata? I can see that there's an INFORMATION_SCHEMA.SCHEMATA ...
0
votes
0answers
22 views

Database schema for a interactive novel

I'm trying to create an interactive story telegram bot on Node-red. To store the story I'm using SQLite How it works: Bot shows you a piece of the story, then shows some answers (buttons) regarding ...
0
votes
1answer
41 views

Enforcing “one-to-one-or-more” vs. “one-to-zero-or-more”

Suppose that I have a mini-world consisting of three types of entities, A, B, and C, subject to the following constraints: each A is associated with 0 or more B's, and each B must be associated with ...
0
votes
1answer
21 views

Set default scheme in Redshift

I'm trying to set the default schema to a user in Redshift with alter option, because I do not want it to write to the public schema by default. I am using the following order: alter user user_1234 ...
2
votes
2answers
22 views

How to design a database for search history with multiple facets?

I want to show users a history of their searches, which can often be complicated: any of these terms: [a,b,c] all of these terms: [d,e,f] not these colors: [h,i,j] Since these searches are done ...
1
vote
0answers
32 views

Setting up an database for an electrical system [closed]

I am trying to set up a database to help working on an electrical system. However, I am not sure of what and how to set up the tables due to the complexity of the schematic diagram. Following is a ...
0
votes
0answers
39 views

Schema: Table with many rows vs single table with many columns

I'm torn between two different schemas and would love some of your thoughts. Essentially, we have a list of items, and each item can have a bunch of extra boolean attributes, currently about 15 ...
3
votes
0answers
28 views

Will my database index be more performant with a smaller varchar size?

We're using Mysql 5.7 We have an index over a varchar(255) column in a table of ~ 500M rows. select distinct varchar_column from table yields seven results, with the max length 21. A DBA consultant ...
0
votes
0answers
16 views

Schema - Can a Decision inherit attributes from unlinked entities

Drawing an EER Schema for a consulting firm, a process involves a Performance Manager (PM) evaluating the work of a Consultant on a Project. The evaluation tracks the name of the consultant, PM, and ...
0
votes
0answers
57 views

Store large amounts of text is Postgres

I have a large text corpus that I am storing in Postgres. A table articles has all of the metadata for each article, about 19 million rows. It's primary key is articles.doc_id. Another table (texts) ...
1
vote
2answers
20 views

postgresql 9.* - user cannot SELECT from VIEW inside a SCHEMA as DEFAULT PRIVILEGE

hope you can help me. I have 3 users First is me, the db admin. I run the GRANT queries, I can do anything. (name doesn't matter now) Second is the data manager user. = manager_u Third is the data ...
1
vote
2answers
87 views

Why do experienced SQL Server DBA's qualify examples with the default schema dbo? [closed]

In PostgreSQL like in SQL Server we have a default schema under a normal install. In PostgreSQL it's public. CREATE TABLE foo ( int a ); SELECT * FROM foo; Assuming a default SEARCH_PATH (which is ...
0
votes
3answers
73 views

How do I move all tables from one Postgres schema to another

I want to move all tables from schema xyz to the default schema public. I can move individual tables with ALTER TABLE table_name SET SCHEMA public; And I can get all tables with SELECT ...
0
votes
1answer
19 views

How to map single-products to categories in my scenario?

I have the tables depicted in the following diagram: Let's say I have only two products in my database at moment, one that has variants, while the other does not have them. Now my question is: how ...
1
vote
1answer
46 views

Should address be in a separate table?

I have a database that is for managing rental properties It has a property table that has details about the property such as bedrooms, sq feet, price, etc, it also has a column called address_id ...
0
votes
1answer
32 views

Schema for stores and groups of stores and companies

Perhaps a silly question. I am building a database where I keep a number of stores in a Store table. Each store obviously have some data as geo coordinates, address etc. Right now these stores are ...
0
votes
1answer
46 views

How to grant permissions to database but restrict to certain schemas?

I am new to database administration and work within a small team of data analysts trying to modernise. We have recently set up an Azure SQL DB server we are accessing using SQL Server Management ...
1
vote
0answers
78 views

How do I add and/or modify $jsonSchema for a MongoDB collection from within the NodeJS driver? [closed]

I would like to use the MongoDB native schema functionality in a Node application using the MongoDB NodeJS driver. I do not want to use Mongoose for this. Mongoose has it's own implementation of ...
1
vote
0answers
25 views

What are the most effective schema migration strategies?

I recently came across a migration system that relies on incremental numbers, so the scripts are executed in ascending order. A folder containing a few scripts might look like: Script1.sql ... ...
2
votes
0answers
31 views

A multiple choice question, with multiple or only one correct answer, and sometimes no correct answer

It's a question & answer based model, and here is the scenario: A question with multiple choices given, with 3 to 7 correct answers among those choices. A question with multiple choices given, ...
0
votes
1answer
34 views

Is it possible to use variables in MySQL schema definition?

I need to do something like this: use mydb; SET @DBNAME = 'users'; CREATE TABLE `some_table` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `user_id` bigint(20) unsigned DEFAULT NULL, ...
0
votes
0answers
71 views

Creating a Database for Multiple Calendars Support

I am trying to create a calendar application that can support multiple calendars, for instance Indian, Nepali, Russian etc. Users can enable or disable the calendars according to their needs. I have ...
2
votes
1answer
91 views

Can't create read-write Postgresql user

I'm trying to create two users in a Postgres database—one with read-write access to all tables in two schemas, and one with read-write-create (i.e., able to make DDL changes) to the same schemas. I ...
0
votes
0answers
27 views

Joining multiple (10+) tables IN CASE attribute exists, min 1, max all

I've asked a version of the question here Allow Null Columns vs separate 1-to-many tables and here Multiple JOINs of possible properties in a 'flower' arrangement - acceptable design? but ...
1
vote
2answers
42 views

Way around Composite Primary Key requiring Composite Foreign Key

Here is a table called Make (where name is the PK) name|other columns ---------------------- A | ... B | ... C | ... And a table called Type (where make, name together is the PK and make ...
0
votes
1answer
123 views

EER Diagram - subclasses which are neither disjoint nor fully overlapping

Suppose we are writing an enhanced entity-relationship diagram (EERD) for a company's database. This company offers two services A and B. If the two services were disjoint, then this might look ...
0
votes
0answers
16 views

Syncing DDL accross Postgres shards

Our database writing needs exceed the capacity of a single Postgres cluster (since each cluster can only support 1 writer server). To combat this, we've sharded our data across 8 separate Postgres ...
0
votes
1answer
48 views

Oracle set schema in a PreparedStatement

Using an Oracle Database through a Java application, I need to execute a SET CURRENT SCHEMA statement using a PreparedStatement to overcome potential SQL injection. So, I cannot do public void ...
1
vote
2answers
87 views

How to implement a dynamic referral program?

Example: Currently, we will provide 100 credits each to both the referrer and referred. Then in the future, we might want to implement a different referral program. Such as change credits to 200 ...
2
votes
0answers
35 views

Windowed IDENTITY column definition? [duplicate]

Is it possible to create a windowed identity column that automatically initializes and increments in SQL Server 2017 or later? I would like the conceptual functionality of SelectedSubsetId below. ...
0
votes
0answers
23 views

How to effectively, and efficiently and the simplest way to partition and move data from one db to another?

We have a large database with client data, with many entities, but we dont allow connections to the production db (mysql) due to security. Only option remaining is to move some data to development db (...
0
votes
0answers
36 views

How to improve query performance through denormalization of this schema or other techniques?

My database attempts to describe some items, their sources and a variety of restrictions on who may use them as well as a number of intrinsic properties about the items. It looks a little bit like: ...
1
vote
1answer
42 views

Should I use a RDBMS JSON field or a NoSQL database to store large JSON datasets that I don't need to query?

I am building a website that allows users to retrieve a bunch of open source chart (eg UN data). I don't need to be able to query the data itself. I will instead extract certain meta data (keywords, ...
1
vote
0answers
31 views

Database pattern with two types of owners for an object. Like how users and groups can make posts in FB [duplicate]

Please help. There is a problem I have tried to resolve for some time now. I want to come up with a pattern where i have a table with advertisements on a web platform. These advertisements shall be ...
2
votes
2answers
587 views

How to grant user rights to create stored procedures in a custom schema

I'm trying to grant SQL Server login rights to create stored procedures and bind them to a custom schema. In this case I've created a schema called IC. A service account will then be added to the ...
0
votes
1answer
31 views

Am I on the right track for a customer user login database design?

I am designing a back end database that will handle registration and login. I need to be able to deal with different account and authentication types. The main issue here, that is different from ...
1
vote
0answers
76 views

Restrict postgresql's public schema's tables visibility in tableua

My requirement is to display only one particular table or materialised view when user give db_host, db_port, db_name, db_username, and db_password to tableau. Currently tableau is showing all the ...
0
votes
0answers
38 views

Multiple JOINs of possible properties in a 'flower' arrangement - acceptable design?

I have a functionality that has multiple (7+) potential properties to be added via a central 'hub' table. In most cases there will only be one property assigned, but it's possible the user will want ...
5
votes
0answers
107 views

Which data model/schema to apply to a timeseries datastore for data sources with varying fields

I'm asked to develop a data-storage for time-series data, yet despite significant research I'm unsure about the data model and storage technology to chose. About the data The source data which is ...
0
votes
1answer
35 views

FK relationships between schemas in azure SQL don't work

I'm having an issue with migrating my SQL 2014 database to an azure database. I'm able to get everything migrated except for any FK relationships between tables that aren't the in same schema. for ...
1
vote
2answers
458 views

SSMS does not open popup wizard to create a new schema

When I right click on 'schemas' and then on 'create new schema' my SSMS only opens the query editor but on every tutorial I saw a pop up wizard opens instead. How can I access this popup wizard? I can'...
0
votes
2answers
99 views

ORA-22285 Error while trying to register an xsd schema on oracle database

Here is the code for user "system" create or replace directory xml_mape as 'D:\app\Gusts\xml_mape'; GRANT READ,WRITE ON DIRECTORY xml_mape to lietotajs; Im trying to access the xsd file "dok1_shema....
0
votes
2answers
38 views

How to model very flexible referencing relationships between many tables?

I have various different entity types in my database. For purposes of example, consider an IMDB database: Movie Person Actor Director Translation and 20 more tables My application enables free-form ...
1
vote
2answers
64 views

database design for song position under play-list

I have a music streaming application where I have playlists. Each playlist has maximum 100 songs. I have a mapping table called PlaylistSongMapping which has the following schema +------+------------+...
0
votes
0answers
16 views

Postgres - Storing Attributes in 1:0 related object, performance improvements?

In my DB's central table, "Profiles," I want to collect activity statistics that will be updated often, although not all profiles will have statistics. The "Profiles" table is already heavily updated ...
0
votes
0answers
17 views

Saving records in MySQL that does not pre-exist

I have asked this question on SO but believe this is the right forum to ask, so here it is Let say, I have a pre-defined table called cities, with almost all the cities in my country. When a user ...
0
votes
2answers
32 views

How to model a relationship with different roles of users

I am creating a new project and currently modeling the relevant business domain. I am hesitating to choose one solution over another. Consider the following case. There is an order, the order has a ...