Stack Exchange Network

Stack Exchange network consists of 174 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

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

-1
votes
1answer
42 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
0answers
34 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
96 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
21 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
21 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 ...
0
votes
0answers
17 views

How do I get a notification on schema changes in Sybase?

ALL, In SQL Server I can create a service_queue and then get notification about schema changes - CREATE, ALTER, DELETE TABLE or INDEX commands and then act accordingly in my C++ application. In ...
3
votes
2answers
59 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
112 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
30 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 ...
0
votes
0answers
21 views

How does an extended entity-relationship to relational mapping work in case of union type?

(This is not a homework question, this example is for my understanding) Consider the example shown in the diagram below: As I believe, Sponsor is a subclass of Club, Team and Dept, and an entity ...
0
votes
0answers
33 views

Modelling a database for a scenario with different member types that are interrelated

I have database with different member types(Dealer, Supplier, Dealer Family, Supplier Family), each type have common columns and there is some unique columns for the Dealer and Supplier types: ...
1
vote
1answer
49 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
25 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 ...
1
vote
1answer
85 views

Could not complete cursor operation because the table schema changed after the cursor was declared

I get this error message occasionally when I execute a stored procedure that pulls information from multiple linked servers and aggregates the data. It looks similar to what I have below. DECLARE Cur ...
0
votes
0answers
23 views

Schema changes in transactional replication

