Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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).

0
votes
0answers
29 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
19 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
82 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
46 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
18 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
33 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
43 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
41 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
22 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
26 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
30 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
44 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
55 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
23 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
38 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
102 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
14 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
32 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
59 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
34 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
22 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
30 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
35 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
204 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
30 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
59 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
37 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
90 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
389 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
85 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
36 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
54 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
31 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 ...
1
vote
0answers
88 views

help in designing a database for medical clinic keeping record of patient visit

I am making software for patients record keeping in a clinic . I have written a peewee model for setting up tables to store record of each patient visit and patient contact and a relative table that ...
0
votes
0answers
30 views

Set up table for relationship type with both users in a different visitor table

I am a newbie in database driven application, and I just started working on a web app for my brother and parents clinic to manage patient records. So for their clinic I have one table Visitor that ...
0
votes
1answer
254 views

Query to get all schemas in a database in PostgreSQL?

I'm not looking for the command \dn of psql, but instead of a query in SQL language that selects all schemas in a given database.
0
votes
0answers
29 views

Database design of beauty center

I'm planning a database to manage the reservations of a beauty center. I'm having some trouble creating his conceptual scheme. I have 4 customers, booking, beautician and room. customers can book a ...
1
vote
0answers
171 views

Interesting Database Designs for Product Catalogue including Recursive

I am doing some work for a client and they are creating a database of products of which I need to consume product data when it's created and updated to store in a third party service and then present ...
-1
votes
1answer
22 views

Creating blog fresh tables from old tables [closed]

I am re-creating a database structure from old tables. New tables should be optimized and easy to understand. In old database the developer used video table name for storing video blogs. However I ...
0
votes
1answer
101 views

Postgresql schema only works when wrapped in quotation marks

I'm seeing an odd behaviour - if I don't wrap my schema name in quotes, the server returns [42601] ERROR: syntax error at or near "." The query is simply: select count(*) from "authorization"....
2
votes
2answers
57 views

Is there an easy way to have a non-IDENTITY, auto incrementing column based on another column?

+----+-------------+-----------------+-------------+ | ID | ProductName | ProductCategory | ProductCode | +----+-------------+-----------------+-------------+ | 1 | Item 1 | 1 | ...
1
vote
0answers
34 views

Checking if schema name is included in every sentence

I got some oracle database scripts for creating the whole database of an application, including sentences for creating tables, sequences, synonyms, inserts and updates of data, etc I need to ensure ...
1
vote
0answers
11 views

How to store a multi-lingual “dictionnary” in a NoSQL database?

I hope this is the good Stack to ask this question, pardon me if it's not. I need to categorize images and need manipulate a lot of words. I'll explain what's bugging me first and then there is a ...
1
vote
0answers
204 views

Gaming shop database schema

I'm trying to design schema for my personal project that would be an online gaming shop. I would like to ask you guys for any suggestions/tips on how could I maybe improve my current draft. Concerns: ...
0
votes
1answer
48 views

Database schema tracking patient stats over time

I want to create a database which allows to record patient reading data points like: weight, height, pressure, etc., over time. I found a database schema like the one discussed in this Q & A ...