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

Set up read-only privileges on the dbo schema, but allow creation of entities in another schema?

Is it possible to create a custom schema in a database such that users that have read-only access in that database for the dbo schema can be set up with the ability to create and modify entities for ...
3
votes
1answer
43 views

What is the purpose of “Schema Owner”?

If I create a new schema and set the schema owner to another schema, for example dbo, what is the meaning of that in regards to my new schema? Does my new schema inherit the same permissions from it'...
2
votes
1answer
32 views

ERD - Multiple FKs with only one of them being filled for each record

I'm designing a database schema for a music platform. Some of the entities as follows: track album band artist A "track" can have an "artist". It can also have a "band" which makes the artist ...
1
vote
0answers
18 views

Schema design: Anonymous user's data across registration

This is a schema design question; implementation is going to be Mongo, but answers are platform agnostic. Given an app, which has users, sessions, and items. All visitors will have a session ...
1
vote
1answer
43 views

Table design - parent with always exactly 1 or 2 children

Lets say I have the following existing tables (both with a primary key column named Id): TableA TableB Now, I need to create a new table that groups together 1 record from TableA with either 1 or 2 ...
0
votes
2answers
66 views

Specifying Order of Executing Stored Procedures

Issue: The order in which my stored procedures are called produces no errors if run independently, but when run as once, yields the following error: Msg 2627, Level 14, State 1, Procedure ...
1
vote
0answers
30 views

Database design for a customer and payment system

I need help with database schema design. Business rules We have users, each user can have listings. Each listing has one or more license. There are n type of licenses (products) Each listing can ...
0
votes
3answers
43 views

Create Synonym error for a user-SQL Server

I have a user who has permission to create synonym in my sql server 2016 database.That user also has data reader permission. When the user tried to create synonym as shown below. USE [DEMODB] GO ...
0
votes
2answers
21 views

Database layout to keep track of the “freshness” of a column value in a specific row

I have a Person with columns like name, address and bio. How do I store data in order to know the "freshness" of these columns? For instance, "for the Person Y, the name has changed 2 days ago, and ...
-2
votes
1answer
27 views

Requesting review of proposed database for Project Billing [closed]

I've had enough experience with databases to get me this far, but I was hoping someone could validate my design. Once I have this confirmed, next steps would be to implement in some tool, and use ...
1
vote
2answers
38 views

Does an Oracle user have all privileges in its schema?

I'm learning Oracle and did some testing. I used the sys user and created a table t in the scott schema and I found t's owner is scott, and scott has full access to t. I want to confirm, does scott ...
0
votes
0answers
29 views

One table to hold all ids related to one user - bad idea?

I was wandering if it's a very bad idea to have one table that holds all the ids related to one entity? For example let's say i have 3 tables each containing data about one user. Should i use an ...
0
votes
1answer
44 views

Storing dates and their history of changes and emailing - Does my database design pose any problems?

Background I'm designing some tables to allow dates to be assigned to an object so progress can be tracked on that object, along with functionality to store emails sent pertaining to these contracts ...
0
votes
1answer
40 views

is “create schema” the same way of saying “create database”?

I'm a beginner in DBMS, sorry if my question sounds dumb. I often see some textbooks says: create a schema . does it that mean "create a database"? But isn't that a schema is also a skeleton of a ...
0
votes
1answer
24 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
44 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
281 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
37 views

Laying out the database schema for an 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
43 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
72 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
31 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 ...
0
votes
0answers
41 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
30 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
194 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
29 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
95 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
170 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
22 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
79 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
34 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
49 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
150 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
43 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
35 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
118 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
315 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
34 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
47 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
160 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
17 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
68 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
119 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
38 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
41 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
48 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 ...