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

1
vote
1answer
27 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 ...
-2
votes
0answers
26 views

Stock table Database Design [on hold]

I have to design a stock database of various products for different entities i.e. Distributor, Manager, Field Force. The Distributor allocates some products from his stock to manager and manager ...
0
votes
2answers
12 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
21 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 ...
0
votes
0answers
9 views

In MySQL 5.6, schema changes in master blocks the slave?

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

MySQL table indexes change location

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
27 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
37 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
30 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
44 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
13 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
114 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
28 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
42 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
23 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
37 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
23 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
21 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
21 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
25 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
18 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
17 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
38 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
18 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
46 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
74 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
95 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
63 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
31 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
78 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
64 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
38 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
190 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
40 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
90 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
363 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
97 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
71 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
34 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
50 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 ...
1
vote
1answer
53 views

Does a Key-Value type table still needs a auto increment key?

Suppose that I have a very simple table to store variable-like data, like this: +-----------+-----------------+ | key | value | +-----------+-----------------+ | site_url | ...
0
votes
1answer
60 views

Schema for multi-account database

I'm building an app which will have accounts for organisations leading to organisation.myapp.com subdomains for their account access. I'm putting together the schema for it and I have a few ...
0
votes
1answer
43 views

modelling warehouse picking

What's the best way to approach modelling a warehouse picking system? To be precise, customers place orders which are processed by warehouse staff. A simple products and orders database isn't an ...
2
votes
0answers
73 views

Which is the better schema design for a voting system?

Imagine a q/a site or forum similar to stackoverflow. I'm trying to determine which of these two methods would be the better design for a voting system: (note: I would also like to keep record of all ...
1
vote
0answers
20 views

Can I include extra columns in a m->m mapping table? [duplicate]

I guess this is sort of an odd question, but consider this scenario. table classes (id_pk) table students (id_pk) table students_classes_map (students_id_fk_pk, classes_id_fk_pk, finalExamScore ...
2
votes
1answer
73 views

select query on unique column AND alternate column of “aliases”

I have a MySQL table (~10,000 rows) with a pk id (int) column and a name (varchar) column (as well as a few other unimportant columns for this question). The name column contains unique values that ...