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

learn more… | top users | synonyms

0
votes
1answer
17 views

Design for periodic data cuts

I've been building an application that has a 6-monthly update schedule, i.e. a cut of the data for H1 & H2 of each year. The previous version of this application implemented this by making a copy ...
1
vote
0answers
24 views

Need Help in building database

I am developing an app that send messages (email and sms) to users. The db must also maintains the list of messages send to users. And also the status of messages send to user like send or failed. I ...
0
votes
1answer
26 views

Migrating everything to do with a schema from one Oracle DB server to another

We have an old DB server, running Oracle 11g on Solaris. We also have a new DB server, also running Oracle 11g, but on RHEL. We want to move a schema to the new db - but we need everything it refers ...
1
vote
1answer
20 views

Schema copy from 9.5 V to 10.5

Using DB2 move utility db2move to copy schema and database from source to target server. The source server is Server1. The target server is Server2. Do I need to set up the TCP/IP port ...
2
votes
0answers
15 views

Schema for user drag and drop ordering

I'm trying to work out the schema for a system where users will be able to order items in an (unlimited length) list by drag and drop. Is there any kind of standard way of storing this ordering ...
0
votes
0answers
26 views

LCK_M_SCH_M Blocking

I have 2 stored procedures (one vendor, one third party) that are constantly (monthly) stepping on each other. They both (for some reason) acquire a schema lock when all they are doing is updating ...
0
votes
0answers
16 views

Accounting - Dimention table in mysql

I am designing a accounting and mini erp system. I use a date dimension table to track all date related data. In oracle we can have many schema for a database. so we could create each schema for ...
0
votes
1answer
44 views

ALTER TABLE blocking in MySQL 5.6

I have a question with a MySQL 5.6 master-slave setup. If I were to modify the master's schema with an ALTER TABLE SQL query, then when the slave tries to execute that ALTER TABLE statement too, would ...
1
vote
1answer
40 views

PostgeSQL 9.3: What is the difference between text and pg_catalog.text?

I have a classic 3-environments setting (dev, staging and production), each with its own RDS PostgreSQL 9.3. The client/server versions are psql (9.3.5, server 9.3.3). In order to keep the schemas ...
0
votes
2answers
17 views

Is there a way to replicate certain part of collection in Mongodb

Is there away to stream certain part of collection in MongoDB or TokuMX from master server to another server? I need to duplicate only certain collection Or if it isn't possible, are there any other ...
1
vote
1answer
27 views

can I give permission to insert into view, but not to referred table?

Here I have a view in public schema referring to the table in private schema: CREATE OR REPLACE VIEW public.products AS SELECT products.id, products.product_id, products.title, products.company_id ...
2
votes
1answer
27 views

MySQL table indexes change location [closed]

Is it possible to move the MySQL Table indexes to another location rather than "datadir" ? for example a ssd drive or memory partition ?
1
vote
2answers
28 views

Schema/Structure to handle secondary computed data

I'm trying to design a LAMP-based website for my pinball league, which plays competitive pinball every week in match play. One of the features of our league is that we convert raw scores to ...
0
votes
2answers
44 views

Can I update only the needed changes in a database?

First of all, I apologize if you can't understand very well what I am saying and it can be edit for better understanding of my post. The next example is for better understanding of databases with ...
1
vote
1answer
33 views

How can prevent writes to a Postgres schema for a period of time?

I am moving a schema from one server to another using pg_dump + pg_restore + drop schema cascade. I need to know that no applications are writing to it (or at least that the writes will fail) during ...
0
votes
1answer
55 views

copy data from public schema to another schema in postgresql

I'm creating a multi-tenant RoR app using PostgreSQL 9.3.5 running on linux (Ubuntu 14). I'd like to copy the data from my public schema to another schema. I tried: pg_dump -U my_username -h ...
0
votes
1answer
15 views

How to store external IDs from different sources?

I have a table of customers with a PK that we can use as an identifier for our internal system. These customers may belong to different companies, each of which may have an external ID in some ...
4
votes
2answers
122 views

How to store immutable, unique, ordered, lists?

I have an entity with millions of instances. Each instance has to reference an ordered list of items. The lists have to be unique so no list will be stored more than once. But once created, both lists ...
0
votes
1answer
42 views

SQL Server 2012 how to create new schema from existing database selecting only subset of database objects?

I'm looking for a way to create new schema from existing database selecting only subset of database objects. I.e. I want to have some "database representation" which only includes subset of tables ...
1
vote
1answer
47 views

Composite Foreign key to single primary key [duplicate]

