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
1
vote
1answer
39 views

Unable to replicate schema changes in transactional replication

I have implemented a transactional replication on one of my dbs. In the documents provided by Microsoftsql (here and here), I should be able to change the schema and that these changes will be ...
0
votes
0answers
8 views

MySQL - Joining table with different charset in a view

I want to join tables from two different schemas. But the problem is both schemas follow different charset utf8_general_ci & utf8mb4_unicode_ci. When i do a join on both tables, it works. But, ...
3
votes
1answer
41 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 ...
1
vote
1answer
14 views

Why does there have to be a tradeoff between optimizing a DB for transactional data and optimizing a DB for analytical data?

I'm a data scientist who's more on the math and modeling side of things than the engineering side, and I don't know much about the logic, data structures and design principles that underly database ...
0
votes
1answer
95 views

Is it good to save Keywords stored by users in a new DB schema?

My question is more from the schema design and of further performance of the DB. I'm designing a DB schema which will store strings primarily. Actually, users work on these strings, as normal mortals,...
0
votes
1answer
45 views

relation “table” does not exist

i created the following schema , inside that schema i created a table and inserted a some values. After that i created a function that get a value in the table based on an ID. Here is all the ...
1
vote
2answers
274 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.
0
votes
0answers
18 views

Altering big table with gh-ost on Google Cloud

We need to add some indexes and one additional column to a large MySQL table. MySQL is Google Cloud SQL version 5.7.25-google-log. Some table info: currently holding 1 331 363 322 rows size of the ...
0
votes
2answers
711 views

Store parameters of various types

I am a (poor, young) scientist working to develop a database to allow a project to scale up. I am experimenting with MySQL and have had very encouraging experiences so far, but I am still novice and ...
0
votes
1answer
29 views

Better jsonb schema for querying in postgres

