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

Generate Postgresql schema using generator like an ORM (like ActiveRecord or Ecto etc)

I'm wondering if there is a software (a generator, a web page, an executable, a command line, etc.) that allows me to generate a Postgresql scheme since I'm not an expert and I wouldn't know where to ...
2
votes
1answer
38 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
1answer
33 views

DB Schema for pricing [on hold]

I am designing a schema for pricing for an application. I am stuck on how to approach this. I have a product, it needs to get Price table to it and give me the options to set the price based on ...
0
votes
0answers
15 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 ...
0
votes
0answers
43 views

How database schema should look like for app like to spreadsheet, airtable, notion.so? [closed]

I am a front-end developer and I am curious how airtable.com or notion.so (spreadsheet) store their data in the backend. Is it a NoSql or SQL solution, or maybe something in between such as Mysql with ...
1
vote
2answers
33 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
61 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
12 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
18 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
33 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
31 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
21 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
29 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
31 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
44 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
27 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
45 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
75 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
30 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
97 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
63 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
44 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
30 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
34 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
29 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
55 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
28 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
64 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
21 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
66 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
33 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
146 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
39 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
69 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 ...
0
votes
1answer
74 views

Designing MySQL Database For Two Different User Types (Staff and Client)?

I'm creating a web-app that will connect to a MySQL database. This app will have 2 users: a Staff and Client that can both log in from the same login page. Both Staff and Client share the following ...
2
votes
2answers
103 views

How can to make a new user see table in existing schema [closed]

I'm new into Oracle so this question might be dumb. I created new user and now I would like him to see table (or even better all tables by one command) in already existing schema (hr schema in this ...
1
vote
0answers
30 views

How do you aggregate the delta of values between two arbitrary datetime columns?

Please look at the columns and types of a table called History: - id PRIMARY KEY - account_id STRING - total_spent FLOAT - date_created DATETIME Sample data for this table: (id, account_id, ...
1
vote
1answer
36 views

MySQL Schema Change Management Tools? [closed]

I am a developer who has worked with SQL Server by way of Entity Framework, I am now using MySQL by way of Node.js I am stepping into your strange world to ask an operations question I hope you can ...
3
votes
2answers
67 views

Optimization of a query on a table that represents a weak entity type

I've been trying to figure this problem out for a while now, so I decided I'll try my luck here. I have a very complex online shop DB structure (multiple shops in one database, hundreds of thousands ...
3
votes
1answer
139 views

Normalizing/combing mutliple tables with similar data

I'm working on setting up a database of vehicle records based on data from a third party vendor. There are 3 models: Vehicles, Transmissions, and Adaptors. Both Vehicles and Transmissions are 1:n ...
0
votes
1answer
42 views

Advantages of a hierarchical schema for private chat

This post claims that All modern chatting interfaces, without exception, implement a hierarchical and a not-chronological schema for chat. It also links another post which gives an example ...
1
vote
1answer
52 views

Soft-deleting change-tracked user content

To begin with, I distinguish between tables containing user-generated content and tables containing information about the user. The former are associated with the user profile, whereas the latter are ...
1
vote
1answer
44 views

Extended Entity-Relationship Diagram: Do the sub classes have foreign keys or primary keys?

Consider this example (they are disjoint, by the way): There are two scenarios which I am guessing: While translating it to relational tables, the rows in PROGRAMMER are unique which do not exist ...