How do I create a composite foreign key to a table with a single primary key? For example suppose I have the following tables: CREATE TABLE Branches ( BranchId UniqueIdentifier DEFAULT ...
0
votes
1answer
24 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 ...
0
votes
0answers
38 views

How to determine a schema version at runtime

Is there a portable way or best practice to access a version number for a schema at runtime? Rationale: you have multiple systems (development, QA, etc.) where you push schema changes from one system ...
0
votes
1answer
29 views

Rescue truncated data from incorrect column type

I have mistakenly inserted a variable 18-20 digit variable into an unsigned INT() column. The data has been truncated to the max 32bit length so all my data is displayed as "4294967295" Is there ...
0
votes
1answer
25 views

Simplest way to log in as Oracle user different from schema user and still see all tables,… of schema?

What is the simplest way to log in as user X and see all tables,... of schema Y as if they belonged to X? With an Oracle 11g database, I would like to log into the database using user X but all the ...
1
vote
1answer
22 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
22 views

Which foreign keys do I use if they are repeated

I have the following two tables company_relationships |ID|Company_ID|Affiliate_ID|<Relationship Info>| +--+----------+------------+-------------------+ jobs |ID|Company_ID|<Job Info>| ...
0
votes
0answers
27 views

Entity Schema Design

I'm working on an application that has an "entities" system (built before my time) that models users, customers, contractors, vendors, etc. These are all types of people, but have different needs in ...
0
votes
1answer
20 views

Working out validity of table relationship

Question Background: In a quest to improve my skill set with TSQL programming I'm designing a 'simple' database schema for a zoo. In all I currently have 4 tables but am having an issue working out ...
0
votes
0answers
18 views

How to store highly configurable relationships based on multiple conditions

I'm trying to devise a schema that needs to be extremely configurable when necessary, but also minimal wherever possible. There is a large number of condition types that would lead to a number of ...
0
votes
1answer
43 views

to pivot or not to pivot - schema design question [closed]

I am implementing something similar to this schema http://www.databaseanswers.org/data_models/event_reservations/index.htm It occurs to me that the pivot table for Customer_Bookings would just keep ...
0
votes
1answer
28 views

Ensuring that 1 row has 5 specific records referencing it

Context: a TEST has many CASEs. Typical. However, the catch is that a TEST instance must have exactly 5 instances of CASE (1 to 5). I could do something like this: create table TEST ( ID ...
0
votes
0answers
21 views

Database & Structure for SaaS reporting software

I'm building a SaaS application to allow users to upload data and perform reporting and analytics on it. The reporting and analytics would be quite simple like grouping and aggregating. This has ...
0
votes
2answers
50 views

PostgreSQL : dependencies on a schema

I need to delete a schema in my PostgreSQL database. I would like to make sure that the schema is not used in another schema (for instance : this schema is used for a view in another one). Is there a ...
1
vote
2answers
77 views

Unique column values between tables

I'm keeping track of teams and their members. Each team has one leader, one contact person, and any number of players, and each of them has a unique email address. The problem is that one physical ...
1
vote
1answer
156 views

Updating mysql trigger via information_schema — access denied?

I have some wrong "definer"s for some triggers in MySQL (actually MariaDB). I figured the easiest way of correcting them would be some version of: use information_schema; update triggers set ...
1
vote
1answer
110 views

PostgreSQL complaining about shared memory, but shared memory seems to be OK

I've been performing kind of intensive schema dropping and creating over a PostgreSQL server, but now complains..: WARNING: out of shared memory ERROR: out of shared memory HINT: You might need to ...
0
votes
0answers
8 views

CROSS JOIN and updatable model design for Ruby on Rails [duplicate]

I am trying to build the multi-level model in rub on rails to allow the following to happen: When a user creates a campaign they will select some categories and some metros from tables Category and ...
0
votes
1answer
33 views

Schema for a reservation system

I'm working on a reservation system schema and have some problems defining availability of resources. I searched for similar question here and on Google but still don't have clear understanding My ...
1
vote
1answer
26 views

What are some databases that are schema-full but not relational

What are some databases/datastores that are schema-full, but not relational? In other word I'm after some NoSql databases that are not schema-less. By schema-full I mean that, like in a traditional ...
1
vote
1answer
100 views

Friendship and Messages in a MySQL table relational schema problem (concerning big data manipulation)

Before you start suggesting this question for duplicate read it, I have searched and i haven't found any proper solution. So, i'm building a social networking site (I know that you are laughing ...
0
votes
0answers
77 views

Changing Schema ownership and side effects

I am trying to understand why changing schema ownership is causing permissions to be revoked. I am having a hard time understanding schema ownership change. Here are the steps I have taken in ...
1
vote
1answer
41 views

MySQL Junction Table as Foreign Key?

I have several tables: company, customer, company_has_customer, and transaction. company_has_customer is a many-to-many junction table using the PKs of company and customer as its PK. In almost all ...
0
votes
1answer
313 views

DB2 - how to safely blow away database on linux?

I'm using (I believe) 10.1.2, on CentOS 6.5 (for sure). The underlying problem I have is that the database upgrade package prepared in house isn't working for me, in at least the sense that the ...
1
vote
1answer
58 views

Permission for sequence in another schema

Postgres 9.3 Debian 7.0 I created a specific schema for a specific user and created a view in this schema for this user, so it's the only table he knows that exists. The problem is that this same ...
1
vote
1answer
113 views

Handling user logins while using a multiple schema design to support multi-tenancy

I'm creating a web application (in PHP and PostreSQL) that will be used by organisations and their staff and I want to use multiple schemas to support multi-tenancy. There will be one database and I ...
1
vote
3answers
657 views

Query to compare the structure of two tables in MySQL

To automate the backup process of one of my MySQL databases, I would like to compare the structure of two tables (current version vs old version). Can you think of a query that can compare two ...
1
vote
1answer
98 views

Schema unusable after deleting another database

I just get a problem with a customer's SQLServer. I'm reading articles related to schema, owners and permissions, but I can't get it out. I have two DB : DB1 and DB2. I have a schema, which has the ...
0
votes
1answer
74 views

Convert multiple databases into schemas SQL Server

Due to a high amount of databases being created on my project and the rising cost per databases I had the following question: Is there a way to convert multiple databases in SQL Server into one ...
2
votes
2answers
38 views

Schema for storing historical data on shared identifiers

When user account is assigned an IP address, I create a table entry for the account/IP mapping. This data in the table is updated every 5 minutes. Right now I'm only keeping real-time data but I need ...
5
votes
2answers
53 views

How to enforce the structural constraints of rectangularly arrayed data?

(I'm particularly interested in answers that view the question below as a special case of the question: how should a RDBMS enforce structural constraints that are more specific than "one-to-many" and ...