I have a JSON with the following structure: uid | item 1 |[{"id":1, "m":"123", "s":1},{"id":2, "msg":"on","s":2},{"id":3, "msg":"of","s":1}] 2 |[{"id":1, "m":"yes", "s":2},{"id":3, "msg":"gh","s":...
2
votes
1answer
366 views

.NET SQL Server Authentication Schema issue

I am currently working on a project for a client. The application is written in ASP.NET C#. The Database used is SQL Server 2008. The project has been to migrate the old Oracle database to a new SQL ...
1
vote
1answer
166 views

Default Schema Problems on server

When i connect to SSMS on my server through remote desktop and query my database using: SELECT * FROM [sys].[database_principals] WHERE [principal_id] = USER_ID() I am getting the correct results (...
2
votes
1answer
8k views

Permission in schema for user

I created a default database and created some schemas within this database. Created users for each schema, now I want to give full permission for each user in your schema only, how should I do?
4
votes
2answers
439 views

Is EAV an appropriate design for a collection of coexisting metadata-rich “fact estimates” on objects? [closed]

I'm attempting to architect a system that will collect estimates on various quantities (10+) on each of many (300K+) objects, and make decisions from the historical record of these estimates. For ...
2
votes
3answers
4k views

Workarounds for setting default schema for domain group?

I am currently working on a project for a client, they have asked me to change the database from Oracle to SQL Server. So we migrated the database across to SQL Server 2008, when this was migrated (...
4
votes
2answers
487 views

Is there a way to force inheritance of a procedure's schema to all procedures called within it

If I have a stored procedure schema1.proc1 and it selects from table1, it correctly selects from schema1.table1. However, when schema1.proc1 executes proc2, it tries to find and execute dbo.proc2. ...
0
votes
1answer
19 views

How to implement database schema for tracking the status of a purchase?

It's an internet stop, physical products with delivery. I'm trying to figure out how to implement transactions/payments, fulfilment, shipping, payments, refunds, etc. Namely, a status of each sale. ...
1
vote
2answers
98 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 ...
2
votes
1answer
153 views

What would be an appropriate schema for this real life scenario? [closed]

A Company C provides an enterprise product to organisations (O1, O2, O3... On) and their sub-organisations (SO1 ... SOn). The product offers the organisations a database of content which is created by ...
0
votes
2answers
31 views

How to create a table with a field 99% INT; 1% STR values?

I am creating a table in PostgreSQL and looking for a best approach that I should follow. One of my fields are mostly consists of integer values. But really mostly. Like: 1,2,3,4,5,6,7,8,9,10,11,12,...
0
votes
1answer
87 views

Re-posting schema

I'm building a social network very similar to Twitter, in which users can see a feed with content posted by users they follow. Users can like and re-post (i.e. "Retweet") posts. Re-posted content ...
1
vote
0answers
37 views

SQL Server Replication How to keep primary key schema at publisher

I have implemented Transactional replication and want to have similar schema structure at both subscriber and publisher end. But while creating the publication, Not For Replication appears in front of ...
0
votes
0answers
13 views

schemes based on 2-Phase Locking (2PL)?

I wanted to ask, what are the schemes based on 2-Phase Locking (2PL) approach that are well suited for data consistency for distributed database systems. I was looking up the answer for this online ...
3
votes
1answer
1k views

speed up PostgreSQL calculations via OLAP warehouse?

So I have asked these three questions: Percentile rank that takes sorted argument (or same functionality) in PostgreSQL 9.3.5 https://quant.stackexchange.com/questions/29572/building-financial-data-...
1
vote
0answers
20 views

PgAdmin: backup and restore schemas using the GUI

I know how to backup and restore one or more schema(s) from a PostgreSQL database using psql: Go to the folder that contains the executables of PostgreSQL: cd C:\Program Files\PostgreSQL\11\bin ...
0
votes
1answer
849 views

Is it possible grant a PRIVATE Synonym from an the Table Owner schema to an User schema?

Is it possible grant a PRIVATE Synonym from an the Table Owner schema to an User schema? Background: We are doing microservice development and there is an sole owner schema and many different service ...
1
vote
0answers
29 views

How to structure SQL data model to maintain data integrity

Background I'm building a Private Chef booking service where you can book a Chef to cook you a custom Menu. I'm having trouble creating a SQL db schema that accurately represents the domain while ...
2
votes
1answer
34 views

SQL Server - dump schema into separate files keeping installation order

While dumping SQL Server's schema, I have these two options: single file or multiple files. Single file is good because is can be used to install schema on clean server - parents are installed before ...
1
vote
1answer
60 views

Designing a multi-tenant database for a scenario with multiple user types

I'm developing a SaaS application for recruiting which has multiple user types such as Client, Recruiter, Panellist and Owner etc. There is also a chance that the user types may increase. The Owner ...
2
votes
1answer
39 views

How to backup PostgreSQL database (a schema) where a user has access to only one schema

I have created a sales schema in Postgres. (so in total there are sales and the default public schemas) I added the user salesuser to Postgres, and granted it the access to the sales schema. This is ...
3
votes
1answer
152 views

Schema for scarce goods

I have following objects: (Rooms) which are (available in a certain period of time [in days]). During the time of availability they can be (booked) by (users). There are basically these four tables. I ...
0
votes
0answers
9 views

database restore with cross foreign keys on different schemas/databases

As I read on this question Mysql allows cross-schema foreign keys but how to deal with database (schemas) restore? I mean, if table A (schema 1) has a foreign key on table B (schema 2) and table C (...
0
votes
1answer
58 views

How can I fill in a foreign key value after creating the first entry of the table the foreign key is referencing?

I am seeking to create an online store so I got to the database definition. I want to have as few tables as possible, so I've got them narrowed down to: Users Products Orders Cart_Items I got to the ...
0
votes
1answer
43 views

Best way for naming schemas in SQL Server 2019 [closed]

Would like to ask about using of schemas in Sql Server 2019. For now we are using numbers as schemas for procedures and functions: 0,1,2 etc. to version procedures and functions. For example [1].[...
0
votes
1answer
17 views

Why does the SHOW FULL COLUMNS command show the Type field in lower case?

When I run SHOW FULL COLUMNS, it shows the Type column in lower case, such as text and varchar(255). I thought these type names were supposed to be upper case (TEXT and VARCHAR), per https://dev....
6
votes
2answers
12k views

Understanding how to choose between fields and tags in InfluxDB

What are some good rules and examples of how to choose between storing data in fields vs. tags when designing InfluxDB schemas? What I've found so far is: a measurement that changes over time ...
0
votes
2answers
34 views

How to change/add to schema in a SQL Server Always On Availability Group?

I have a 2-node Always On Availability group cluster in SQL Server 2016. I am wondering how should I go about adding a new table, or modifying the schema of an existing table in an Always On ...
0
votes
2answers
52 views

Designing One-To-One Relationships

To begin with, I have to admit that I don't have great experience in designing database schemas. Being influenced by OOP , I find that by dividing the info we have about a object into different ...
0
votes
1answer
51 views

How is this called in a snowflake schema?

I'm learning the basics of DWH modeling and I didn't find a name to refer to what I colored in red in the image below, i.e. a group of tables related to each other, with one of them being related to ...
0
votes
1answer
28 views

Query another schema on a different sid Oracle

I have 3 db's that should be in sync with each other, but there are some differences that I am trying to rectify. We try to stay out of the main schema and do all of our work on a separate user ...
0
votes
1answer
116 views

Storing revisions and relations between revised items in 1:N and N:N scenarios for audits

I have several of the following situations in a MySQL schema that I design for a custom CRM/ERP solution. 1:N related tables SO and SO_ITEMS (SO is just one example I have several) that both need to ...
0
votes
0answers
25 views

Is this a pattern or can I improve this schema?

I inherited a MySQL 5.6 database that I am trying to optimize in terms of structure and speed. Quick background: Users submit their daily time records for the day through a application which goes ...
1
vote
1answer
891 views

Inventory Database Design? Get historical data from DB for reporting & accounting

Following is the design of my db: Product: ID Name Quantity in Hand (as on date - total across all format) Reorder_point Remarks Warehouse: ID Name Address Remarks is_default Inventory: ID ...
0
votes
0answers
29 views

Need an ERD of the schema mentioned

Need the erd of the schema for doctor, patient and appointment database. scenario is given as under: Clinic Management Entity-Relationship Model -Doctor has a unique id, name, address, qualification ...
0
votes
2answers
158 views

Online Schema change when DB has existing triggers

I'm doing some due diligence before attempting a MariaDB schema change that really needs to be performed online, but seems like I'm going to have issues with the existing DB triggers in using a tool ...
0
votes
1answer
107 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 ...
6
votes
4answers
3k views

Postgres: modify existing search_path (preserve current values)

Sometimes I would like to extend the existing search path, rather than replace it, say: To start with say it is already set like so: SET search_path TO schema_b, schema_c, public; I want to add my ...
0
votes
1answer
75 views

Can a single field reference two PRIMARY KEY (or UNIQUE) fields of two different tables? [closed]

These are my tables: CREATE TABLE uregister( u_id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL, name VARCHAR(150) NOT NULL, u_mail VARCHAR(150) NOT NULL, u_password VARCHAR(8) NOT NULL, PRIMARY KEY(...
1
vote
2answers
156 views

Database normalization, 3 mistake [stucked] - Homework

I've been given a homework at university, which says I should make a database with 7 tables [see picture below]. The scheme I made is good, except some normalization is needed, as my tutor told me. (...

1
2 3 4 5
18