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

0
votes
0answers
17 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
16 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
votes
1answer
11 views

Ecommerce database products with different sizes i want them to have different prices [closed]

im creating an ecommerce site with only 9 products, each product has three sizes each size is a different price how would i structure the db? or what tables should i create. Should i create one ...
1
vote
0answers
23 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
39 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
33 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
17 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
37 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
46 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
47 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
37 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
22 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
25 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
19 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
12 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
39 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
44 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
26 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
39 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
16 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
77 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
52 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
40 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
52 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
136 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
98 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
72 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
41 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
77 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
34 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
561 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
64 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
102 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
213 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
36 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 ...
0
votes
0answers
17 views

Rename a schema that contains an extension used by another schema

How can you rename a schema containing an extension without breaking tables in other schemas that rely on this extension (PostGIS)? I'm trying to temporarily rename a schema "dest" to "public" in ...
1
vote
0answers
59 views

What is a good schema for a database where users can search for inventory availability on certain dates (such as Airbnb)?

I apologize if this is not the right venue to ask such a question. This is my first time on this site and I'm open to suggestions. I am trying to find a good schema for a database that needs to ...
0
votes
1answer
30 views

Relations and Sets

From my understanding, One = {1,2,3,4,5...20}, and then if Two = {1}, then One won't map into Two. On that token, how come the answer is d instead of a?
3
votes
1answer
86 views

Database slows down at millions of rows, how to optimize schema and queries?

I am building a database to store Ethereum Transactions that are connected to specific addresses. Once the database table eth_addresses_tx starts hitting 3 million + rows, things start slowing down a ...
1
vote
0answers
24 views

How to design databse for storing object states in mysql?

I have multiple objects with multiple states. I want to build a timeline for each object. For example: an object might have a created, submitted, processing and success state. I would like to store ...
2
votes
2answers
60 views

Are the any inherent shortcomings in this schema?

This is derived from the SQL generated by sqldbm.com that I simplified because it wasn't generating foreign keys correctly and it seems to me that there's no need to pull in every foreign key from the ...
1
vote
2answers
133 views

Manually installing sample schemas in Oracle 11g

I am trying to manually install HR schema in my oracle environment by following the steps specified by oracle Help Center (11.2). Web link: https://docs.oracle.com/cd/E11882_01/server.112/e10831/...
2
votes
1answer
41 views

Creating a lost and found schema

I have a school assignment to create a lost and found schema. An admin can log a found item (someone turned it into the lost and found office) A user can report a lost item A user can claim a found ...
0
votes
1answer
46 views

Database schema for large possible product combinations and associated price [closed]

I'm new to database schemas and need some help. I have to draw up a database schema from a large, (2601) possible product combination, with width and height and associated price. Is creating ...
1
vote
0answers
34 views

Designing a database for the academic activities of individuals or departments

I have a database which stores the academic activities in which some individuals or departments are involved. An activity could be any among some of these types: article (written) course (imparted) ...
1
vote
1answer
55 views

Circular references for tracking currently active entity

I want to store active entity in one of my tables let's say I have 2 tables: Pirate Ship Named it like that to keep things interesting I want each pirate to own many ships but have only one active ...
1
vote
2answers
72 views

Designing a database for Questions, Options and Answers

A practice in trying to adhere to relational database techniques. Can anyone give me a little bump as to what I'm doing wrong/how this should be? In Short All Questions have 5 Possible Answers. That'...
0
votes
2answers
75 views

How can I combine these tables?

I have my system working with drinks, and now I'm ready to add burgers and other types of items. Right now I have 1 orders table with separate item and itemorder tables for each type of item. This ...