I have a publisher (SQL Server cluster) in one box and the distributor/subscriber together is on the other box. I have a requirement by the vendor that if there is a new build (schema/database ...
0
votes
0answers
24 views

Hierarchy of objects in Postgres: What is contained directly within “databases” (excluding schema sub-items)?

I'm building a declarative system that lets me define my projects/databases/schemas/tables/fields etc in code, as well as the instances of projects on servers, and it handles schema changes and ORM ...
1
vote
1answer
30 views

How to handle a table schema with a new relationship subtype in an interviews scenario?

I have a table of "interviews" and "interview reviews" for both the interviewer and the interviewee. When the project was started there was only the need for one type of review, which is shown below ...
0
votes
3answers
102 views

Where to find the OID of a newly created Schema

I have created a new Schema in my Postgres db. I want to replace an OID in the pg_class table with the OID of the new Schema. But I don't know where to find the OID of the newly created Schema. Can ...
2
votes
1answer
38 views

Handling unique IDs on client side

Reading around on the topic hasn't got me closer to understanding best practices. I'm also relatively new and self-taught in programming, so might have some questions about how to actually execute the ...
1
vote
0answers
21 views

schema for product prices based on membership level

I need to model the following scenario: Customers have 3 options of annual memberships: [basic:$0,silver:$50,gold:$100] We have a supplier with product A at a cost of $30 per unit. (We will have ...
0
votes
1answer
48 views

Primary key and index column for Merge replication- Id vs GUID

I currently am revising the tables structure for a few geographically distributed databases- all tables currently have a UUID that is set as primary key. Merge subscription is currently (and in the ...
0
votes
2answers
48 views

read/write/execute to pl/sql grants [closed]

For a current task and future references, I am wondering how pl/sql grants (select, insert, update, delete, references, alter, index, all) are categorized from a read/write/execute or read/read-write/...
-2
votes
1answer
56 views

Database design for a timesheet app?

I'm designing the database schema for a time tracking application and I need a little piece of advice. The application must permit the user: To enter for each day of the week the amount of time he ...
2
votes
0answers
44 views

Surrogate foreign keys allow inconsistent data - which normal form does this violate?

We have inherited a system which has surrogate keys on every table (that is, an auto-increment primary key, id). All of the foreign key constraints are on individual surrogate keys, and this seems to ...
0
votes
1answer
24 views

Organizing and assigning privileges to multiple objects in MySQL

I'm new to MySQL, moving from MS SQL Server, and I'm wondering if there's a way to logically group tables, views, sp's, etc in the same manner as you would with schemas in SQL Server....seeing as how ...
1
vote
0answers
30 views

A flexible and robust schema for storing precomputed values/metrics

Background I'm dealing with a table that contains a lot of historical data. What I am doing is generating a bunch of metrics based on different combinations of column values and date periods (e.g. ...
1
vote
0answers
20 views

How to model a database for prices that vary depending on their categories and parameters?

My first post on DBA! Advanced apologies if I made any mistake. Before jumping into the schema and tables, I would like to share what I am trying to achieve first. I am working on sort of a courier ...
0
votes
0answers
13 views

Postgres: 2 selects depending on a boolean flag

I have a codectype table where there are some codec entries. CREATE TABLE codectype ( codecid smallint NOT NULL, codecname character varying(32), stat_column character varying(64), ...
0
votes
1answer
43 views

Is it good practice to use non standard schema names in SQL Server? [duplicate]

If I have a database MyDB, is it considered good practice / bad practice to use schemas other than dbo? For example: MyDB.reports.YearlySales MyDB.reports.TopSellers MyDB.[data].ProviderX MyDB.[data]...
1
vote
0answers
96 views

How to create entity-relationship diagram for tables that can potentially be united by an “IS A” relationship?

I have this schema: create table hall( building-id int, hall-id int, capacity int, primary key(building-id, hall-id) ); create table semester( sem-id char(5), start-date date, eend-...
1
vote
1answer
30 views

Permissions for runtime user on all objects in schema (including future objects)

In PostgreSQL 10 - when using an "owner user" (login role owning a schema and all tables, not used by the application at runtime) and a "runtime" user I can GRANT Select/Update/Delete permissions on ...
0
votes
1answer
148 views

How to give read only access to a user to view the tables of different owner in oracle

I have 2 users are present in oracle ex User1 and User2, Under user1 some tables are present ex t1,t2,t3 etc.. . what are the minimum permissions to view the tables of user1 by using user2 ( Just want ...
1
vote
1answer
18 views

Permission errors on insert after schema drop/create (Pg 9.0.23)

We have a web application that allows users to modify data through an API. In order to allow people to run tests, we provide a sandbox area called 'daily'. This sandbox gets refreshed every day from ...
1
vote
3answers
125 views

What can be best database schema for multiple project and one database? [closed]

I am working on multiple projects but the back-end will remain the same. I have a different server for auth, data, etc. and one database to store all the details. If one projects are creating any ...
1
vote
1answer
120 views

How to check the default schema of WINDOWS_GROUP?

I am using the following query to check if the default schema is set to dbo. SELECT DB_NAME() AS [database] ,[name] ,[type_desc] ,[default_schema_name] FROM [sys].[...
2
votes
0answers
42 views

Design for a child to have a single parent `A` OR `B` with FK cascade constraint?

I'm assuming this is not possible with PK and FK... Let's say I have tables A, B, and C. A and B are "parents", and B and C are "children" A is the root B can be the child of either A or B C ...
2
votes
0answers
56 views

How can I find variations in database schemas? [duplicate]

I'm working with a system where each customer has their own database. This amounts to ~4000 databases (approximately 150 tables each database) spread over 8 instances of Microsoft SQL server 2012. ...
5
votes
1answer
201 views

Transfer row from 'parent' to 'child' without creating duplicate and without knowing all 'parent' columns

I want to set up a database with different schemas that correspond to different data-entry systems. There needs to be a “master” (administration) schema with a “parties” table, and multiple other ...
2
votes
2answers
139 views

Dangers in dropping all tables in a schema with CASCADE

The answer to this question suggests I can use the CASCADE option of DROP SCHEMA to remove all a schema and all its tables, but warns: CASCADE - Automatically drop objects (tables, functions, etc.) ...
1
vote
0answers
102 views

Building a service reservation system in SQL [closed]

I am trying to build a system to book services for partners but I am a bit concerned about how can I design the architecture of the database. I need to provide free time where available « slots » ...
0
votes
2answers
67 views

How to remove multiple redundant indexes?

I have a legacy app. I wanted to find out which tables are the largest ones using script from Percona blog. It turned out some of my tables have indices that are larger than actual data by factor of 3 ...
0
votes
1answer
98 views

Is it possible to backup a subset of a database in Microsoft SQL Server?

Using Microsoft SQL Server, is there a way to backup a subset of a production database? I only want the database structure, and a very small amount of transaction data for a few selected tables. I ...
-2
votes
1answer
38 views

What's the best way to represent an event from a system in a chat?

Conversations -------------- id Messages -------------- id conversation_id user_id reply_to_id content created_at I was looking at this question and I had a question: How to build a table for a ...
1
vote
4answers
760 views

Handling currencies in a database

How should money currency be handled in the database to be consistent? Should I choose the specific currency to use across my database or is it better to mix different values of different currencies? ...
0
votes
1answer
73 views

How to import an existing schema and tablespace into a postgres database, from a filesystem-level backup

Follow-up question from: Have postgres' pg_dump export an index Say I have an existing database, and I want to add to it another schema with content. I saved that content previously through a ...
0
votes
1answer
153 views

Have postgres' pg_dump export an index

I have an index that is very expensive to calculate, and I'm looking for ways to have it be restored or transfered in a simple copy fashion. It would be done along with the underlying data of course. ...
5
votes
6answers
1k views

Storing personal data on an invoice to preserve data integrity

I want to store invoice data like addresses of people. But the problem is addresses can change over time - for example someone will move out and change his address. The problem of storing such data ...
0
votes
1answer
392 views

Django postgres multiple schema

I'm trying to develop a django interface for an existing postgresql db, the db makes use of various schemas, looking at the literature the following example should work, but it only returns the schema ...
2
votes
1answer
58 views

How to export base and discover its structure

We are with a small problem at work. Our DBA decided to go around the world on a journey of self-discovery. I really love the idea and wish the best for him. The only detail is that he left without ...
1
vote
1answer
39 views

Database Schema Access

I have a database with multiple (10) schemas. I need a user to have access to two of them. If I grant access to the database and then GRANT SELECT to 2 schemas, is access to the other schemas DENY-ed ...