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
50 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 ...
1
vote
1answer
477 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
56 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
54 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
1answer
43 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
819 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
146 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
140 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 ...
2
votes
3answers
3k 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
61 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
518 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
42 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
707 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
449 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
26 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
64 views

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

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
148 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
441 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 ...
4
votes
1answer
2k 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 ...
1
vote
2answers
179 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
382 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
19 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
295 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
519 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
39 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
87 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
92 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
32 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
5k 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
0answers
258 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
208 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
46 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 ...
7
votes
1answer
202 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 to ...
0
votes
1answer
43 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
555 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
219 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
48 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
332 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
2answers
42 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 ...
0
votes
0answers
448 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 ...
1
vote
1answer
4k 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.
1
vote
0answers
498 views

Interesting Database Designs for Product Catalogue including a Recursive Approach

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
25 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
447 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
91 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
36 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
18 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
583 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
89 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 ...
-1
votes
1answer
305 views

How to copy data and schema from oracle to ms sql

Is there any easy way to copy data and schema from oracle to ms-sql. i am using sql server 2017 and oracle sql developer Version 4.1.4.21 I need one time migration. I am using SSMA but getting ...

1 2
3
4 5
18